Jump to content

**SOLVED** Problem Sorting MySQL data


Antonella

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
Link to comment
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]
Link to comment
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!
Link to comment
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]
Link to comment
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 :)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.