CODEWITHSUNDEEP
phpmysql
user7389260
user7389260

Reputation:

PHP - Displaying records from database with same column value

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)

Upvotes: 2

Views: 39

Answers (2)

vmachan
vmachan

Reputation: 1682

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";
    }
}
?>

Upvotes: 1

tyteen4a03
tyteen4a03

Reputation: 1932

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);

Upvotes: 1

Related Questions