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>';
}