How to display multiple result from MySql query using PHP

Question

I have my current code working perfectly but I needed help on when I want the code to display as many queries exist in the table called wine... for example if there are more than wine type in category as spain then it should display all but it only displays one.

Here is my PHP:

<?php  

include"db_connection.php";

$sql = mysql_query("SELECT * FROM WINE WHERE country='Chile'");
    while($row = mysql_fetch_array($sql)){ 
         $description = $row["description"];
         $wine_type = $row["wine_type"];
         $country = $row["country"];
         $bottle_price = $row["bottle_price"];
         $indicator = $row["indicator"];
         $colour = $row["colour"];
         $case_price = $row["case_price"];
         $case_size = $row["case_size"];         
         $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));    
     }
?>

Here is my HTML:

<?php include('header.php'); ?>

<div id="content">
<table width="100%" border="0" cellspacing="0" cellpadding="15">
    <tr>
        <td width="19%" valign="top"><img src="inventory_images/<?php echo $id; ?>.jpg" width="142" height="188" alt="<?php echo $wine_type; ?>" /><br />
        <a href="inventory_images/<?php echo $id; ?>.jpg">View Full Size Image</a></td>
            <td width="81%" valign="top"><h3><?php echo $wine_type; ?></h3>
            <p><?php echo "$".$bottle_price; ?><br /><br />
                <?php echo "$country $indicator"; ?> <br /><br />
                <?php echo $description; ?> <br />
            </p>

              <form id="form1" name="form1" method="post" action="cart.php">
                <input type="hidden" name="pid" id="pid" value="<?php echo $id; ?>" />
                <input type="submit" name="button" id="button" value="Add to Shopping Cart" />
              </form>
            </td>
    </tr>
</table>
</div>

<?php include('footer.php'); ?>

@Fred-ii- I tried it still shows once is there any examples you can suggest — user3311898
– user3311898, Commented Mar 10, 2014 at 22:57
Actually, you should be using a foreach with your mysql_fetch_array — Funk Forty Niner
– Funk Forty Niner, Commented Mar 10, 2014 at 23:00

kero · Accepted Answer · 2014-03-10 22:57:19Z

2

The problem is currently that the while() loop will keep overriding the variables. This can be solved in a couple of ways, one would be that you save the entire fetched column in an array and use that array later for iteration.

$allRows = array();
while($row = mysql_fetch_array($sql)) {
    $allRows[] = $row;
}

Now, as mentioned above, iterate over $allRows in your template

<?php include('header.php'); ?>
<div id="content">
<table width="100%" border="0" cellspacing="0" cellpadding="15">
<?php
foreach ($allRows as $row) {
?>
    <tr>
      <td width="19%" valign="top"><img src="inventory_images/<?php echo $row['id']; ?>.jpg" width="142" height="188" alt="<?php echo $row['wine_type']; ?>" /><br />
      etc.
    </tr>
<?php
}
?>
</table>
</div>
<?php include('footer.php'); ?>

Here I addressed the variables as $row['...'] - if you don't want to change that part of the code, simply do the assigning at the beginning of the loop.

etc.
<?php
foreach ($allRows as $row) {
     $description = $row["description"];
     $wine_type = $row["wine_type"];
     //etc.
?>

A much cleaner solution (not mixing that much HTML and PHP which usually creates lots of confusion) would be using a template engine. Also don't use mysql_* functions in new code - they are deprecated. See this answer for more information.

answered Mar 10, 2014 at 22:57

kero

10.7k5 gold badges44 silver badges52 bronze badges

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

1 Comment

user3311898 Over a year ago

My code seems to work but then i noticed its overwriting all of the results to one @kingkero

Collectives™ on Stack Overflow

How to display multiple result from MySql query using PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related