CODEWITHSUNDEEP
phpmysqlsql
doflamingo
doflamingo

Reputation: 565

PHP MySQL - How to fetch like this?

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

enter image description here

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?

Upvotes: 1

Views: 54

Answers (1)

Logan Wayne
Logan Wayne

Reputation: 5991

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.

Upvotes: 1

Related Questions