How to display all rows of mysql_fetch_array using while loop

Question

Unable to display all rows of mysql_fetch_array using while loop.

Hi. My problem is I am only able to display last row entered into database table. I am parsing form data with php to insert into a database table. I know that the form data is being inserted into the table. I am using a while loop to attempt to extract data from two columns in all of the rows of the table. I am using php to display the data of the two columns id & product_name in each row. It is my understanding that a mysql_fetch_array will only return 1 row unless it is used in conjunction with a while loop, which then should return all rows. So I am confused why I can only out put the last row.

When I browse the table in phpMyAdmin there are three rows each with an id number. They are ordered sequentially 1, 2, 3 because the id column is auto increment. id column is primary key. product_name column is unique key.

I don't think there is a problem with my query structure. Possibly a problem defining $product_list variable ? I don't know.

I have closed browser and cleared all history, cookies etc. I have stopped & restarted Apache server & mysql.

When I echo var_dump ($sql); I get: resource(6) of type (mysql result) . When I echo var_dump ($product_list); I get: string(99) "3 - Blue Jeans edit • delete " When I print_r ($sqL); I get: Resource id #6 When I echo $product_list; I get: 3 - Blue Jeans edit • delete

I have spent a lot of time searching for answer to this via google searches but none seem to fit this particular problem.

I did see one similar question suggesting using a concatenation approach, but there were zero (0) up-votes. So I wasn't sure if this was a viable remedy to my problem.

Here is the code I am using:

<?php
    //this block grabs the whole list for viewing 
    $product_list = "";
    $sql= mysql_query ("SELECT * FROM `products`");
    $product_count = mysql_num_rows ($sql);
    if ($product_count > 0) {
        while($row = mysql_fetch_array ($sql)) {
            $id = $row['id'];
            $product_name = $row ['product_name'];
            $product_list = "$id - $product_name &nbsp; &nbsp; &nbsp; <a               
            href='#'>edit</a> &nbsp; &bull; <a href='#'>delete</a>  
            <br/>";

        }
    } else {
        $product_list = "You have no products listed in your store yet";
    }
    ?>

    <?php echo $product_list; ?>

And do not use mysql_* functions anymore. They are depreciated. — Sayantan Das
– Sayantan Das, Commented Mar 18, 2017 at 18:11

Jakub A. · Accepted Answer · 2017-03-18 18:10:54Z

1

You are overwriting $product_list in every loop.

You have to concateate by concat operator .=

$product_list .= "$id - $product_name       <a href='#'>edit</a>   • <a href='#'>delete</a> <br/>";

answered Mar 18, 2017 at 18:10

Jakub A.

3113 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Learn2Earn Over a year ago

I added the dot after $product_list as you suggested and now I have an error message.Parse error: syntax error, unexpected '=' in C:\xampp\htdocs\tutorials\kensStore\store_admin\inventory_list.php on line 58

Learn2Earn Over a year ago

concant operator does work in this situation. I had made a stupid mistake. I had a space between the dot symbol and the equal symbol. Looked like ". =" instead of ".=" Correcting the concant operator resolved my issue. Thank you Jakub A.

Prav · Accepted Answer · 2017-03-18 18:32:44Z

0

Ok this answer might not what you're looking for but I only use mySQLi not the older version. I'll rewrite the your code with the newer syntax only because I know this would work. So here goes

<?php
  $conn = new mysqli($server, $username, $password, $database);
  $product_list = "";
  $sql = $conn->query("SELECT * FROM products");
  $product_count = mysqli_num_rows ($sql);
  if ($product_count > 0) {
        while($row = $sql->fetch_assoc()) {
            $id = $row['id'];
            $product_name = $row ['product_name'];
            $product_list = "$id - $product_name &nbsp; &nbsp; &nbsp; <a               
            href='#'>edit</a> &nbsp; &bull; <a href='#'>delete</a>  
            <br/>";

        }
    } else {
        $product_list = "You have no products listed in your store yet";
    }
  ?>

You would need to either add the $conn = new mysqli($server, $username, $password, $database); in mySQLi Object Orientated method or can replace $conn on the $sql = $conn->query("SELECT * FROM products"); line withe your existing one and this should work.

answered Mar 18, 2017 at 18:32

Prav

2,9132 gold badges24 silver badges34 bronze badges

1 Comment

Learn2Earn Over a year ago

Praveen M Thanks for the reply your method produces the same result as I had previously.

Collectives™ on Stack Overflow

How to display all rows of mysql_fetch_array using while loop

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related