Page 1 of 1

[SOLVED]How to sort data

Posted: Tue Nov 02, 2004 1:30 pm
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

Posted: Tue Nov 02, 2004 2:26 pm
by Dale
You have to create a SQL varible but then have another one inside that... if you get what i mean...

Posted: Tue Nov 02, 2004 2:32 pm
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.

Thanks

Posted: Tue Nov 02, 2004 2:38 pm
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

Re: Thanks

Posted: Tue Nov 02, 2004 2:53 pm
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 ;)

Posted: Tue Nov 02, 2004 3:37 pm
by patrikG

Posted: Tue Nov 02, 2004 3:39 pm
by John Cartwright
Wouldn't you need to have a JOIN if you are going to use GROUP By

Posted: Tue Nov 02, 2004 3:42 pm
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"

Posted: Tue Nov 02, 2004 3:43 pm
by John Cartwright
/hijack

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


/hijack off

Posted: Tue Nov 02, 2004 3:47 pm
by patrikG
Thanks :)

Where's the round? ;)

/glares thirstily into phenom's direction...

Posted: Tue Nov 02, 2004 3:57 pm
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!!!

Posted: Tue Nov 02, 2004 4:33 pm
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>';
}