PHP iterate through every row

Question

I'm trying to display the information from every row in my database. I have multiple rows with information that I'm going to use to output to my page.

Here's what my table looks like:

id | collection | name | image
-------------------------------
 1 |  Col One   |col-1 | url-1
-------------------------------
 2 |  Col Two   |col-2 | url-2

Here's what my code currently looks like:

$sql = "SELECT * FROM collections";
$result = $link->query($sql);
$collections = mysqli_fetch_array($result, MYSQLI_BOTH);

$html = '<div class="collection" id="' . $collections["name"] . '"><div class="collection-hover">' . $collections["collection"] . '</div></div>';
echo '<div id="collectionsDiv">';
while ($row = $result->fetch_assoc()) {
    echo $html;
}
echo '</div>';

My code currently puts only the first row into the $collection variable, and it echoes everything correctly for that first row. But, I need every row to print to the page. I've found mysqli_fetch_all, and when I do a var_dump, I get all of the rows. However, my while loop breaks and I get "undefined index" errors for "name" and "collection" in my $html variable line.

As per answers I've found on this site, I've tried these loops:

while($collections = mysqli_fetch_array($result)) {
    $summary = $collections['collection'];
echo $summary;
}

while($row = mysqli_fetch_assoc($result)) {
    var_dump($row);
}

$rows = new MySqlResult($collections);
foreach ($rows as $row) {
    $summary = $row['collection'];
    echo $summary;
}

answers found here: MySQL Display Multiple Rows & MySQL returns only one row & PHP mySQLi_fetch_all: iterate through each row

The first two only return the last row, and the last one doesn't work (I think the MySqlResult may have depreciated. I tried mysqli_result in its place, but it didn't work either).

I'm not sure what piece of the puzzle I'm missing.

If I were to use mysqli_fetch_all, how could I get all of the information for each item? Is there a better way to do this? Maybe I could pass the fetch_all array ID in the $html variable as well, but I'm not sure how I would do that either.

I'm very new to PHP, so pardon me if this is really simple.

whats so confusing about this, just fetch the whole result on the first `while` block and thats it, it will continually move the pointer until the very last row. if you invoke another `while fetch` block, it'll just result into `null`, you don't need multiple `while fetch` blocks, and don't use a stray `fetch_array` outside your `while` block, or just put it inside a container first (an array), put all the values in there, then just reuse that array into various parts of the code — Kevin, May 05 '16 at 02:55
I do only have only one while block running at a time. I didn't specifically state this, but I test one while the rest are commented out. Every while block I've tried only gives one result. — Christy, May 05 '16 at 03:01

score 2 · Accepted Answer · answered May 05 '16 at 02:55

2

Fetch rows in a loop using mysqli_fetch_assoc. In the loop use variable returned by this function:

<?php
if (! $r = $link->query("SELECT * FROM collections")) {
    // handle error
}

echo '<div id="collectionsDiv">';

while ($row = $r->fetch_assoc()) {
    echo <<<EOS
    <div class="collection" id="{$row['id']}">
        <div class="collection-hover">{$row['collection']}</div>
    </div>
EOS
}

echo '</div>';

$r->free();

answered May 05 '16 at 02:55

Ruslan Osmanov

20,486
7
46
60

This is amazing, thank you so much. It's working as it should, and, of course, I was just over complicating it. – Christy May 05 '16 at 03:07

score 0 · Answer 2 · answered May 05 '16 at 02:56

 $query = "SELECT * FROM table";
    $result = mysqli_query($con, $query);
    $ALLDATA = array();

    while ($record = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
      array_push($ALLDATA, $record);
    }

mysqli_free_result($result);

$ALLDATA will now have all of the data, and you can do what you need with it.

score -1 · Answer 3 · answered May 05 '16 at 03:02

@Ruslan's answer is excellent. A mysqli version would be:

$sql = "SELECT * FROM collections";
$result=mysqli_query($link,$sql);
echo '<div id="collectionsDiv">';
while($row=mysqli_fetch_assoc($result)){
    echo <<<EOS
    <div class="collection" id="{$row["id"]}">
        <div class="collection-hover">{$row["collection"]}</div>
    </div>
EOS
}
echo '</div>';

PHP iterate through every row

3 Answers3