Christy Christy - 1 year ago 87
SQL Question

PHP iterate through every row

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)) {

$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.

Answer Source

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

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>

echo '</div>';