Page 1 of 1

Retrieving Query Data [SOLVED]

Posted: Fri Jul 04, 2008 4:56 pm
by groog
Ok, here's tough question and I'm getting a strange results. I have this book I'm using (PHP and MySQL for Dynamic Websites) and I'm reading it trying to figure out how I can retrieve data from a query and display it on the page. Ok so here is what I'm looking at. I won't give you the url because I'll probably be messing around with it while waiting for replies.

Image

In the left menu I'm going to have the top 10 most viewed guides left aligned and with a hyperlink to the page (which hasn't been attempted in the code I will show you. I'm trying to get it to display first). Right aligned to the right of it (in another td or div) I will display the amount of views. Right now just to display what I want it to look like It's just using <ul><li> tags. Now my book is telling me to go by this script that I followed as close as possible (but made changes to adapt to my needs).

Code: Select all

<?php
$host = 'localhost';
$user = 'groog_admin';
$pass = 'password';
$query = "SELECT guide_name AS name, guide_views AS views FROM guides_table ORDER BY guide_views ASC LIMIT 10";
 
$connect = @mysql_connect($host, $user, $pass) or die('Error connecting to MySQL!');
@mysql_select_db(groog_guides) or die('Error connecting to database!');
$result = @mysql_query($query);
 
if ($result){
echo '<tr><td align="left">Name</td><td align="right">Views</td></tr>";
 
while ($row = $mysql_fetch_array($result, MYSQL_ASSOC))
{
echo '<tr><td align="left">' . $row['name'] . '</td><td align="right">' . $row['views'] . '</td></tr>';
}
mysql_free_result($result);
mysql_end();
?>
To narrow it down, the mysql_connect, mysql_select_db, and mysql_query all work fine because I've tested it with another script and it works. Furthermore, the query itself works because when I use the PhpMyAdmin query the results display fine-

Image

So I think It's safe to say that the problem is this last chunk of code-

Code: Select all

if ($result){
echo '<tr><td align="left">Name</td><td align="right">Views</td></tr>";
 
while ($row = $mysql_fetch_array($result, MYSQL_ASSOC))
{
echo '<tr><td align="left">' . $row['name'] . '</td><td align="right">' . $row['views'] . '</td></tr>';
}
And when I enter this code in (I've tried the rest of the code but excluded this and the page wasn't altered) all the rest of the code from here on out fails.

Image

Don't worry about that chunk of text that is now in the left menu. That's just the forced ad that sits at the bottom (that I shrunk down). But everything from there over disappeared (because that come relatively first in the index.php file). So my question is, How can I retrieve data and display it? Why won't my code work?

Thanks in advanced :D

Re: Retrieving Query Data

Posted: Fri Jul 04, 2008 7:30 pm
by califdon
First, while you are still debugging your code, remove all those @ signs in front of your commands because they suppress any warning messages that might tell you what's going wrong.

Next, if you want the TOP 10, you have to make the ORDER BY guide_views DESC, which means "biggest first."

Finally, you've made a risky assumption, that the query works because you've used it successfully directly on the database. That makes logical sense, but it doesn't take into account numerous little things that might go wrong--the very things that you are now struggling with. On a first pass, that might be a reasonable assumption, but when things start going wacko, that's the very first assumption you should discard.

In fact, since your problem is that apparently nothing is being sent to the page in the place you're looking for it, the first thing I would suspect would be the query, since your code only sends that part of it to the page if ($result) ... and thus, I would bet some small amount that $result is false!!

So where you now have:

Code: Select all

$result = @mysql_query($query);
I recommend that you put the following, temporarily, and run it again:

Code: Select all

$result = mysql_query($query) or die(mysql_error());

Re: Retrieving Query Data

Posted: Fri Jul 04, 2008 8:12 pm
by groog
I tried all that and no luck. I even looked around and added a new script that might shed some light but still nothing. No error messages will come up either :S

Code: Select all

<?php
$host = 'localhost';
$user = 'groog_admin';
$pass = 'password';
$guides_database = 'groog_guides';
$query = 'SELECT guide_name AS name, guide_views AS views FROM guides_table ORDER BY guide_views DESC LIMIT 10';
 
mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($guides_database) or die(mysql_error());
$result = mysql_query($query) or die(mysql_error());
 
if ($result){
echo '<tr><td align="left">Name</td><td align="right">Views</td></tr>";
 
while ($row = $mysql_fetch_array($result, MYSQL_ASSOC))
{
echo '<tr><td align="left">' . $row['name'] . '</td><td align="right">' . $row['views'] . '</td></tr>';
}
}else {
trigger_error("Could not perform action. Query failed: " . mysql_error(), E_USER_WARNING);
}
mysql_free_result($result);
mysql_end();
?>

Re: Retrieving Query Data

Posted: Fri Jul 04, 2008 8:54 pm
by califdon
OK. So absolutely nothing appears on the screen? Then we must determine what, if anything, is being returned as $result.

I would remove everything after your $result = mysql_query($query) or die(mysql_error()); line and continue with the following:

Code: Select all

echo "Rows returned: " . mysql_num_rows($result);
This will tell you whether the query is returning any rows. If it is zero, or if nothing at all follows "Rows returned:", then you have a problem with your query. If that's the case, I'd try it again with the simplest of all possible queries:

Code: Select all

$query = 'SELECT guide_name, guide_views FROM guides_table;
If it still returns no rows, there's something wrong with something like your table or field names.

Re: Retrieving Query Data

Posted: Fri Jul 04, 2008 11:12 pm
by groog
Rows Returned: 1
It found one row. So I guess the query is good? What then could be the problem? And I also ran if($result){} as-

Code: Select all

if($result){
echo "Result is true!!";
}else{
echo "Uh oh. Result is false!! :(";
}
And it turns out result is true.

Re: Retrieving Query Data

Posted: Sat Jul 05, 2008 12:22 am
by califdon
Now you're making progress! You know the connection is good and the SQL has returned 1 row. So now you want to find out what's in that row, so:

Code: Select all

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
foreach ($row as $field => $value) {
  echo "Field: $field, Value: $value<br />";
}
That's going to fetch just one row (we've already determined that there's only one, so no point in cycling through them), and then for each Field Name in the row it should print out the name and its value.

See what that produces.

Re: Retrieving Query Data

Posted: Sat Jul 05, 2008 12:43 am
by groog
I've been messing with it and I got it to work on it's own! Thank you for your help, it really helped :D

SOLVED