PHP is only returning first row of a MySQL SELECT * FROM tablename

Here’s what I’m trying to do…
I have a small mysql table called cats, theres about six entries inserted in it.
My code is supposed to print to screen the results of 'SELECT * FROM cats — Im hoping to get all the rows, or items in the table
When I execute my php file it only returns the first row, and all the other cats are missing.
Anybody know why its only returning the first row and not all the rows in my table?

<?php
/* 
 *  Select data from mysql database and display it to the screen
 */
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

if (!$db_server) die ("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());

$query = "SELECT * FROM cats";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " . mysql_error());
$rows = mysql_num_rows($result);

echo "<table><tr> <th>Id</th> <th>Family</th> <th>Name</th> <th>Age</th> </tr>";

for ($j = 0 ; $j < $rows ; ++$j);
{
    $row = mysql_fetch_row($result);
    echo "<tr>";
    for ($k = 0 ; $k < 4 ; ++$k) echo "<td>$row[$k]</td>";
    echo "</tr>";
}
echo "</table>";
mysql_close($db_server);
?>

You code seem to be fine, I can’t see what is wrong. Did you try to print $rows to check how much records is returned by your query? I myself prefer to use mysql_fetch_array(), and referencing to returned values using table field names as array index, like this:
[php]$row = mysql_fetch_array($result);
echo “

”;
echo “$row[‘Id’]”;
echo “$row[‘Family’]”;
echo “$row[‘Name’]”;
echo “$row[‘Age’]”;
echo “”;[/php]

Thanks for the reply,
When I entered:
echo $rows
The number 5 was returned, which just so happens to be the number of cats I have entered in my DB.
However it still only prints to screen the first row.

I sloppily tried to add your code into my first_try.php but couldn’t get it to work… So I looked in my book to learn a bit about mysql_fetch_array and sure enough my book has no mention of it. Perhaps I need to install another php_mod in apache… But I’m guessing it was the fact that I dont know where to slap in that piece of code you made.

And now another stupid question… yeah I know there are not stupid questions but this one’s a Duzayy
What should I remove and where should I paste that in?

The mysql_fetch_array() function does not require additional module (if you already have mysql enabled in php). You can read about this function here: mysql_fetch_array() function.

Try to replace this line in your code:
[php] for ($k = 0 ; $k < 4 ; ++$k) echo “

$row[$k]”;[/php]

with this:
[php]echo “

$row[‘Id’]”;
echo “$row[‘Family’]”;
echo “$row[‘Name’]”;
echo “$row[‘Age’]”;[/php]

Just make sure field names in the code above match your database table field names (case sensitive).

I tried to replace the line with the code snip above.
Netbeans hit me with errors and when I ran it screen was blank.
I’ll post the non-working code… I PUT SOME COMMENTS // next to the area that blows up my IDE
[php]

<?php /* * Select data from mysql database and display it to the screen */ require_once 'login.php'; $db_server = mysql_connect($db_hostname, $db_username, $db_password); if (!$db_server) die ("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database) or die("Unable to select database: " . mysql_error()); $query = "SELECT * FROM cats"; $result = mysql_query($query); if (!$result) die ("Database access failed: " . mysql_error()); $rows = mysql_num_rows($result); echo ""; for ($j = 0 ; $j < $rows ; ++$j); { $row = mysql_fetch_row($result); // I tried changing this to say as well.. $row = mysql_fetch_array($result) echo ""; // for ($k = 0 ; $k < 4 ; ++$k) echo ""; Commented this line out echo ""; echo ""; // THESE 3 LINES DISPLAY AN ERROR echo ""; // Description: echo ""; // Syntax error:expected: identifier, variable ${ echo ""; } echo "
Id Family Name Age
$row[$k]$row['Id']$row['Family']$row['Name']$row['Age']
"; mysql_close($db_server); ?>

[/php]

Well, try to change to this:
[php]$row = mysql_fetch_array($result);
echo “

”;
echo “”.$row[‘Id’]."";
echo “”.$row[‘Family’]."";
echo “”.$row[‘Name’]."";
echo “”.$row[‘Age’]."";
echo “”;[/php]

And make sure database table fields are spelled correctly: Id, Family, Name, Age (check if these are the same what you have in your MySQL database.

I’ve done away with the ‘mysql_fetch_row’ and switched over to the ‘mysql_fetch_array’
It was the capitalized names that was the error the whole time… wish netbeans would of said that haha… one day!

Anyway I’m still only pulling the first row.
So typical with computers, you make a thousand changes and wind up right where you started.

[php]
for ($j = 0 ; $j < $rows ; ++$j); // Is the problem in here? I copied this from a book, could be this?
{
echo “

”;
$row = mysql_fetch_array($result);
echo “”;
echo “”.$row[‘id’]."";
echo “”.$row[‘family’]."";
echo “”.$row[‘name’]."";
echo “”.$row[‘age’]."";
echo “”;
echo “”;
}
[/php]

Well, now I see what was the problem - you need to delete trailing semicolon here:

for ($j = 0 ; $j < $rows ; ++$j);
{

And I believe your original code with mysql_fetch_row() would also work fine if not this typo.

Holy Cannolie!!
Good eye phphelp that whole time it was the konfangled semi colon!
I was just about to poke my eyes out.
Now both versions work _array and _row
The cats are all in place and the earth can continue its rotation!!

BEWARE OF THE
;

Another related question.
Let me see if I can guess what this means.
for ($j = 0 ; $j < $rows ; ++$j)

When $j is equal to zero … run the code below
When $j is less than the number of table rows… run code below
Add +1 to $j each time this code below is exectued???

and I didnt say anything in the code about what $j is … Is it that $j becomes what is enclosed { just below?}

not quite…

$j is 0 to begin with and then increments with each pass to the next number until it matches the value of $rows.
the stuff in between the {} brackets is what the script will do while the value is true. IE: not greater than $rows.

(the variable $j itself can be anything you like… $i, $a, $bb, $dd etc etc)

Hope that clarifies things for you :wink:

Sponsor our Newsletter | Privacy Policy | Terms of Service