Page 1 of 1
[SOLVED] DB Ordering Question
Posted: Mon May 02, 2005 10:07 am
by Sphen001
Hi,
I'm trying to find a way to order the information I get from the DB. The items are links, and the DB structure is:
Code: Select all
link_name varchar(255)
link_url varchar(255)
link_desc varchar(255)
link_cat varchar(255)
Now the name, url and desc are unique to each link. But the cat is the category that they are in. There are about 5 or 6 different ones. What I want to do is to use a query to get all of the links, ie.
Code: Select all
$sql = 'SELECT *
FROM links';
$result = mysql_query($sql);
This works fine. What I want to do after this is get say, all the ones in the category 'PHP', and output them, via a loop, and then do this for all of the other categories. Is this possible, or do I have to use several different queries, ie.
Code: Select all
$sql = 'SELECT *
FROM links
WHERE link_cat=PHP';
Thanks for any help you can give me.
Sphen001
Posted: Mon May 02, 2005 11:20 am
by timvw
Code: Select all
select *
from links
order by
case asc
when link_cat = 'PHP' then 0
else 1
end
and then it's only a matter of looping through the resultset...
Posted: Mon May 02, 2005 12:44 pm
by Sphen001
Ok, thanks for your reply. I'm not sure that this is what I need though. I'm trying to get every row in the table via one call,
Then I can output the category name, and then loop through all the returned rows that belong in that category.
I looked through your code and I don't think it does what I need.
Thank you anyway
Sphen001
Posted: Mon May 02, 2005 4:40 pm
by timvw
i assumed you would have no problems with the basic loop...
Code: Select all
...
$query = "SELECT * FROM links ORDER BY link_cat";
$result = mysql_query($query);
$current = '';
while ($row = mysql_fetch_assoc($result))
{
if ($current != $row['link_cat'])
{
// new category
echo "<p>{$row['link_cat']}</p>";
}
// show link
echo "<a href='{$row['link_url']}'>{$row['link_name']}</a>";
$current = $row['link_cat'];
}
...
Posted: Mon May 02, 2005 5:18 pm
by Calimero
Try this if you wish to choose only certain categories from all of the existing:
Code: Select all
// Insert the values in order you want them to appear on the screen
$categories = array("php", "javascript", "asp", "c/c++/c#" "etc...");
mysql_connect("your_server","your_db","your_pass");
mysql_select_db("your_db_name");
for ( $i = 0; $i < count($categories); $i++ )
{
$qry = mysql_query ("SELECT * FROM links l WHERE l.category = ".$categories[$i].";");
while ( $results = mysql_fetch_array($qry) )
{
// in here create variables from table column names
// echo them as you wish to format them
}
}
// Sorry if some typing error occured - it is now 00:16, and I got up veryyyy early
If however you need all the records from the DB, just classified by the column category do this:
Code: Select all
mysql_connect("your_server","your_db","your_pass");
mysql_select_db("your_db_name");
$qry = mysql_query ("SELECT * FROM links l ORDER BY l.categories;");
while ( $results = mysql_fetch_array($qry) )
{
// in here create variables from table column names
// echo them as you wish to format them
}
// NOTE that this way you can sort ( like grouping ) only in ascending or descending ( alfanumerical ) order - you canot just say I want first this, then this, and in the end this category to be listed - for use the first piece of code.
Re: ...
Posted: Mon May 02, 2005 5:23 pm
by timvw
Calimero wrote:Try this if you wish to choose only certain categories from all of the existing:
Code: Select all
// Insert the values in order you want them to appear on the screen
$categories = array("php", "javascript", "asp", "c/c++/c#" "etc...");
mysql_connect("your_server","your_db","your_pass");
mysql_select_db("your_db_name");
for ( $i = 0; $i < count($categories); $i++ )
{
$qry = mysql_query ("SELECT * FROM links l WHERE l.category = ".$categories[$i].";");
while ( $results = mysql_fetch_array($qry) )
{
// in here create variables from table column names
// echo them as you wish to format them
}
}
// Sorry if some typing error occured - it is now 00:16, and I got up veryyyy early
That explodes to a lot of queries... While you could simple do it with 1 query
Code: Select all
SELECT *
FROM foo
WHERE link='php' or link='javascript'
Calimero wrote:
If however you need all the records from the DB, just classified by the column category do this:
Code: Select all
// NOTE that this way can sort ( like grouping ) only in ascending or descending ( alfanumerical ) order - you canot just say I want first this, then this, and in the end this category to be listed - for use the first piece of code.
I'm sorry, but the first query demonstrated that it's possible to define your own order...
here's another example
Code: Select all
select *
from fruits
where color = 'red' or color = 'blue' or color = 'orange'
order by case
when color = 'red' then 0
when color = 'blue' then 1
when color = 'orange' then 2
end
Posted: Mon May 02, 2005 5:35 pm
by Sphen001
Thank you both.
I think I now have enough to get this to work.
Just FYI, I first tried this, but it didn't work
Code: Select all
while ($row = mysql_fetch_assoc($result) && $row['link_cat'] == 'PHP')
{
blah
}
Because it didn't work, I didn't know the first way shown was proper.
Again, thank you both.
Sphen001
...
Posted: Tue May 03, 2005 10:51 am
by Calimero
Ok, errors noted - sorry ( my brain overlooked that with FOR loop ),
and for the second... never mind.
Just tried to help - didn't want to defy your number of posts "timvw"
Hope someone found that helpful.
Posted: Tue May 03, 2005 12:14 pm
by Sphen001
Thanks to both of you. I managed to get a modified version of the posted code working well.
Thanks
Sphen001