PHP why my while loop echos only one item while i have 3 items in mysql

Question

im trying to echo a product list with all my my products, yet i have 3 products in mysql. but from some reason when i echo my list it shows only one product the first one the list. while i want to see all the products (3).

if this may be relevant i'm using: Server version: 5.5.36 - MySQL and [PHP: 5.4.27]

    // This block grabs the whole list for viewing
include "../sitescripts/connect_to_mysql.php";
    $product_list = "";
    $sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC");
    $productCount = mysql_num_rows($sql); // count the output amount
    if ($productCount > 0) {
        while($row = mysql_fetch_array($sql)){ 
                 $id = $row["id"];
                 $product_name = $row["product_name"];
                 $price = $row["price"];
                 $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
                 $product_list = "Product ID: $id - <strong>$product_name</strong> - $$price - <em>Added $date_added</em> &nbsp; &nbsp; &nbsp; <a href='inventory_edit.php?pid=$id'>edit</a> &bull; <a href='inventory_list.php?deleteid=$id'>delete</a><br />";
        }
    } else {
        $product_list = "You have no products listed in your store yet";
    }

can you post the result of `SELECT * FROM products ORDER BY date_added DESC`? — Federico Piazza, Jul 29 '14 at 23:11
You are overwriting your variables on each `while()` loop, so only the last will be set. Store each as an array, and then loop over each array — Sean, Jul 29 '14 at 23:22

score 2 · Accepted Answer · answered Jul 29 '14 at 23:32

your product list is only showing the last item in your rows.

$product_list = "Product ID: $id - <strong>$product_name</strong> - $$price - <em>Added $date_added</em> &nbsp; &nbsp; &nbsp; <a href='inventory_edit.php?pid=$id'>edit</a> &bull; <a href='inventory_list.php?deleteid=$id'>delete</a><br />";

is overwriting the value on each iteration, I think you meant to do:

$product_list .= "Product ID: $id - <strong>$product_name</strong> - $$price - <em>Added $date_added</em> &nbsp; &nbsp; &nbsp; <a href='inventory_edit.php?pid=$id'>edit</a> &bull; <a href='inventory_list.php?deleteid=$id'>delete</a><br />";

for appending the string value over each iteration. Note the period before the = sign.

Wow this the correct answer that dot cost me a few day probably a week thank you much ! — Zip, Jul 30 '14 at 06:28

score 1 · Answer 2 · edited May 23 '17 at 10:33

NOTE:

You should echo the $product_list variable INSIDE your IF ELSE condition. NOT AFTER the conditions. The reason for having ONLY the last set of row is because the last set of row would overwrite the previous rows of the loop.
I would also suggest using MySQLi instead of deprecated MySQL.
Use at least mysqli_real_escape_string function to variables before using them to prevent SQL injections.

If you convert your code to MySQLi, it would look like this:

connect_to_mysql.php:

<?php

/* ESTABLISH CONNECTION */

$con=mysqli_connect("YourHost","YourUsername","YourPassword","YourDatabase"); /* REPLACE NECESSARY DATA */

if(mysqli_connect_errno()){

echo "Error".mysqli_connect_error();
}

?>

Your fetch/main file:

    include "../sitescripts/connect_to_mysql.php"; /* INCLUDE CONNECTION */
    $product_list = "";
    $sql = mysqli_query($con,"SELECT * FROM products ORDER BY date_added DESC");
    $productCount = mysqli_num_rows($sql); // count the output amount
    if ($productCount > 0) {
        while($row = mysqli_fetch_array($sql)){ 

                 /* USED mysqli_real_escape_string FUNCTION TO PREVENT SQL INJECTION */

                 $id = mysqli_real_escape_string($con,$row["id"]);
                 $product_name = mysqli_real_escape_string($con,$row["product_name"]);
                 $price = mysqli_real_escape_string($con,$row["price"]);
                 $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
                 $product_list = "Product ID: ".$id." - <strong>".$product_name."</strong> - $".$price." - <em>Added ".$date_added."</em> &nbsp; &nbsp; &nbsp; <a href='inventory_edit.php?pid=$id'>edit</a> &bull; <a href='inventory_list.php?deleteid=$id'>delete</a><br />";

                 echo $product_list; /* PRINT $product_list VARIABLE */

        } /* END OF WHILE LOOP */

    } /* END OF $productCount VARIABLE IS MORE THAN 0 */

    else {
        $product_list = "You have no products listed in your store yet";
        echo $product_list; /* PRINT $product_list VARIABLE */
    } /* END OF ELSE */

    /* I ASSUME YOU TRY TO PRINT THE $product_list VARIABLE IN HERE */

Whilst this is very informative, problems in OPs code still remain. This is completely off topic for the question at hand. — Daryl Gill, Jul 30 '14 at 01:08
@DarylGill - I answered it already. The first one on my note. OP stores the needed data inside $product_list variable and echoes it after the IF ELSE conditions which OP should have print INSIDE the conditions/loop. IF you read and try to analyze my answer, I answered the problem and your down vote is not helpful and no evidence at all. — Logan Wayne, Jul 30 '14 at 01:10
The error at hand is,because OP is overwriting the variables set in the while loop on every iteration, so once the 4th iteration finished values of those variables will be to only the last iteration which took place. The information you have provided is unclear to that result — Daryl Gill, Jul 30 '14 at 01:22
, also. OP might be developing on older versions of PHP which depreciation notes are not present & will have an entire package developed around mysql_* functions, so answering using the incorrect library is also a reason for down voting. By all means, I agree with your statements and see that there Is a resolution there. Just not the correct library — Daryl Gill, Jul 30 '14 at 01:22
@DarylGill - the version that the OP uses is one step behind the latest version. And as I've said, I already did try to explain the OP's problem on my first note. — Logan Wayne, Jul 30 '14 at 01:33

PHP why my while loop echos only one item while i have 3 items in mysql

2 Answers2