PHP MySQL - How to fetch like this?

Question

I have a database like this and want to fetch images to my website like this, see the screenshot

but I don't know, Did SQL can fetch like this? I know only

$sql = "SELECT id, photographer, image FROM MyTable";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_assoc($result))
                {
                    echo "<tr>";
                    echo "<td align='center'>".$i."</td>";
                    echo "<td>".$row['photographer']."</td>";
                    echo "<td><img src='".$row['image']."'></td>";

                    echo "</tr>";

                }

but it's not show what I want. How should I do? or I design database wrong?

Why are you echoing `username`, `password`, and `email`? Those aren't the columns you selected. — Barmar, Dec 09 '16 at 02:45
See http://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 for how you can keep related rows in the table together in the output. It's shows how to do it with a heading row for each group, but the same general idea can be used for putting all the images in the same row of the table. — Barmar, Dec 09 '16 at 02:46
You can also use `GROUP_CONCAT(image) AS images` to get all the images together for each photographer. Then in the PHP you can use `explode(',', $row['images'])` to turn them into an array, and put each image in the row. — Barmar, Dec 09 '16 at 02:50

score 1 · Answer 1 · answered Dec 09 '16 at 03:51

Database design is not all wrong. You can create another table for storing different photographers and make the photographer column of MyTable table as a foreign key.

photographer_tb:

id | photographer_name
---+--------------------
 1 | Photographer A
 2 | Photographer B
 3 | Photographer C

MyTable:

id | photographer_id |   image
---+-----------------+-----------
 1 |        1        |  foo1.jpg
 2 |        1        |  foo2.jpg
 3 |        1        |  foo3.jpg
 4 |        2        |  foo4.jpg
 5 |        2        |  foo5.jpg
 6 |        3        |  foo6.jpg

Then, you can have two (?) options for achieving the display that you want:

First is a JOIN query:

$sql = "SELECT * FROM MyTable a LEFT JOIN photographer_tb b ON a.id = b.photographer_id";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){

    if(empty($lastphotographer)){
        echo '<tr>
                  <td align="center">'.$row['photographer_name'].'</td>
                  <td>';
    } else if($lastphotographer != $row['photographer_name']){
        echo '</td></tr>
              <tr>
                  <td>'.$row['photographer_name'].'</td>
                  <td>';
    }

    echo '<img src="'.$row['image'].'">';

    $lastphotographer = $row['photographer_name'];

}

echo (mysqli_num_rows($result) > 0)?'</td></tr>':'';

Second is a nested loop. Loop first all the photographer, then run another loop inside for all of the linked images:

$sql = "SELECT id, photographer_name FROM photographer_tb";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result))
{

    echo '<tr>
              <td align="center">'.$row['photographer_name'].'</td>
              <td>';

    $sql2 = "SELECT images FROM MyTable WHERE photographer_id = '$row[id]'";
    $result2 = mysqli_query($conn, $sql);
    while($row2 = mysqli_fetch_array($result2)){

        echo '<img src="'.$row2['images'].'">';

    }

    echo '</td></tr>';

}

You can also refer to the comments of your post for other feasible options. And while you're using mysqli_* already, check prepared statement.

PHP MySQL - How to fetch like this?

1 Answers1