CODEWITHSUNDEEP
phpmysqldatabase
Learn2Earn
Learn2Earn

Reputation: 33

How to display all rows of mysql_fetch_array using while loop

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; ?>

Upvotes: 0

Views: 4019

Answers (2)

Prav
Prav

Reputation: 2894

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.

Upvotes: 0

Jakub A.
Jakub A.

Reputation: 301

You are overwriting $product_list in every loop.

You have to concateate by concat operator .=

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

Upvotes: 1

Related Questions