CODEWITHSUNDEEP
phpmysql
user3223205
user3223205

Reputation:

PHP Select from MySQL table and return column with most rows

I want to select from a mysql table and return the most popular string in a column

for example, if i have 6 rows and a column called producttype

producttype = 'One'
producttype = 'One'
producttype = 'Two'
producttype = 'Three'
producttype = 'Three'
producttype = 'Three'

it will return the string Three because there is more rows of that than anything else

Upvotes: 0

Views: 97

Answers (2)

d.raev
d.raev

Reputation: 9556

I guess you don't know how to make the DB connection and all so here is a full example:

<?php
    $db = mysql_connect($host, $DB_User, $DB_Pass);
    if(!$db){
        echo "Failed to connect to MySQL: " . mysql_connect_error();
        die;

    }

$query = "Select * "
        ."FROM my_table"
        ."GROUP BY producttype "
        ."ORDER BY COUNT(*) DESC"
        ."LIMIT 1";

$result = mysqli_query($db, $query);

$result = mysqli_query($con,"SELECT * FROM Persons");

$row = mysql_fetch_array($result);
if($row){
  echo 'most common type is: ' . $row['producttype'];
}

mysqli_close($db);

Upvotes: 0

juergen d
juergen d

Reputation: 204894

select producttype 
from your_table
group by producttype 
order by count(*) desc
limit 1

If you group by the producttype then you can use count() to determine the count of each group. Order by that and take only the first record.

Upvotes: 3

Related Questions