Duplication of results after selecting data from two tables with the same values

Question

Good day! I'm having a problem in duplication of results when I'm selecting data from two tables with the same values. I have one data in my first table homepost with an ID of 2 and I have three data in table multiple with an ID of 2.

So. In my source code. I'm selecting all the data with the same values ID. But it duplicates my one data from homepost because I have three data from multiple with the same values [ID]. Any help? Thank you!

For example in the table below:

Table [homepost]                    Table [multiple]

homeID   homeDesc               multipleID     multipleImage
2          John                     2              Image1
3        Samantha                   2              Image2
                                    2              Image3
                                    3              Image4
                                    3              Image5

Results of my code:

John
Image1
John
Image2
John
Image3
Samantha
Image4
Samantha
Image5

What I want is:

John
Image1
Image2
Image3

Samantha
Image4
Image5

Here's my Source Code:

<?php
    include ("dbconnect.php");

    $content_sql="SELECT post.postID, post.postTitle, post.postDesc, post.postImage 
                    FROM post 
                        JOIN category ON (post.categoryID=category.categoryID) 
                    WHERE post.categoryID=".$_GET['categoryID'];

    if($content_query=mysqli_query($con, $content_sql)) {
        $content_rs=mysqli_fetch_assoc($content_query);
    }

do {
    echo $content_rs['postTitle'];
    echo $content_rs['postDesc'];
} while ($content_rs=mysqli_fetch_assoc($content_query))

?>

Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Mar 12 '19 at 17:36
I'm just starting to understand how PHP works for now because I am just a beginner. I think security comes later. — Rojen Agustin, Mar 12 '19 at 17:40
You can use group_concat for this whcih will return all multipleImage name in comma separated form. Have a look of https://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql — Rohit Mittal, Mar 12 '19 at 17:45

RiggsFolly · Accepted Answer · 2019-03-12T18:20:40.197

0

You can simply check the name each time round the loop and only output it if its a new one

<?php
include ("dbconnect.php");

$sql="SELECT post.postID, post.postTitle, post.postDesc, post.postImage 
      FROM post 
          JOIN category ON (post.categoryID=category.categoryID) 
      WHERE post.categoryID=?";

$stmt = $con->prepare($sql);
$stmt->bind_param('i', $_GET['categoryID']);
$stmt->execute();

$result = $stmt->get_result();

$last_name = '';        
while ( $row = $result->fetch_assoc() ) {
    if ( $last_name != $row['postTitle'] ) {
        echo $row['postTitle'];
        $last_name = $row['postTitle'];
    }

    echo $row['postDesc'];
}
?>

edited Mar 12 '19 at 18:20

answered Mar 12 '19 at 17:40

RiggsFolly

93,638
21
103
149

I tried this one. This works with the `postTitle`. But the `postDesc` doesn't work because it is echoed by `content_rs` and it says `Unidentified Variable`. Any solution? I know it will get the error because there's no variable `$content_rs` to start with. – Rojen Agustin Mar 12 '19 at 17:50
Check the code again. I also made it bind the $GET parameter – RiggsFolly Mar 12 '19 at 17:52
Nevermind. I just changed `$content_rs` into `$row`. Its working now. Thanks alot! – Rojen Agustin Mar 12 '19 at 17:52

Duplication of results after selecting data from two tables with the same values

1 Answers1