PHP - Displaying records from database with same column value

Question

I'm trying to display some records from the database with the same column value. It's being displayed but the problem is it's being displayed multiple times depending on how many rows did that particular column value has.

Example:

ID | NAME | TOPIC | COMMENT
===x======x=======x========
1  | Jane |  ABC  |  hello
2  | Doe  |  ABC  |  hello
3  | Mary |  ABC  |  hello
4  | Pop  |  DEF  |  hello
5  | Tris |  DEF  |  hello

If I try to display these records by TOPIC, it appears like this.

Topic: ABC

ID | NAME | TOPIC | COMMENT
===x======x=======x========
1  | Jane |  ABC  |  hello
2  | Doe  |  ABC  |  hello
3  | Mary |  ABC  |  hello

ID | NAME | TOPIC | COMMENT
===x======x=======x========
1  | Jane |  ABC  |  hello
2  | Doe  |  ABC  |  hello
3  | Mary |  ABC  |  hello

ID | NAME | TOPIC | COMMENT
===x======x=======x========
1  | Jane |  ABC  |  hello
2  | Doe  |  ABC  |  hello
3  | Mary |  ABC  |  hello

It displays three times because there are 3 entries with that topic. I want to display the records once if I choose a topic.

Here's my code:

<?php
if(isset($_POST['sortBtn']))
{
    $sortEvent = $_POST['sort_event'];
    $sortQuery = "SELECT event_topic FROM tbl_comment";
    $sortResult = mysqli_query($dbcon, $sortQuery);

    while($row = mysqli_fetch_assoc($sortResult))
    {
        if($sortEvent == $row['event_topic'])
        {

            $query = "SELECT * FROM tbl_comment WHERE event_topic = '". $sortEvent . "'";
            $result = mysqli_query($dbcon, $query);

            echo "<table class='tbEvents' border='1'>";
                echo "<tr>";
                    echo "<th class='tHead'>#</th>";
                    echo "<th class='tHead'>User's Name</th>";
                    echo "<th class='tHead'>Event Topic</th>";
                    echo "<th class='tHead'>Comment</th>";
                    echo "<th class='tHead'>Date / Time</th>";
                echo "</tr>";
                if ($result->num_rows > 0) {
                    while($row = $result->fetch_assoc()) {      
                        echo "<tr>";
                            echo "<td class='tData'>". $row["user_number"] ."</td>";
                            echo "<td class='tData'>". $row["user_nickname"] ."</td>";
                            echo "<td class='tData'>". $row["event_topic"] ."</td>";
                            echo "<td class='tData'>". $row["user_comment"] ."</td>";
                            echo "<td class='tData'>". $row["date_time"] ."</td>";

                        echo "</tr>";

                    }
                } else {
                    echo "0 results";
                }
            echo "</table>";
        }
    }
}
?>

I tried doing GROUP BY, ORDER BY, DISTINCT, and nothing works. It's either being displayed the same way, or I get an error.

EDIT:

If I add ORDER BY or GROUP BY, I get this error:

Trying to get property of non-object

on this line of code:

if ($result->num_rows > 0)

Your code is vulnerable to [SQL Injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — tyteen4a03, Jan 08 '17 at 01:02

vmachan · Answer 1 · 2017-01-08T01:21:27.137

UPDATED:to remove SQL Injection

Try the below, your original code has an outer loop which seems to be the issue

<?php
if(isset($_POST['sortBtn']))
{
    $sortEvent = $_POST['sort_event'];
    $sortQuery = "SELECT * FROM tbl_comment WHERE event_topic = ?";
    $sortQuery->bind_param('s', $_POST['sort_event']);
    $sortResult = mysqli_query($dbcon, $sortQuery);

    if ($sortResult->num_rows > 0) 
    {
        echo "<table class='tbEvents' border='1'>";
        echo "<tr>";
            echo "<th class='tHead'>#</th>";
            echo "<th class='tHead'>User's Name</th>";
            echo "<th class='tHead'>Event Topic</th>";
            echo "<th class='tHead'>Comment</th>";
            echo "<th class='tHead'>Date / Time</th>";
        echo "</tr>";
        while($row = mysqli_fetch_assoc($sortResult))
        {
             echo "<tr>";
                 echo "<td class='tData'>". $row["user_number"] ."</td>";
                 echo "<td class='tData'>". $row["user_nickname"] ."</td>";
                 echo "<td class='tData'>". $row["event_topic"] ."</td>";
                 echo "<td class='tData'>". $row["user_comment"] ."</td>";
                 echo "<td class='tData'>". $row["date_time"] ."</td>";
             echo "</tr>";
         }
        echo "</table>";
    } else {
        echo "0 results";
    }
}
?>

This is SQL injection vulnerable; http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php please patch before I get back my -1 — Blag, Jan 08 '17 at 01:17
Thanks, better, here take a +1 (and please you don't need to put two -1 on my questions, it's useless, if I temporary cast one on this answer it's because SQL injection is a real treat that SHOULD NOT be taken lightly. you can't help a beginner if you let this kind of thing in your answer); kind regards blag — Blag, Jan 08 '17 at 01:27
My bad, don't seem to come from your account (even if it was the same timing), someone seem to find funny to down-vote my questions / answers... never-mind u_u — Blag, Jan 08 '17 at 01:41

score 1 · Answer 2 · edited May 23 '17 at 12:33

When you run SELECT event_topic FROM tbl_comment, it fetches all the records in the table (then you later did manual comparison when you should have just used a WHERE).

You have 6 rows in the table. When $sortEvent is not ABC, the loop will exit immediately; that means your loop is run 3 times, meaning that the <table> is also displayed 3 times.

You can get rid of the outer loop altogether and use this query code below, which will prevent SQL Injections:

        $query = "SELECT * FROM tbl_comment WHERE event_topic = ?";
        $query->bind_param('s', $_POST['sort_event']);
        $result = mysqli_query($dbcon, $query);

PHP - Displaying records from database with same column value

2 Answers2