Page 1 of 1

SELECT DISTINCT

Posted: Tue Aug 12, 2008 8:33 am
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>
';
 

Re: SELECT DISTINCT

Posted: Tue Aug 12, 2008 8:48 am
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.

Re: SELECT DISTINCT

Posted: Tue Aug 12, 2008 8:49 am
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.

Re: SELECT DISTINCT

Posted: Tue Aug 12, 2008 8:52 am
by idevlin
Indeed, listen to EverLearning and ignore me :D

Re: SELECT DISTINCT

Posted: Tue Aug 12, 2008 9:11 am
by ardan16
Thank you both,
Just trying to get that to reflect table.

Re: SELECT DISTINCT

Posted: Tue Aug 12, 2008 9:54 am
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.
 
  
?>

Re: SELECT DISTINCT

Posted: Tue Aug 12, 2008 10:02 am
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>';
}

Re: SELECT DISTINCT

Posted: Tue Aug 12, 2008 10:10 am
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.

Re: SELECT DISTINCT

Posted: Thu Nov 27, 2008 3:39 am
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

Re: SELECT DISTINCT

Posted: Thu Nov 27, 2008 4:54 am
by VladSun