Second 'Do While' loop is only showing last item

[computerbarry]

Hi all

The problem I’m having is the second while loop only shows the last item from the result/db??
Can’t figure out what’s going on, can anybody spot what I’m doing wrong?

Output displays the problem below.

<?php if($row_cnt): ?>
<ul class="gallery-portrait">
  <?php
    do {
      if ($layout == 'p') {
        print '<li>' . $img_name . '</li>';
      }
    } while ($stmt->fetch());
  ?>
  </ul>

<? else: ?>
  <p>No portraits found</p>
<? endif ?>

<?php if($row_cnt): ?>
  <ul class="gallery-landscape">
  <?php
    do {
      if ($layout == 'l') {
        print '<li>' . $img_name . '</li>';
      }
    } while ($stmt->fetch());
  ?>
  </ul>

<? else: ?>
  <p>No landscapes found</p>
<? endif ?>

Output

<ul class="gallery-portrait">
<li>Img name 1</li>
<li>Img name 2</li>
<li>Img name 3</li>
<li>Img name 4</li>
<li>Img name 5</li>
</ul>

<ul class="gallery-landscape">
<li>Img name 5</li>
</ul>

Thanks.

[Thallius]

You cannot use the fetch() command multiple times. In your first loop you move through all results, so in the next loop it will not start with the first result.
Easiest way to solve this would be you do a fetchAll() into an array and then loop through the array

[computerbarry]

Arrrh, yes this could be it, Thallius!
So what should I do? Change all instances of fetch() to fetchAll() ?

Do I need to change my $stmt = $mysqli->prepare("") or just add fecthAll() to the php?

Barry

[Thallius]

Just do a

$results = $stmt->fetchAll()

before the loops and in the loops use $results[index]

[computerbarry]

I’ll have to run some tests… lots of errors

On the right track anyhow, I’ll report back once I figure what is happening.

It makes more sense now after you mentioned:

You cannot use the fetch() command multiple times. In your first loop you move through all results, so in the next loop it will not start with the first result.

Barry

[computerbarry]

Ok, I’ve managed to add the below, no errors:

$resultSet = $stmt->get_result();
$data = $resultSet->fetch_all(MYSQLI_ASSOC);
$row_cnt = $resultSet->num_rows;

Just not sure how to loop through and display the results?

You said $results[index]

Could you show an example, maybe?

How should/would I change the below for example:

<?php if($row_cnt): ?>
  <ul class="gallery-landscape">
  <?php
    do {
      if ($layout == 'l') {
        print '<li>' . $img_name . '</li>';
      }
    } while ($stmt->fetch());
  ?>

Thanks

[m_hutley]

so $data is now an array of arrays; it contains all your data.

Walk the array in the manner of your choosing (foreach, for, while,etc)

[computerbarry]

After running some tests, this seems to work!

foreach($data as $row)
{
  if ($row['layout'] == 'p') {
     $imgname = $row['img_name'];
     print '<li>' . $imgname . '</li>';
 }
}

Thanks @m_hutley

so $data is now an array of arrays; it contains all your data.

[computerbarry]

Just wondering, my full script below.

I previously used bind_result:
$stmt->bind_result($true, $id, $img_name, $ref, $layout, $collection);
These are now redundant?
I needed the $collection and $description (not shown) for the page title and metadata before I ran the loops - these are now empty?

Full script

if (isset($collection)) {

  $stmt = $mysqli->prepare("

    SELECT TRUE
      , p.id 
      , p.img_name
      , p.ref
      , p.layout
      , c.collection
    
    FROM collection_photos p
      LEFT JOIN collection_details c ON ( p.collection = c.collection )
    WHERE c.collection = ?");

    $stmt->bind_param('s', $collection);
    $stmt->execute();

    $stmt->bind_result($true, $id, $img_name, $ref, $layout, $collection);
    //$stmt->store_result();
    $resultSet = $stmt->get_result();
    $data = $resultSet->fetch_all(MYSQLI_ASSOC);
    $row_cnt = $resultSet->num_rows;

    print $stmt->error; //to check errors

    $stmt->fetch();

Can I improve this?

Barry

[Thallius]

Yes you do not need the bind_results. In fact I never used that because you can always access the values by their array index

[computerbarry]

Cool

So how would you display, for example the value of collection just once using the array index?

And do I still need $stmt->fetch();

Barry

[computerbarry]

I’ve managed to fix everything, thanks for the input all!

I also came across a nice reference explaining: What is the difference between get_result() and store_result() in php - If helps anybody.

Barry