PHP sort MySQL results

[CodilX]

Hi :)

I'm having trouble sorting my MySQL results in php.

Here's the database I have

id¦ value ¦ some info 
--------------------------
1 ¦ name1 ¦ info
2 ¦ name1 ¦ info 
3 ¦ name2 ¦ info
4 ¦ name2 ¦ info 
5 ¦ name2 ¦ info
6 ¦ name3 ¦ info

What I'm trying to do, is to get a result in PHP looking like this:

name1 - 2results
name2 - 3results
name3 - 1result

the idea is for the script to show what values there are in the 'value' column, and show how many are there.

I made this working fine by searching for the exact value and then counting them, but the issue is that the there will be hundreds of different value.

thanks in advance :)

[coopster]

COUNT is an aggregate function. You can use a COUNT but don't forget to GROUP BY value to get what you desire here.

[surrealillusions]

is this what your after?

mysql_query ("SELECT * FROM database ORDER BY value DESC")

Not sure about counting though..maybe theres some sort of function to find how many id's there are...

[eelixduppy]

>> is this what your after?

No, that would retrieve the data from the table however it will not group by the name value. To do this, you would have to use GROUP BY, as coopster has suggested. An example query using GROUP BY would be as follows:

$query = "SELECT * FROM `table_name` GROUP BY `value` ORDER BY `id` DESC";

[coopster]

Don't forget the COUNT!

SELECT 
  value, 
  COUNT(value) AS count 
 FROM table  
 GROUP BY value 
 ORDER BY value 
;

Regarding aggregate functions and columns in the SELECT list, this additional information about the The Single-Value Rule [webmasterworld.com] may be handy in the future.

[eelixduppy]

hehe - I actually didn't see what was being counted in the first place and misunderstood what was wanted in the original thread, coop; thanks for clarifying. :)

[CodilX]

this works great, thanks :)

just one more thing.. the values in the table are a list of lets say fruit, I need them to be sorted and shown by the first letter of the word. sort of like WikiPedia style listing, so for example this is what it would look like:

A:
Apple - 2
C:
Cherry - 4
Cranberry - 1
M:
Mango - 5
Melon - 3
Mandarin - 3

[eelixduppy]

You are going to have to use PHP itself to implement this kind of sorting. Here is a watered down example without the MySQL implementation:

$fruits = array('apple','cherry','cranberry','mango','melon','manderin');
$last = '';

foreach($fruits as $fruit) {
if($last != $fruit[0]) {
echo '<h3>'.$fruit[0].'</h3>';
$last = $fruit[0];
}
echo $fruit.'<br/>';
}

This should at least give you the right idea so that you can implement your own. :)

[CodilX]

thank you, it works well with the array of fruits, but how do I get that kind of array out of MySQL?

[eelixduppy]

The mysql query will give you the array of fruits sorted in the same manner as I have them in the array above and you would have to do the same check that I gave an example of above with each iteration. It would look something more like this; my above code was just an example of the method involved.

$query = "SELECT value, COUNT(value) AS count FROM table GROUP BY value ORDER BY value";
$result = mysql_query($query) or die mysql_error());
$last = '';
while($row = mysql_fetch_assoc($result)) {
if($last != $row['value'][0]) {
echo '<h3>'.$row['value'][0].'</h3>';
$last = $row['value'][0];
}
echo $row['value'].'<br/>';
}

[CodilX]

works perfectly, thank you so much!

[eelixduppy]

Glad everything worked out for you :)