PHP/SQL Help Please

[Raistlin]

Hi Guys,

I'm trying to use the following PHP code to query some entries in a MySQL database but I just can't get it to work:

PHP Code:

<?php
$link = mysql_connect("[My Database]", "dbmapserver", "[Password]")
or die("Could not connect: " . mysql_error());
mysql_selectdb("dbmapserver",$link) or die ("Can\'t use dbmapserver : " . mysql_error());

$result = mysql_query("SELECT * FROM 'memberlocations'",$link);
if (!$result)
{
echo "no results ";
}
while($row = mysql_fetch_array($result))
{
echo "var point = new GPoint(" . $row['lon'] . "," . $row['lat'] . ");\n";
echo "var marker = createMarker(point, '" . $row['desc'] . "');\n";
echo "map.addOverlay(marker);\n";
echo "\n";
}

mysql_close($link);
?>

Where it says "[My Database]" I need to put in the name of my database (Duh!), the database is hosted on my I-Web hosting account as a MySQL database and is called "catswor_maps" - what should I put in as the name of the database? Do I need to include the full path of the site? Do I just put in the database name?

I know what the password is (I just blanked it out above) so that's not an issue. Do I need to include a user name somewhere though?

Any thoughts would be greatly appreciated.

When I upload the page containing this code to my site it simply displays a blank page, if I take this section of code out everything is ok and I get the results I'm expecting.

Here's the page without the PHP included.

http://www.catsworthpcs.co.uk/maps/example.htm

[punky]

Where you have [My Database] that should be the hostname that runs your MySql server. Something like mysql.i-websolutions.co.uk or whatever.

The user name is normally your account user name, rather than anything to do with the DB.

In the mysql_select_db bit, you should use "catswor_maps" which appears to be the name of your DB? Or is that a table inside your DB?

[Raistlin]

Any idea where I need to include the username?
__________________

I just tried replacing [My Database] with the URL for my site and got no joy, I'm assuming it's trickier than that.

Anybody successfully connecting to an I-Web MySql database got any idea what the path needs to be?

[iron25]

Originally Posted by Raistlin

Any idea where I need to include the username?
__________________

I just tried replacing [My Database] with the URL for my site and got no joy, I'm assuming it's trickier than that.

Anybody successfully connecting to an I-Web MySql database got any idea what the path needs to be?

What is the url you are using?

If you are running the php on the box itself then you can just use localhost as shown below:

$username = "name";
$password = "password";
$hostname = "localhost";

$dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to mysql");

The username & password is the username & password for the mysql database. I've never used I-Web so I can only assume that there must be just one username that is global to your account.

It might be worthwhile checking their website as you might need to specify a port to connect on.

The format of mysql_connect() is this ;

PHP Code:

mysql_connect('server', 'user', 'password') 


You then use the mysql_select_db() statement to connect to your database. If the php is running on the same server as mysql then the server will be 'localhost'.

Edit, oh well, beaten by half a dozen .......

[Raistlin]

I think I'm being incredibly dense here

It doesn't seem to want to work.

I've substituted localhost, my user name, and my password and I still get a blank page.

I've uploaded the code as I have it at the moment (including username and password. If you try to load the link above now you should just see the blank page (but you'll be able to see the source as well).

Maybe someone can spot what I'm doing wrong?

[iron25]

Originally Posted by Raistlin

I think I'm being incredibly dense here

It doesn't seem to want to work.

I've substituted localhost, my user name, and my password and I still get a blank page.

I've uploaded the code as I have it at the moment (including username and password. If you try to load the link above now you should just see the blank page (but you'll be able to see the source as well).

Maybe someone can spot what I'm doing wrong?

If it is running PHP then we should not be able to view the PHP code in 'View Source' only the results

[Raistlin]

That's what I thought.....

It's definitely running PHP though.....

[iron25]

Which server is the mysql database stored on and which server is running the HTML & the PHP?
__________________

Originally Posted by Raistlin

That's what I thought.....

It's definitely running PHP though.....

It can't be

[Raistlin]

Originally Posted by iron25

Which server is the mysql database stored on and which server is running the HTML & the PHP?

It's on whatever server my website is set up on.....I'm sure that's not a lot of use though is it

Ummm..... Other than the URL for the site and the fact that MyPHP Admin lists it as localhost (as said above) I'm not sure what other informtion I can give.....

The HTML and the PHP are running from the public_html/maps folder of my webspace which is where the rest of the site pages sit.

Originally Posted by iron25

It can't be

It says that it is on the main page of the Control Panel for the site.....maybe I'm reading it wrong (or I've got to set something else up).....

[Tezcatlipoca]

Originally Posted by Raistlin

It says that it is on the main page of the Control Panel for the site.....maybe I'm reading it wrong (or I've got to set something else up).....

Have you created a User for the database? (I have one, same name as the database itself, used for my blog)

I think the server is unlikely to execute the php because you have used a .htm file extension - which it will simply serve up as a static html page.

[iron25]

OK, can we clarify that the html & php you are running is on the same server that the mysql database is running on.

If it is then Paul M is right.

[Raistlin]

Originally Posted by Paul M

I think the server is unlikely to execute the php because you have used a .htm file extension - which it will simply serve up as a static html page.

Well spotted

It seems to be running the PHP now, if you check out:

http://www.catsworthpcs.co.uk/maps/example.php

You will see the result

All I get now is a little grey // towards the bottom of the page.

Here's the complete HTML:

PHP Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <script src="http://maps.google.com/maps?file=api&v=1&key=ABQIAAAA_0Z8PeM-XgTpnTEQ2Eu_dhS9v8dKVB9YxxVRvDfddQklYutOPxSzQleklTrdQ1Oj9gVkdwpq6oq7uQ" type="text/javascript"></script>
  </head>
  <body>
    <div id="map" style="width: 500px; height: 400px"></div>
    <script type="text/javascript">
//<![CDATA[

var map = new GMap(document.getElementById("map"));
map.addControl(new GLargeMapControl());
map.addControl(new GMapTypeControl());
map.centerAndZoom(new GPoint(***), 12);

// Creates a marker whose info window displays the given number
function createMarker(point, number)
{
var marker = new GMarker(point);
// Show this markers index in the info window when it is clicked
var html = number;
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml(html);});
return marker;
};
<?php
$link = mysql_connect('localhost', '***', '***')
or die("Could not connect: " . mysql_error());
mysql_selectdb('***',$link) or die ("Can\'t use dbmapserver : " . mysql_error());

$result = mysql_query("SELECT * FROM '***'",$link);
if (!$result)
{
echo "no results ";
}
while($row = mysql_fetch_array($result))
{
echo "var point = new GPoint(" . $row['lon'] . "," . $row['lat'] . ");\n";
echo "var marker = createMarker(point, '" . $row['desc'] . "');\n";
echo "map.addOverlay(marker);\n";
echo "\n";
}

mysql_close($link);
?> 

//]]>
</script>

</body>
</html>
  </body>
</html>

Have I made another dumb mistake somewhere?

[iron25]

Originally Posted by Raistlin

Have I made another dumb mistake somewhere?

Yes

Can\'t use dbmapserver : Access denied for user 'catswor_catswort'@'localhost' to database 'catswor_catswort'

Looks like the username & password are incorrect.

Either the db does not exist or it has additional authentication. I know in mysql you can add authentication right down to field level.