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,

Code: Select all

$sql = 'SELECT *
  FROM links';
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