PHP, MySQL- "mysql_fetch_ array won't display all rows".

[derek02]

mysql_fetch_array doesn't return all rows in a table.
I am completely new to MySQL and PHP so I hope I can explain what my problem is. A new site that I am working on has content that is fed from a database. Each webpage has to extract certain rows FROM the table WHERE the field in a column is a certain "county". I am working on the following script which does pretty much what it needs to except it doesn't display all the rows that are called. It seems to leave out at least one row. In phpMyAdmin all fields are set to NULL and type is set to TEXT. Someone in another forum said that the script was fetching one row and not doing anything with it then continuing to the while statement. Can someone confirm and better explain this. Thanks for having a look. Here is the script (not finished)...

<?php
$DBhost = &quot;localhost&quot;;
$DBuser = &quot;****&quot;;
$DBpass = &quot;****&quot;;
$DBname = &quot;homesubmit&quot;;
$table = &quot;sellersinfo&quot;;
$caswell = &quot;Caswell&quot;;
mysql_connect($DBhost, $DBuser, $DBpass) or die (&quot;Unable to connect to database.&quot;
mysql_select_db(&quot;$DBname&quot or die (&quot;Unable to select database.&quot;
$ysql = &quot;SELECT * FROM $table WHERE county = '$caswell'&quot;;

$yquery = mysql_query($ysql) or die (&quot;Unable to query database.&quot;
$myresult = mysql_fetch_array($yquery);
while($myresult = mysql_fetch_array($yquery)) {
$myforsaleby = stripslashes ($myresult['forsaleby']);
$mycounty = stripslashes ($myresult['county']);
$mycity = stripslashes ($myresult['city']);
$mylotsize = stripslashes ($myresult['lotsize']);
$mysquarefeet = stripslashes ($myresult['squarefeet']);
$mybedrooms = stripslashes ($myresult['bedrooms']);
$mybaths = stripslashes ($myresult['baths']);
$myutilities = stripslashes ($myresult['utilities']);
$mycitytaxes = stripslashes ($myresult['citytaxes']);
$myprice = stripslashes ($myresult['price']);
$mycomments = stripslashes ($myresult['comments']);

echo &quot;<td class=\&quot;listingscontent\&quot; colspan=\&quot;2\&quot; rowspan=\&quot;1\&quot;><a href=\&quot;temp.html\&quot;><img class=\&quot;nobord\&quot; src=\&quot;myhome.jpg\&quot; width=\&quot;150\&quot; height=\&quot;100\&quot; alt=\&quot;home\&quot;>$myforsaleby, $mycounty, $mycity, $mylotsize, $mysquarefeet, $mybedrooms, $mybaths, $myutilities, $mycitytaxes, $myprice, $mycomments</a></td></tr>&quot;;

}
mysql_close();
?>

 

[DRJ478]

The mistake is in re-using the $myresult variable.
First you retreive the result set of the SQL query into $myresult.
$myresult is a resource set.
In your while loop you reassign the first row you retreive from the resource set to $myresult and therefore destroy the ability to get the other rows.

Use:
while($row = mysql_fetch_array($result){
etc.
}

That's all.

 

[DRJ478]

I had to amend this.
Your code actually confused me the same way it's confusing you.

You retreive the $myresult before the while loop.
That is the first row. It will be missing because you retreive the second row in the while statement.

Please disregard the previous post.

 

[derek02]

Thanks for the quick reply DRJ478. I actually deleted this part - $myresult = mysql_fetch_array($yquery);
It now sends the correct amount of entries to my website but it doesn't echo the information into the entries. I think my brain is actually shrinking from all this confusion! Let me post what I have now...


<?php
$DBhost = &quot;localhost&quot;;
$DBuser = &quot;****&quot;;
$DBpass = &quot;****&quot;;
$DBname = &quot;homesubmit&quot;;
$table = &quot;sellersinfo&quot;;
$caswell = &quot;Caswell&quot;;
mysql_connect($DBhost, $DBuser, $DBpass) or die (&quot;Unable to connect to database.&quot;
mysql_select_db(&quot;$DBname&quot or die (&quot;Unable to select database.&quot;
$ysql = &quot;SELECT city FROM $table WHERE county = '$caswell'&quot;;

$yquery = mysql_query($ysql) or die (&quot;Unable to query database.&quot;

while( mysql_fetch_array($yquery)) {
$myforsaleby = ($myresult['forsaleby']);
$mycounty = ($myresult['county']);
$mycity = ($myresult['city']);
$mylotsize = ($myresult['lotsize']);
$mysquarefeet = ($myresult['squarefeet']);
$mybedrooms = ($myresult['bedrooms']);
$mybaths = ($myresult['baths']);
$myutilities = ($myresult['utilities']);
$mycitytaxes = ($myresult['citytaxes']);
$myprice = ($myresult['price']);
$mycomments = ($myresult['comments']);

echo &quot;<td class=\&quot;listingscontent\&quot; colspan=\&quot;2\&quot; rowspan=\&quot;1\&quot;><a href=\&quot;temp.html\&quot;><img class=\&quot;nobord\&quot; src=\&quot;myhome.jpg\&quot; width=\&quot;150\&quot; height=\&quot;100\&quot; alt=\&quot;home\&quot;>$myforsaleby, $mycounty, $mycity, $mylotsize, $mysquarefeet, $mybedrooms, $mybaths, $myutilities, $mycitytaxes, $myprice, $mycomments </a></td></tr>&quot;;

}
mysql_close();
?>

 

[DRJ478]

What happened to $myresult?
You have now:
while( mysql_fetch_array($yquery)) {

It should read:
while( $myresult =mysql_fetch_array($yquery)) {

You need to assign the returned row to a variable.
That's what is missing now.

 

[derek02]

It works!! I owe you big time DRJ478. Thanks!!