Jump to content

Archived

This topic is now archived and is closed to further replies.

Antonella

**SOLVED** Problem Sorting MySQL data

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.