Power By 专否社区

Mysql/PHP code is returning all rows except first and last

Go To StackoverFlow.com

-2

I am having a time tryng to pull ALL records out of a database. For example I have the following 

$result = mysql_query("SELECT * FROM `plant_info` ORDER BY id LIMIT 0, 50") or die(mysql_error());
// Variables to pull from the database
// I know the line below is the culprit now, so i must change the code below correct? ///
$returneddata = mysql_fetch_array($result);
///////////////////////////////////////////
$LatinName = $returneddata['Latin_Name'];
$CommonName = $returneddata['Common_Name'];
$Category = $returneddata['Category'];
$Type = $returneddata['Type'];
$Fruit = $returneddata['Fruit'];
$Flower = $returneddata['Flower'];
$MinHeight = $returneddata['Min_Height'];
$MaxHeight = $returneddata['Max_Height'];
$MinWidth = $returneddata['Min_Width'];
$MaxWidth = $returneddata['Max_Width'];
$Exposure = $returneddata['Exposure'];
$Comments = $returneddata['Comments'];
$SoilType = $returneddata['Soil_Type'];
$Zone = $returneddata['Zone'];
$PotSize = $returneddata['Pot_Size'];
$CostPrice = $returneddata['Cost_Price'];
$RetailPrice = $returneddata['Retail_Price'];
$ImageName = $returneddata['Image_Name'];
$ImageNameThumb = $returneddata['Image_Name_Thumb'];
$num_rows = mysql_num_rows($result); echo "$num_rows Rows\n";

while ($row = mysql_fetch_array($result)) {


echo "                           <tr>
                            <td align=\"center\" bgcolor=\"#000000\">
                                <p><img src=\"$row[Image_Name]\" style=\"width:120px;height:auto;\"></p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Latin_Name]</p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Common_Name]</p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Category]</p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Type]</p>
                            </td>

                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Flower]</p>

                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                               <p>$row[Comments]</p>
                            </td>

                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                               <p><a href=\"editplant.php?get=$row[id]\">Edit</a></p>
                               <p><a href =\"print_sign_div.php?get=$row[id]\">Print</a></p>
                            </td>
                        </tr>

";
}

The issue i have is that it will pull all the records except for the first one. Every other record shows, there are only 5 records currently.

I am missing something silly I know it. I looked through questions and could not find the answer. Thanks

2012-04-05 22:53
by ATechGuy
If there are only 5 records, can you paste your DB for us - DaOgre 2012-04-05 22:55
I had that Norse, same thing : - ATechGuy 2012-04-05 23:06
I'm not sure what you mean Keaner. Try adding in $count = mysql_num_rows($result); print "Count is $count"; Before you do any of your looping to verify mysql is grabbing the number of rows you're expecting. If you can update the above with your full code you're now using (as well as a dump of "SELECT * from plant_info", that would help people be able to help yo - DaOgre 2012-04-05 23:13
So yep I'm an idiot, I don't even need the

$Variable = $returneddata['column_name'];

In this page. sorry guys, but thanks for all the help. need sleep much - ATechGuy 2012-04-05 23:27


3

It could be because you're calling the mysql_fetch_array command twice.

$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result)) {

You only need to declare mysql_fetch_array once, which is inside the while parameter.

This should be your code:

$result = mysql_query("SELECT * FROM `plant_info` ORDER BY id LIMIT 0, 30");
while ($row = mysql_fetch_array($result)) {

echo "                           <tr>
                            <td align=\"center\" bgcolor=\"#000000\">
                                <p><img src=\"$row[Image_Name]\" style=\"width:120px;height:auto;\"></p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Latin_Name]</p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Common_Name]</p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Category]</p>
                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Type]</p>
                            </td>

                            <td align=\"center\" bgcolor=\"#90c084\">
                                <p>$row[Flower]</p>

                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                               <p>$row[Comments]</p>
                            </td>

                            </td>
                            <td align=\"center\" bgcolor=\"#90c084\">
                               <p><a href=\"editplant.php?get=$row[id]\">Edit</a></p>
                               <p><a href =\"print_sign_div.php?get=$row[id]\">Print</a></p>
                            </td>
                        </tr>

";
}
2012-04-05 23:02
by Norse
Cal's answer below got me all but the very first result.

$numrows = mysqlnumrows($result); echo "$numrows Rows\n";

Gives me "5 - ATechGuy 2012-04-05 23:06

Cal's suggestion is the correct syntax. It should be giving you all your results unless you're implementing it wrong. I'll update my answer for you - Norse 2012-04-05 23:12
Ill update my full code, standby : - ATechGuy 2012-04-05 23:18
Updated Above : - ATechGuy 2012-04-05 23:23

1

you didn't post valid/all your PHP - there's no mysql_query() call.

SELECT * FROM `plant_info` ORDER BY id LIMIT 0, 30
$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result)) {

line 2 there fetches a row and does nothing with it anyway, throwing away the first row. try this:

$result = mysql_query("SELECT * FROM `plant_info` ORDER BY id LIMIT 0, 30");
while ($row = mysql_fetch_array($result)) {

After your question update, it looks like you want to pull out info from the first row, then loop over all the rows. Here's a way to do that:

$result = mysql_query("SELECT * FROM `plant_info` ORDER BY id LIMIT 0, 30");
while ($row = mysql_fetch_array($result)) {
  $rows[] = $row;
}

$row = $rows[0];
$LatinName = $returneddata['Latin_Name'];
...

foreach ($rows as $row){
    ...
}
2012-04-05 22:59
by Cal
Thanks, that got me 4 out of my 5 to return. Only one missing now is the very first one - ATechGuy 2012-04-05 23:03
Can you update your questions to show your full code? You should reduce the example code as much as possible to isolate the issue - just echo one line in each loop. The less code you have, the easier it is to spot the problem - Cal 2012-04-05 23:05
I also have one of these in there

$returneddata = mysqlfetcharray($result);

Which was taking my first result, but i use this to display data like so:

$LatinName = $returneddata['Latin_Name'];

hmmm - ATechGuy 2012-04-05 23:11

you must post all your code in the question. we can't guess what it looks like - Cal 2012-04-05 23:14
come on cal your not a psychic? :). I got it though thanks, see comment above - ATechGuy 2012-04-05 23:30
aha, updated my answe - Cal 2012-04-05 23:55
Ads