SELECT DISTINCT

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ardan16
Forum Commoner
Posts: 32
Joined: Fri Aug 01, 2008 6:07 am

SELECT DISTINCT

Post by ardan16 »

Hi all,
Can someone please point me in the right direction.

If I have a table called banks2 with a field called name and it could have any number of entries that are the same.
Eg
Netbank
Netbank
Commonwealth
Netbank
Westpac

How would I loop through to get the number of times each name appears is it SELECT DISTINCT or will that only give me each unique name and therefore 1 entry. I have the code below to get one name.

Code: Select all

$query = 'SELECT * FROM `banks2` WHERE '
        . ' `name`= \'Commonwealth\' LIMIT 0, 30 ';     
$result = @mysql_query ($query); // Run the query.
$num1 = mysql_num_rows($result);
if ($num > 0) { // If it ran OK, display the records.
 
} else { // If it did not run OK.
    echo '<p class="error">There are currently no entries.</p>';
}
 
echo '
<tr><td >Commonwealth Bank</td><td ><img src="includes/column.gif" width="'.( $num * 10).'" height="10" />   '.$num.'</td></tr>
';
 
User avatar
idevlin
Forum Commoner
Posts: 78
Joined: Tue Jun 26, 2007 1:10 pm
Location: Cambridge, UK

Re: SELECT DISTINCT

Post by idevlin »

You can combine select count(*) with DISTINCT, and it should give you the name and the number of times it appea.s

Something like:

Code: Select all

SELECT name, DISTINCT count(*) FROM banks2
or something like that. I can't test it as I don't have access to MySql at work.
Last edited by idevlin on Tue Aug 12, 2008 8:52 am, edited 1 time in total.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: SELECT DISTINCT

Post by EverLearning »

I think what you want is

Code: Select all

$query = '
SELECT `name`, COUNT(*) as num 
    FROM `banks2` 
    GROUP BY `name`';     
 
It will return banks grouped by name, and the number of times the name appears in the table.
User avatar
idevlin
Forum Commoner
Posts: 78
Joined: Tue Jun 26, 2007 1:10 pm
Location: Cambridge, UK

Re: SELECT DISTINCT

Post by idevlin »

Indeed, listen to EverLearning and ignore me :D
ardan16
Forum Commoner
Posts: 32
Joined: Fri Aug 01, 2008 6:07 am

Re: SELECT DISTINCT

Post by ardan16 »

Thank you both,
Just trying to get that to reflect table.
ardan16
Forum Commoner
Posts: 32
Joined: Fri Aug 01, 2008 6:07 am

Re: SELECT DISTINCT

Post by ardan16 »

Lost here with table??????

Code: Select all

$query = 'SELECT `name`, COUNT(*) as num FROM `banks2` GROUP BY `name`';        
$result = @mysql_query ($query); // Run the query.
$num = mysql_num_rows($result);
if ($num > 0) { // If it ran OK, display the records.
 
} else { // If it did not run OK.
    echo '<p class="error">There are currently no entries.</p>';
}
 
 
while ($num = mysql_fetch_array($result, MYSQL_ASSOC)) {
        
echo '
<tr><td >' . $num['name'] . '</td><td ><img src="includes/column.gif" width="'.( $num * 10).'" height="20"  />'.$num.'</td></tr>';
}   
 
    echo '</table>';
mysql_close(); // Close the database connection.
 
  
?>
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: SELECT DISTINCT

Post by EverLearning »

Code: Select all

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo '<tr><td >' . $row['name'] . '</td><td ><img src="includes/column.gif" width="'.( $row['num'] * 10).'" height="20"  />'.$row['num'].'</td></tr>';
}
ardan16
Forum Commoner
Posts: 32
Joined: Fri Aug 01, 2008 6:07 am

Re: SELECT DISTINCT

Post by ardan16 »

Thanks EverLearning,
I did copy that from some of my previous code however went and changed the row to num without first trying it.

Thanks once again.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: SELECT DISTINCT

Post by simonmlewis »

This seems to be a similar query to mine.

I have a table full of IDs and keywords (a search log of departments).

I want to list all the departments that have been searched for, but there will be duplicates. So rather than see:

commercial
commercial
commercial
commercial
commercial
commercial

I want to see:
commercial 5

I've tried various DISTINCT COUNT ideas, but nothing works.
Is it even possible?

Code: Select all

 
$result = mysql_query ("SELECT keywords, DISTINCT count(*) FROM deptlog");
while ($row = mysql_fetch_object($result)){
 
echo "<tr><td>&bull;&nbsp;$row->keywords</td>
Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SELECT DISTINCT

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
Post Reply