**SOLVED** Problem Sorting MySQL data

[Antonella]

ok, I've tried and tried to figure this out, but I'm not able to.

I'm pretty sure it's easy, but, as I'm not a php expert I really don't understand how to do this.

I have a table recording categories and one recording articles, their relation is the category id (id_cat)

my query is
[code]$articoli=mysql_query("SELECT * FROM sayit_news n LEFT JOIN sayit_categorie c ON n.id_cat = c.id_cat GROUP BY n.id_cat ORDER BY n.titolo")
    or die(mysql_error());[/code]
now the problem is this:
how do I display the results like this:

Category 1:
- article1
- article2
- article3
...

Category 2:
- art1
- ...

and so on?

Thanks for any help

[Barand]

Why the LEFT JOIN?

- Do you have items not in any category?

- If you want to show categories even if they have no items then it should be "categories LEFT JOIN news"

You don't want GROUP BY, that's for aggregating totals, averages etc. Just put in category order.
[code]
[code]SELECT * FROM sayit_news n LEFT JOIN sayit_categorie c ON n.id_cat = c.id_cat  ORDER BY n.id_cat, n.titolo[/code]


(pseudocode)
[code]
last_cat = '';

while (fetch_row)

           if (last_cat not equal to category)

                 output category
                 set last_cat = category

           end if

           output details

end while[/code]

[Antonella]

Well, I used left join because I want to get the name of the categories.
No, I prefer not to show categories with no items.

Now I'm trying to write the code following your directions, as I have a problem with the set command (it's the first time I "meet" it :) )



[code]
$last_cat = '';

while ($row=mysql_fetch_assoc($query)) {

           if ($last_cat != $row['id_cat']) {

                 echo $row['nome_c']; #output category name
                 set $last_cat = $row['id_cat']; #set category : is this syntax correct?

          } #end if

           echo $row['titolo']; # output title of the article

} #end while[/code]

Is this what I have to do?

Thanks a lot for your help and patience!

[Barand]

[!--quoteo(post=367642:date=Apr 23 2006, 10:07 AM:name=Antonella)--][div class=\'quotetop\']QUOTE(Antonella @ Apr 23 2006, 10:07 AM) [snapback]367642[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well, I used left join because I want to get the name of the categories.
No, I prefer not to show categories with no items.
[/quote]

Use INNER JOIN in that case.

LEFT JOINS are used when a matching row may not exist in the right table but you still want the row contents from the left table.

There is no "set" command. I was just describing the logic of how to process (pseudocode), not coding it.


Just use
[code]$last_cat = $row['id_cat'];[/code]

[Antonella]

Wow!

As usual thanks Barand, your help is always precious :)

I've tested the code and it works like magic.

Btw, I understoon what that "set" meant: not a command hey? just meant I had to declare $last_cat= etc...!!!

I'm extra slow, but I understand things...sometimes :D

Thanks again!

I'm changing LEFT JOIN with INNER JOIN now :)