[SOLVED] Simple SELECT all command not working right

[spock9458]

This is driving me nuts... I am trying to refresh my memory to do some PHP / MySql developing, and my first simple test code is not working right.  I'm not sure if this is a PHP or MySql problem, but basically I have entered 4 Companies in my "company" table.  In phpMyAdmin I can see all 4 entries plain as day.  However, when I pull up the php page, it only returns 3 of the companies.  Here is the code:

 

<?php

//Test Connection to DB

mysql_connect("*****", "*****",
"*****") or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("robg_members");

// Retrieve all the data from the "company" table
$result = mysql_query("SELECT * FROM company")
or die(mysql_error());  

// store the record of the "company" table into $row
$row = mysql_fetch_array( $result );

echo "<table border='1'>";
echo "<tr> <th>Company Name</th> <th>ID</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row['comp_name'];
echo "</td><td>"; 
echo $row['id'];
echo "</td></tr>"; 
} 

echo "</table>";

?>

 

I know I'm probably missing something stupid, but please somebody help point it out to me.

 

Thanks,

Rob

[cunoodle2]

Your problem is that you have "$row = mysql_fetch_array( $result );" once BEFORE the loop AND in the loop.  You need to get rid of that first one.  That first call is calling the first item in your result list, then when the loop starts it is pulling the second one.  With the way you have your code written you will ALWAYS be one short.  Try this..

 

<?php

//Test Connection to DB

mysql_connect("*****", "*****",
"*****") or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("robg_members");

// Retrieve all the data from the "company" table
$result = mysql_query("SELECT * FROM company")
or die(mysql_error());  

// store the record of the "company" table into $row
//$row = mysql_fetch_array( $result );
//I COMMENTED OUT THE ABOVE LINE AS YOU DON'T NEED IT!!!!!!


echo "<table border='1'>";
echo "<tr> <th>Company Name</th> <th>ID</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
   // Print out the contents of each row into a table
   echo "<tr><td>"; 
   echo $row['comp_name'];
   echo "</td><td>"; 
   echo $row['id'];
   echo "</td></tr>"; 
} 

echo "</table>";

?>

[PFMaBiSmAd]

$row = mysql_fetch_array( $result );

The above line of code fetches the first row from the result set but discards it because the contents it puts in $row is not used before the while() loop that is fetching and using the rows form the result set. Why do you have that line of code in your program?

[spock9458]

I knew it had to be simple.... THANKS cunoodle2!!  I'm sure I'll be back with more questions, this was great response time.

 

Rob

[cunoodle2]

No worries at all.  Welcome to the site.  To make your website more secure don't print the mysql error to the screen.  It's good while you are developing but never on an actual live site.  It will make your site less secure and allow issues with hackers/mysql injections.

 

Also since you are new you may just want to just jump right into php PDO statements.  They are a little harder to learn but are 10 times as secure as traditional mysql queries.