Jump to content


Photo

**SOLVED** Problem Sorting MySQL data


  • Please log in to reply
4 replies to this topic

#1 Antonella

Antonella
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationItaly

Posted 22 April 2006 - 07:06 PM

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
$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());
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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 22 April 2006 - 08:11 PM

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]SELECT * FROM sayit_news n LEFT JOIN sayit_categorie c ON n.id_cat = c.id_cat  ORDER BY n.id_cat, n.titolo


(pseudocode)
last_cat = '';

while (fetch_row)

           if (last_cat not equal to category)

                 output category
                 set last_cat = category

           end if

           output details

end while

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Antonella

Antonella
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationItaly

Posted 23 April 2006 - 09:07 AM

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 :) )



$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

Is this what I have to do?

Thanks a lot for your help and patience!

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 23 April 2006 - 09:15 AM

[!--quoteo(post=367642:date=Apr 23 2006, 10:07 AM:name=Antonella)--][div class=\'quotetop\']QUOTE(Antonella @ Apr 23 2006, 10:07 AM) View Post[/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
$last_cat = $row['id_cat'];

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 Antonella

Antonella
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationItaly

Posted 23 April 2006 - 09:23 AM

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 :)






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users