Retrieving Query Data [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
groog
Forum Newbie
Posts: 15
Joined: Wed Jun 18, 2008 11:48 am

Retrieving Query Data [SOLVED]

Post 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
Last edited by groog on Sat Jul 05, 2008 1:15 am, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Retrieving Query Data

Post 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());
groog
Forum Newbie
Posts: 15
Joined: Wed Jun 18, 2008 11:48 am

Re: Retrieving Query Data

Post 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();
?>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Retrieving Query Data

Post 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.
groog
Forum Newbie
Posts: 15
Joined: Wed Jun 18, 2008 11:48 am

Re: Retrieving Query Data

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Retrieving Query Data

Post 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.
groog
Forum Newbie
Posts: 15
Joined: Wed Jun 18, 2008 11:48 am

Re: Retrieving Query Data

Post 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
Post Reply