[SOLVED]How to sort data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

[SOLVED]How to sort data

Post by neophyte »

I have two tables like so:

Code: Select all

articles
------------------
id  name    category
1   article1    1
2   article2    2
3   article3    2
4   article4    3
5   article5    3
6   article6    4

category
------------------
id  name   
1   category1    
2   category2    
3   category3    
4   category4    
5   category5    
6   category6
I want to print these to the page like:

category1
article1

category2
article2
article3

category3
article4
article5

category4
article6

The problem I'm having is not ouputting the table. I can do that. It's getting the label "category x" to appear once before the grouping. Does anybody know how to do this? Is the answer in the sql? or is it in a some sort of code around in PHP?

Thanks
Last edited by neophyte on Tue Nov 02, 2004 4:06 pm, edited 1 time in total.
Dale
Forum Contributor
Posts: 466
Joined: Fri Jun 21, 2002 5:57 pm
Location: Atherstone, Warks

Post by Dale »

You have to create a SQL varible but then have another one inside that... if you get what i mean...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

you can do something like this

Code: Select all

<?

$sql = "SELECT `categories` ORDER BY `id`";
$result = mysql_query($sql) or die(mysql_error());

while ($row=mysql_fetch_array($result))
{

$sql = "SELECT `articles` WHERE `id` = '".$row['id']."' ORDER BY `id`";

//output articles

}

?>


Not sure if this is what your talking about, or looking for an all sql method of this. If thats what you want, then I'm afraid I can't help you.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Thanks

Post by neophyte »

Dale wrote:You have to create a SQL varible but then have another one inside that... if you get what i mean...


You mean you have to write a sub query?

I'll try your idea Phenom....

Let you know if it works
Dale
Forum Contributor
Posts: 466
Joined: Fri Jun 21, 2002 5:57 pm
Location: Atherstone, Warks

Re: Thanks

Post by Dale »

neophyte wrote:You mean you have to write a sub query?

I'll try your idea Phenom....

Let you know if it works
Phenom told you my idea ;)
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Wouldn't you need to have a JOIN if you are going to use GROUP By
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

yes, but not an explicit JOIN, a simple one such as "SELECT table1.field1, table2.field1 ... WHERE table1.field_id1=table2.field_id2 GROUP BY table1.id3"
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

/hijack

congrats on 2k posts :)
beers on me!!!


/hijack off
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Thanks :)

Where's the round? ;)

/glares thirstily into phenom's direction...
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

Well Phenom's way solved the immeadiate problem for me!

But I would like to try out this Group by thing patrikG suggested. I've tried Group By in the past but I always ended up with just one set (one category selection). I'll have to try it again and see if I get different results.

THANKS!!!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

$query = "SELECT articles.name AS aname, category.name AS cname FROM articles INNER JOIN category ON articles.category=category.id ORDER BY category.id ASC, articles.id ASC";
$result = mysql_query($query) or die(mysql_error());
$category = '';
while ($row = mysql_fetch_assoc($result)) {
  if ($category != $row['cname']) {
    echo '<b>' . $row['cname'] . '</b></br>';
    $category = $row['cname'];
  }
  echo $row['aname'] . '</br>';
}
Post Reply