Click here to Skip to main content
14,173,097 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have two tables: multiple_image and posts

The db structures ===>

multiple_image:

| id |  image | imagesid |
--------------------------
| 1  |  name  |    5557  |
| 2  |  name  |    5557  |
| 3  |  name  |    5585  |
--------------------------

posts:

| commentid |  comment  | iamgesid |
------------------------------------
|     1     |   fool    |   5557   |
|     2     |  fool2    |   5585   |


The output that I am trying to achieve is:
I want to display all the images for a comment which has the same imagesid, but my problem is that, the comment is repeating itself.

What I have tried:

$sql = "SELECT image, posts.imagesid, multiple_image.imagesid, comment
        FROM multiple_image JOIN posts ON (multiple_image.imagesid=posts.imagesid)";
$result = $conn->query($sql);

if (!$result) {
    trigger_error('Invalid query: ' . $conn->error);
}

if ($result->num_rows > 0) {

    // output data of each row
while($row = $result->fetch_assoc()) {

echo $row['comment'];
$imgs= "<div id='img_div'><img width='' src='upload/".$row['image']."' ></div>";
echo $imgs;
}
}
Posted
Updated 22-Feb-19 6:11am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

$sql = "SELECT distinct image, posts.imagesid, multiple_image.imagesid, comment
        FROM multiple_image JOIN posts ON (multiple_image.imagesid=posts.imagesid)";


Add keyword "distinct" in above query

(or) add extra line with keyword "Group by" as below

$sql = "SELECT image, posts.imagesid, multiple_image.imagesid, comment
        FROM multiple_image JOIN posts ON (multiple_image.imagesid=posts.imagesid)
        GROUP by image, posts.imagesid, multiple_image.imagesid, comment";
   
Comments
Galarist_00 21-Feb-19 6:46am
   
Still showing the same comment next to each images which has the same imageid
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You'll need to select the comments and images separately. I don't "do" PHP, but something like this should work:
$sql = "SELECT comment, imagesid FROM posts";
$result = $conn->query($sql);

if (!$result) {
    trigger_error('Invalid query: ' . $conn->error);
}

if ($result->num_rows > 0) {
    // NB: Use a prepared statement to avoid any possibility of SQL Injection:
    $stmt = $conn->prepare("SELECT image FROM multiple_image WHERE imagesid = ?");
    $stmt->bindParam(1, $imagesid);
    
    while($row = $result->fetch_assoc()) {
        // NB: Encode the comment to avoid XSS vulnerabilities:
        echo htmlspecialchars($row['comment']);
        
        $imagesid = $row['imagesid'];
        if ($stmt->execute()) {
            echo "<div id='img_div'>";
            while ($imgrow = $stmt->fetch()) {
                echo "<img src='upload/" . $imgrow['image'] . "'>";
            }
            echo "</div>";
        }
    }
}

NB: Since your column appears to be an integer, there probably wouldn't be any danger of SQL Injection[^] for the nested query in this case. But it's always a good idea to use properly parameterised queries anyway.
PHP: SQL Injection - Manual[^]
PHP: Prepared statements and stored procedures - Manual[^]
   
Comments
Galarist_00 23-Feb-19 9:27am
   
That looks like POD and I am working with Object-Oriented PHP

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190524.3 | Last Updated 22 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100