Issues connecting PHP and MySQL

[kennyaidan]

Hi Guys,

On my home PC, I have installed a MySql DB and an Apache server in order to progress my PHP knowledge. I created a new database called "manual" and within this DB I added a table and simple simple data. When I query my DB through MySQL server, I can see the "manual" database and a test table I created called "pets". Here is the SQL query below

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 rows in set (0.56 sec)

mysql> use manual
Database changed
mysql> SHOW TABLES;
Empty set (0.03 sec)

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20));
Query OK, 0 rows affected (0.92 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_manual |
+------------------+
| pet |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO pet VALUES ('Tiger','Damian');
Query OK, 1 row affected (0.13 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_manual |
+------------------+
| pet |
+------------------+
1 row in set (0.00 sec)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

However when I run sample php files to extract and display the data I just get empty pages back with no error messages.

Here is an example of a php test file I have been running on localhost:

<?php
$connection = mysql_connect("localhost", "root", "*******");
$database = mysql_select_db("manual");
$result = mysql_query("SELECT * FROM pets");

while($row = mysql_fetch_array($result, MYSQL_NUM)) {
echo $row[0] . '<br>';
}
mysql_close();
?>


So it seems that while the MySQL DB has been created and tables are present, the php files cannot connect and retrieve data from the DB hence the test pages are appearing blank. I receive no error messages on these test php files.

When I add simple pieces of text before the php code, they are displayed fine in browser so Apache itself is working fine, I feel it is obviously my code that is missing something? Or do I need to install further SW that allows PHP and MySQL talk to each other?

Any ideas on how I should resolve this issue would be great

Thanks

Aidan

 

[jpadie]

try this debug code

<?php
//connect to the database and server
mysql_connect("localhost", "root", "xxxxxxxx") or die (mysql_error());
mysql_select_db("manual") or die(mysql_error());

//retrieve the table names
$sql = "show tables";
$result = mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_array($result)){
	$tables[] = $row[0];
}

//output the table names
echo "Found ".count($tables) ." tables in the database.  Table names are :";
echo "<ol>";
foreach($tables as $table){
	//create some anchors for easy navigation
	echo "<li><a href=\"#$table\">$table</a></li><br/>";
}
echo "</ol>";
echo "<br/>";

//iterate through the table names 
foreach ($tables as $table){
	echo "<hr/>";
	echo "<a name=\"$table\"></a>";
	echo "Dumping $table <br/>";
	$sql = "select * from $table";
	$result = mysql_query($sql) or die();
 	//check for any results
	$nR = mysql_num_rows($result);
	if($nR > 0){
		echo "Retrieving $nR results<br/>";
		$first = true;
		echo "<table border=\"1\">";
		while ($row = mysql_fetch_assoc($result)){
			echo "<tr>";
			if ($first){
				echo "<td>".implode("</td><td>", array_keys($row)) ."</td></tr><tr>";
				$first = false;		
			}
			echo "<td>".implode("</td><td>", array_values($row))."</td>";
			echo "</tr>";
		}
		echo "</table>";
	} else {
		echo "Table is empty<br/>";
	}
}
?>

 

[vacunita]

You said:

I have installed a MySql DB and an Apache

I know its obvious, but have you configured Apache to run PHP?
How are you calling your PHP page: You should be accesing it like this:

http://localhost/myphppage.php

Create a file that contains only the following:

<?php
phpinfo();
?>
[code]

And try to access it, you should get a bunch of information regarding your PHP installation. 

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[kennyaidan]

Hey Guys,

Thanks for getting back to me. I tried the code you suggested jpadie and still no luck. I just got a blank page back, no error messages at all. Its like the php cannot connect to the DB at all.

To answer your question Vacunita, I think I have configured Apache correctly because normal php that don't try accessing the DB work fine. If I use the code jpadie suggested and just add in a simple "hello" string at the top, it is displayed when I type the url into my browser -

http://localhost/testMysql5.php

-------------------------------------------
hello

<?php
//connect to the database and server
mysql_connect("localhost", "root", "*****") or die (mysql_error());
......
......

echo "Table is empty<br/>";
}
}
?>
-------------------------------------------

When I place a number of normal echo strings to test where the code stops working, some aren't displayed after the DB connect code begins

So for example the following code:
++++++++++++++++++++++++++++++++++++++
hello

<?php

echo "hello1";

//connect to the database and server
mysql_connect("localhost", "root", "8461116") or die (mysql_error());
echo "hello2";
mysql_select_db("manual") or die(mysql_error());

echo "hello3";
......
......

echo "Table is empty<br/>";
}
}
?>

++++++++++++++++++++++++++++++++++++++

The Apache server displayes "hello" and "hello1" but does not display "hello2" or "hello3". I'm not sure if this helps but its like the DB connect code doesn't get processed by Apache.

Tks again for the help

Aidan

 

[jpadie]

Thanks for getting back to me. I tried the code you suggested jpadie and still no luck. I just got a blank page back, no error messages at all. Its like the php cannot connect to the DB at all.

I can't see how that is possible. the debugging code I wrote will have issued something, come what may...

UNLESS ....
you have turned display_startup_errors off in your php.ini file.

check this. turn it on and then reboot the webserver and try again.

i'd guess you have a problem with your libmysql.dll file. try either including the php directory in the path and rebooting, or copy libmysql into the windows directory.

 

[kennyaidan]

Hi jpadie,

You were right!! The display_startup_errors was set to off in the php.ini file. I turned that to "On" and rebooted apache. I then get the following three error messages on reboot

1. PHP Startup:Unable to load dynamic library 'C:\Program Files\PHP\ext\php_mysqli.dll' - The specified module could not be found

2. PHP Startup:Unable to load dynamic library 'C:\Program Files\PHP\ext\php_pdo_mysql.dll' - The specified module could not be found

3. PHP Startup:Unable to load dynamic library 'C:\Program Files\PHP\ext\php_mysql.dll' - The specified module could not be found

When I access your test file I still get a blank page but I assume it is because I am missing the modules above? Where would I download all the necessary modules?

The libmysql.dll file is in the C:\Program Files\PHP\ directory, I set up a ext directory and add the libmysql.dll and php_mysqli.dll' files but still getting an error when I reboot Apache?

Tks
Aidan

 

[jpadie]

you need to add the other two libraries too. or comment the extension directive out from your php.ini file and reboot.

i would also recommend setting C:\Program Files\PHP\ as part of your path environment variable.

When I access your test file I still get a blank page but I assume it is because I am missing the modules above? Where would I download all the necessary modules?

you should not get a blank page - you should, at least, get the startup errors.

these errors are "fatal" meaning that php won't continue whilst they remain unresolved.

 

[kennyaidan]

I changed my php.ini file to now lists the three modules as
;extension=php_mysql.dll
;extension=php_mysqli.dll
;extension=php_pdo_mysql.dll

They had been

extension=php_mysql.dll
extension=php_mysqli.dll
extension=php_pdo_mysql.dll

When I reboot Apache, the "PHP Startup:Unable to load dynamic library.." errors do not appear but I still get a blank page with no errors.

Should I change these to

;php_mysql.dll
;php_mysqli.dll
;php_pdo_mysql.dll

Sorry to be a pain and apologies if these are basic questions but I have very limited knowledge of setting up servers and DB's to work in tandem. As I say it looks like my Apache server does even start to process your code, it stops before
mysql_connect("localhost", "root", "***") ....

 

[jpadie]

if you prepend a semicolon you stop the library from loading. i doubt whether this is what you want, particularly in the case of php_mysql.dll

they need to be loaded at runtime or boottime.

i would recommend also turning on the display_errors directive in php.ini and also setting error_reporting to E_ALL in php.ini. that way, during this debug process, you will at least get some feedback as to what is wrong.

don't forget to restart the webserver after making the changes.

 

[kennyaidan]

Hi jpadie,

I changed around some settings in the php.ini file and finally got to see so errors. Problem is they are fatal!

Here is what I got:

Fatal error: Call to undefined function mysql_connect() in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\testMysql6.php on line 3

Line 3 is:

$link = mysql_connect("localhost", "root", "****") or die(mysql_error());

I have PHP 3.01, Apache Version 2.0 and MySQL Server 5.0. Do these versions run smoothly together? Should I start from scratch and reinstall different versions?

Thanks

Aidan

 

[jpadie]

i'm pretty sure that you need to bring your php installation up to date. i do not know whether a connection library for php 3 -> mysql 5 exists. we're at php 5.2 now (discounting php6 for the time being).

 

[kennyaidan]

Hi jpadie,

Aplogies but on further inspection, I actually had PHP 5.0 installed. I downloaded and installed PHP version 5.2.4. However I still can't get a MySql DB connection working. Here is the sample code

<?php
$link = mysql_connect("localhost", "root", "****") or die(mysql_error());
echo "<br>Successfully connected to the mysql server...";
?>

I get the following error:
Fatal error: Call to undefined function mysql_connect() in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\testMysql6.php on line 3

Any suggestions on where to go from here? As I said in previous posts, the DB tables are present and there is data stored in the tables. I just can't get a connection to the DB using PHP. Am I missing some SW that connects the the DB and PHP?

Tks

Aidan

 

[jpadie]

you have not enabled mysql in your php.ini file. or if you have it still is not loading.
you need to make sure that extension=php_mysql.dll is firmly in your php.ini file.

then reboot.

and also ensure that libmysql is in you php folder or in the windows directory.

 

[kennyaidan]

Hi jpadie,

All sorted now, got it working. Thank you very much for the help

Aidan

 

[jpadie]

well done!