Sort categories in A-Z order

[moazam]

Hi,
I am having a problem in sorting category, here is what i am doing..

SELECT S.subcat_name, count(S.subcat_id) AS total FROM subcategory S LEFT OUTER JOIN files F ON F.subcat_id=S.subcat_id GROUP BY F.subcat_id ORDER BY S.subcat_name ASC

this SQL query will result like this

Arts (1) (this is empty but still showing 1)
Celebrity(5)

I want to display result like this http://tinyurl.com/yt5z78

Please help me to correct this query and also suggest me how to display results just like the below sample shows you..

A
ABC (1)
DEC (55)

B
XYZ (23)
JOO (0)

Thanks

[Markus]

Please show the code you use to display the data.

Use CODE=PHP tags.

[moazam]

its very simple.. i just don't know to put category names under A-Z
[PHP]
$a = $mydb->query("SELECT S.subcat_name, count(S.subcat_id) AS total FROM subcategory S LEFT OUTER JOIN files F ON F.subcat_id=S.subcat_id GROUP BY F.subcat_id ORDER BY S.subcat_name ASC");

while($b = mysql_fetch_assoc($a)) {
echo $b['subcat_name'] . ' (' . $b['total'] . ')<br />';
}
[/PHP]

[moazam]

No one here to answer :(

[moazam]

here is the updated code.

[PHP]
include("connect.php");
$a = mysql_query("SELECT S.subcat_name, S.subcat_id, count(F.id) AS total FROM files F RIGHT JOIN subcategory S USING(subcat_id) WHERE S.cat_id=3 GROUP BY S.subcat_id");
if($a) {
if(mysql_num_rows($a)== 0 ) {
echo 'No Result';
}
while($b = mysql_fetch_assoc($a)) {
echo "$b[subcat_id] - ". $b['subcat_name'] . "($b[total])<br >";
}
}
else {
echo 'Error: '.mysql_error();
}
[/PHP]

Output
======
Angelina Jolie(0)
Salma Hayek(0)
Jessica Simpson(0)
Jessica Alba(0)
Berry Moore(1)

What i need is..to display this like

A S
Angelina Jolie Salma Hayek

B
Berry Moore

J
Jessica Alba
Jessica Simpson

any idea?

[code green]

You need to do a bit more with this line [PHP]while($b = mysql_fetch_assoc($a)) {
echo "$b[subcat_id] - ". $b['subcat_name'] . "($b[total])<br >";[/PHP] A simple HTML table will give you more control over the format

[dlite922]

here is the updated code.

[PHP]
include("connect.php");
$a = mysql_query("SELECT S.subcat_name, S.subcat_id, count(F.id) AS total FROM files F RIGHT JOIN subcategory S USING(subcat_id) WHERE S.cat_id=3 GROUP BY S.subcat_id");
if($a) {
if(mysql_num_rows($a)== 0 ) {
echo 'No Result';
}
while($b = mysql_fetch_assoc($a)) {
echo "$b[subcat_id] - ". $b['subcat_name'] . "($b[total])<br >";
}
}
else {
echo 'Error: '.mysql_error();
}
[/PHP]

Output
======
Angelina Jolie(0)
Salma Hayek(0)
Jessica Simpson(0)
Jessica Alba(0)
Berry Moore(1)

What i need is..to display this like

A S
Angelina Jolie Salma Hayek

B
Berry Moore

J
Jessica Alba
Jessica Simpson

any idea?

This is easy... here's what you do. First get an array of the letters A - Z.

You know your data is sorted in Ascending order, so you aschend your alphabet with it.

You compare your first index in the array with the first letter or $b['subcat_name'], if its a match, out put the Letter A , and SET A FLAG THAT A MATCH HAS BEEN FOUND.

this flag will be set to false so that you don't out put the A for each result under A.

When you reach a subcat_name that doesn't not match A, you increase your index until you get a match, say we skipped B and we matched the subcat_name on C. Then reset the flag back to True and output that Index, in our example C.

and you'll get something like this with the correct HTML:

A
Andersion
Azul

C
Cat
Corndog

G
Gearge

H
Have Fun!!

[dlite922]

let me know if you need help with the coding, but code what you can understand of my logic and i'll correct it for you.