Retrieving data from mysql

steveDD

Hi
I am having problems retrieving data from my mysql database. I have the database called blc and a table called Companies.
within this table is 5 rows, of which I only want to request 4 (catagory, company, description, value).

I have the following code and because I want to use this with my all flash website I have added the XML coding (this is the part that is not working). Has anyone any ideas why my page is returing 0 results?

I am using the following code:

<?PHP

$link = mysql_connect("localhost","username","password");
mysql_select_db("blc");

$query = 'SELECT catagory, company, description, value FROM Companies';
$results = mysql_query($query);

echo "<?xml version=\"1.0\"?>\n";
echo "<company>\n";

while($line = mysql_fetch_assoc($results)) {
	echo "<item>" . $line["catagory"] . "</item>\n"
	echo "<item>" . $line["company"] . "</item>\n"
	echo "<item>" . $line["description"] . "</item>\n"
	echo "<item>" . $line["value"] . "</item>\n";
}

echo "</company>\n";

mysql_close($link);

?>

Thank you.

Steve

laserlight

That looks okay. When you say zero results, you mean that all you get is:

<?xml version=\"1.0\"?>
<company>
</company>

Is that correct? If so, it implies that the data is simply not in the database.

steveDD

I am getting the following for some reason:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252"></HEAD>
<BODY></BODY></HTML>

I have saved the file as results.php and I have 6 entries in the database.

laserlight

Out of curiosity, is your error_reporting setting to set E_ALL and display_errors set to On in php.ini? It looks like you are getting a default blank page.

As props for debugging, write:

<?php
$link = mysql_connect("localhost","username","password")
    or die("Could not connect to database server: " . mysql_error());
mysql_select_db("blc")
    or die("Could not select database: " . mysql_error());

$query = 'SELECT catagory, company, description, value FROM Companies';
$results = mysql_query($query)
    or die("Could not execute SQL statement: " . mysql_error());

echo "<?xml version=\"1.0\"?>\n";
echo "<company>\n";

while($line = mysql_fetch_assoc($results)) {
	echo "<item>" . $line["catagory"] . "</item>\n"
	echo "<item>" . $line["company"] . "</item>\n"
	echo "<item>" . $line["description"] . "</item>\n"
	echo "<item>" . $line["value"] . "</item>\n";
}

echo "</company>\n";

mysql_close($link);

?>

Of course, in production code you should handle such errors more gracefully.

steveDD

I just thought in this part of the code:

echo "<?xml version=\"1.0\"?>\n";
echo "<company>\n";

while($line = mysql_fetch_assoc($results)) {
    echo "<item>" . $line["catagory"] . "</item>\n"
    echo "<item>" . $line["company"] . "</item>\n"
    echo "<item>" . $line["description"] . "</item>\n"
    echo "<item>" . $line["value"] . "</item>\n";
}

echo "</company>\n";

I am putting company at the beginning and end, however my table is called Companies?

The 4 option catagory, company, description and value are my rows.

steveDD

I have just tried the debug text above but again I am getting nothing but a blank page, I am not sure what you mean by the "is your error_reporting setting to set E_ALL and display_errors set to On in php.ini?"

laserlight

I am putting company at the beginning and end, however my table is called Companies?

That does not matter.

The 4 option catagory, company, description and value are my rows.

Eh, they appear to be your columns.

I have just tried the debug text above but again I am getting nothing but a blank page

Okay, test:

<?php
$link = mysql_connect("localhost", "username", "password")
    or die("Could not connect to database server: " . mysql_error());
mysql_select_db("blc", $link)
    or die("Could not select database: " . mysql_error());

echo "Companies:<br />\n";
$query = "SELECT catagory, company, description, value FROM Companies";
$results = mysql_query($query, $link)
    or die("Could not execute SQL statement: " . mysql_error());

while ($row = mysql_fetch_assoc($results)) {
    echo htmlspecialchars($row['catagory']) . "<br />\n"
       . htmlspecialchars($row['company']) . "<br />\n"
       . htmlspecialchars($row['description']) . "<br />\n"
       . htmlspecialchars($row['value']) . "<br />\n";
}

mysql_close($link);
?>

I am not sure what you mean by the "is your error_reporting setting to set E_ALL and display_errors set to On in php.ini?"

These are configuration settings that determine what level of errors are reported in your code, and whether to display the errors (if any). For development purposes, error_reporting should be set to E_ALL and display_errors to On. In production, display_errors should be Off and error logging used instead.

You can check these settings either by manually checking php.ini, or by using [man]phpinfo/man.

steveDD

I have tried that and again nothing, I am really sorry I am quite new to this and do not understand where to go and ammend the settings.

These are configuration settings that determine what level of errors are reported in your code, and whether to display the errors (if any). For development purposes, error_reporting should be set to E_ALL and display_errors to On. In production, display_errors should be Off and error logging used instead.

You can check these settings either by manually checking php.ini, or by using phpinfo().

laserlight

Okay. Create a new PHP script. In this script write:

<?php phpinfo(); ?>

Save and view the page.

By the way, did you install PHP on your local computer, or is it on a web server that you have no direct control over?

steveDD

I am running this on my webserver. I am uploading by ftp and viewing directly online.

I have completed the task:
error_reporting (mine set to - 2047 in local and master)
display_errors (mine is set to off and off)

Steve

laserlight

display_errors (mine is set to off and off)

You need to change that to on. If you are unable to change the php.ini file or use say, a .htaccess file to change this configuration setting, try putting this at the start of your script:

ini_set('display_errors', '1');

If that does not work, then you have to contact your web server administrator for assistance, and/or install PHP locally for testing.

steveDD

do you understand why I am getting the blank page now?

thanks steve

laserlight

do you understand why I am getting the blank page now?

What is your current script?

steveDD

<?PHP

$link = mysql_connect("localhost","username","password");
mysql_select_db("blc");

$query = 'SELECT catagory, company, description, value FROM Companies';
$results = mysql_query($query);

echo "<?xml version=\"1.0\"?>\n";
echo "<Companies>\n";

while($line = mysql_fetch_assoc($results)) {
	echo "<item>" . $line["catagory"] . "</item>\n"
	echo "<item>" . $line["company"] . "</item>\n"
	echo "<item>" . $line["description"] . "</item>\n"
	echo "<item>" . $line["value"] . "</item>\n";
}

echo "</Companies>\n";

mysql_close($link);

?>

laserlight

As I mentioned, add the ini_set and see if it works:

<?php
ini_set('display_errors', '1'); 

$link = mysql_connect("localhost","username","password");
mysql_select_db("blc");

$query = 'SELECT catagory, company, description, value FROM Companies';
$results = mysql_query($query);

echo "<?xml version=\"1.0\"?>\n";
echo "<Companies>\n";

while($line = mysql_fetch_assoc($results)) {
	echo "<item>" . $line["catagory"] . "</item>\n"
	echo "<item>" . $line["company"] . "</item>\n"
	echo "<item>" . $line["description"] . "</item>\n"
	echo "<item>" . $line["value"] . "</item>\n";
}

echo "</Companies>\n";

mysql_close($link);

?>

Still, I had the impression that the die() would take effect.

steveDD

No still shows nothing not even an error,

laserlight

That's tough. Let's go for something more basic:

<?php
if (ini_set('display_errors', '1') === false) {
    echo "Error: could not set display_errors to On<br />\n";
} else {
    echo "ini_set: display_errors set to On<br />\n";
}

echo "Connecting to database server...<br />\n";
$link = mysql_connect("localhost","username","password")
    or die("Could not connect to database server: " . mysql_error());
echo "Connected!<br />\nSelecting database...<br />\n";
mysql_select_db("blc")
    or die("Could not select database: " . mysql_error());
echo "Selected!<br />\nExecuting SQL statement...<br />\n"
$query = 'SELECT catagory, company, description, value FROM Companies';
$results = mysql_query($query)
    or die("Could not execute SQL statement: " . mysql_error());
echo "Executed!<br />\nRetrieving first row from result set...<br />\n";
$row = mysql_fetch_assoc($results);
echo "Retrieved!";
?>

EDIT:
The PHP manual states that: "Although display_errors may be set at runtime (with ini_set()), it won't have any affect if the script has fatal errors. This is because the desired runtime action does not get executed."

This could be what is happening. An alternative is to create a .htaccess file and set:

<IfModule mod_php5.c>
    php_flag display_errors on
</IfModule>

If you are using PHP4, set:

<IfModule mod_php4.c>
    php_flag display_errors on
</IfModule>

If the above still does not work, then your setup is unsuitable for development. You have to either install a web server and PHP locally, or request a change of configuration settings from your web server administrator.

steveDD

Abosolutly nothing just the usual:

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252"></HEAD>
<BODY></BODY></HTML>

Do I need to change any of the settings you mentioned earlier if so, how? I just can't understand why there are no results.

thanks

laserlight

Do I need to change any of the settings you mentioned earlier if so, how?

You can try the .htaccess file approach I mentioned in my edit. More information can be found in the PHP manual's entry on How to change configuration settings.

I just can't understand why there are no results.

You have an error, probably a fatal error, that is causing everything to simply not display. With the correct settings for development, you would be informed of the error instead.

steveDD

I will try and have a look, which is the eaiest method for changing your settings and is it the two settings you mentioned earlier I need to change?

I appreciate I may have an error in my code but even the latest code you asked me to try still didn't display any results.

Its just driving me crazy, as its code that works for eveyone else but not me 🙁 frustrating...

Steve