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
– Barmar, Commented Dec 9, 2016 at 2:45
See stackoverflow.com/questions/27575562/… 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
– Barmar, Commented Dec 9, 2016 at 2: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
– Barmar, Commented Dec 9, 2016 at 2:50

Logan Wayne · Accepted Answer · 2016-12-09 03:51:53Z

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.

Collectives™ on Stack Overflow

PHP MySQL - How to fetch like this?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related