Cable Forum - PHP/SQL Help Please

Cable Forum (https://www.cableforum.uk/board/index.php)

- Internet Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=25)

- - PHP/SQL Help Please (https://www.cableforum.uk/board/showthread.php?t=32772)

Raistlin

30-07-2005 23:28

PHP/SQL Help Please

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

30-07-2005 23:35

Re: PHP/SQL Help Please

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

30-07-2005 23:39

Re: PHP/SQL Help Please

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

30-07-2005 23:43

Re: PHP/SQL Help Please

Quote:

Originally Posted by Raistlin

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.

Paul	30-07-2005 23:44

Re: PHP/SQL Help Please

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 ....... :cool:

Raistlin

30-07-2005 23:56

Re: PHP/SQL Help Please

I think I'm being incredibly dense here :dunce:

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

31-07-2005 00:04

Re: PHP/SQL Help Please

Quote:

Originally Posted by Raistlin

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

Raistlin

31-07-2005 00:05

Re: PHP/SQL Help Please

That's what I thought.....

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

iron25

31-07-2005 00:06

Re: PHP/SQL Help Please

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

Quote:

Originally Posted by Raistlin

That's what I thought.....

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

It can't be :shrug:

Raistlin

31-07-2005 00:10

Re: PHP/SQL Help Please

Quote:

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.

Quote:

Originally Posted by iron25

Quote:

Originally Posted by Raistlin

That's what I thought.....

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

It can't be :shrug:

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

31-07-2005 00:12

Re: PHP/SQL Help Please

Quote:

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)

Paul	31-07-2005 00:12

Re: PHP/SQL Help Please

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

31-07-2005 00:13

Re: PHP/SQL Help Please

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

31-07-2005 00:18

Re: PHP/SQL Help Please

Quote:

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

31-07-2005 00:21

Re: PHP/SQL Help Please

Quote:

Originally Posted by Raistlin

Have I made another dumb mistake somewhere?

Yes :p:

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.

Raistlin

31-07-2005 00:25

Re: PHP/SQL Help Please

Ok, the username and password are correct.....

What error message are you getting now?

I only ask that because I've made a change, it's still not working, and I'm not getting any error messages - I wasn't even getting the one that you were getting :(

iron25

31-07-2005 00:29

Re: PHP/SQL Help Please

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

Raistlin

31-07-2005 00:32

Re: PHP/SQL Help Please

Ok, I've deleted that user and added a new one, then changed the user name and password in the PHP so that they match the new account created.

Still not working and still getting no error messages.....

Is there any chance that there's something not set up quite right somewhere that I don't have access to on the site?

iron25

31-07-2005 00:35

Re: PHP/SQL Help Please

In mysql you will need to give users rights to access certain databases and tables. You can assign the select/update/delete rights for each user for a database & table. I don't know how it works with your provider as I do it from either a linux prompt or webmin.

Raistlin

31-07-2005 00:41

Re: PHP/SQL Help Please

Ok, I think I'm getting somewehere.

Hadn't realised that you have to explicitly grant the user access to each database.....

Done that now and I-Web's admin panel actually supplies the connection strings.

The PHP now appears to be connecting to the database ok but I still get a blank page.

I am now seeing an error message in the source for the page though:

Quote:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/catswor/public_html/maps/example.php on line 34

iron25

31-07-2005 00:43

Re: PHP/SQL Help Please

I would suggest that there is a problem with your query section of the code. From the paste of the code you have:

$result = mysql_query("SELECT * FROM 'memberlocations'",$link);

If the memberlocations table exists then you do not need to put quotes around it:

$result = mysql_query("SELECT * FROM memberlocations",$link);

Raistlin

31-07-2005 01:06

Re: PHP/SQL Help Please

:woot:

It's Working :D

Thanks for all the help guys, really appreciate it :tu:

It'll 404 at the moment though because I'm fiddling, give me a moment and I'll show you the result :)
__________________

Ok, it's working now.....

Click the balloon ;)

It's the start of a little project that I've been wanting to have a go at for a while :)

Thanks again for all the help :tu:

iron25

31-07-2005 01:54

Re: PHP/SQL Help Please

Quote:

Originally Posted by Raistlin

Click the balloon ;)

Aaah, ain't that special :)

Richard M

01-08-2005 00:18

Re: PHP/SQL Help Please

Quote:

Originally Posted by Paul M

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.

Sorry, just to be annoying here. :D
The mysql_select_db() function actually does a "use <database>" command.
The mysql_connect() function does establish the connection in the first place.

You can probably test this by using this code:

PHP Code:


		
			
<?php

if(!mysql_connect('server', 'username', 'password') {

echo 'I can\'t connect!';

exit;

}

else {

echo 'Connected OK!';

}

?>

All times are GMT. The time now is 13:16.