Output distinct values in SQL column with PHP

Question

I have a table with two collumns (shortened), NAME and CATEGORY.

I want to output the number of distinct categorys. For an examle: Sport : 5 , Houses : 10.

I use this one:

$test = mysqli_query($con,"SELECT category, COUNT(category) as count FROM tablename GROUP BY category ORDER BY count DESC");

This work then I run the code in SQL Shell, but I have no clue on how to output it in PHP. I have searced Google up and down without any successfull solution.

Any help?

I want to output it in a table format.

EDIT: Here is my full code: (tablename is changed, and $con is removed)

$test = mysqli_query($con,"SELECT DISTINCT lkategori, COUNT(lkategori) as count FROM tablename GROUP BY lkategori ORDER BY count DESC");

while($row = mysql_fetch_array($test)) {
    echo $row['lkategori'] . ":" . $row['count'];
    die("test");
}

RJK · Accepted Answer · 2013-11-25 12:57:05Z

1

$test = mysqli_query($con,"SELECT DISTINCT lkategori, COUNT(lkategori) as count FROM tablename GROUP BY lkategori ORDER BY count DESC");

echo "<table border='1'>";
    while($row = mysqli_fetch_array($test)) {
        echo "<tr>";
        echo "<td>" . $row['lkategori'] . "</td>";
        echo "<td>" . $row['count'] . "</td>";
        echo "</tr>";
    }
echo "</table>";

This will output all the categories and the count returned by the sql statement into a table. Also as a sidenote you should look into PDO.

EDIT: to make sure you do get the distinct values you should use the DISTINCT keyword in your sql statement:

$test = mysqli_query($con,"SELECT DISTINCT category, COUNT(category) as count FROM tablename GROUP BY category ORDER BY count DESC");

edited Nov 25, 2013 at 12:57

answered Nov 25, 2013 at 12:14

RJK

1519 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

angribirdi Over a year ago

I now have the code You gave. But it still wont output anything. I have put in a die('') in the while() code, and it dont "die". So what is wrong with the while code?

RJK Over a year ago

Edit your post and give the full code so I can see how it is layed out.

Strawberry Over a year ago

Obviously that should be mysqli_fetch_array() - and DISTINCT can do nothing for you here.

user7789076 · Accepted Answer · 2013-11-25 12:51:22Z

0

use this

while($row = mysqli_fetch_array($test)) {
    echo $row['lkategori'] . ":" . $row['count'];
    die("test");
} 
Thanks

answered Nov 25, 2013 at 12:51

user7789076

7983 gold badges12 silver badges25 bronze badges

1 Comment

mickmackusa Over a year ago

Please never post "use this" (code-only) answers on Stack Overflow. Every answer should include an educational explanation.

Collectives™ on Stack Overflow

Output distinct values in SQL column with PHP

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related