select all order by and list certain one?

juschillinnow

If I have a bunch of categories like so:

Category ID | Category
----------------|-------------
1                     Apples
2                     Pears
3                     Firemen
4                     New York
5                     Peaches
6                     Seeds
etc...

and I want to do a query like so:

$query = "select * from category order by Category";

It will obviously list them in alphabetical order like:

Apples
Firemen
New York
Peaches
Pears
Seeds

Within the query is there a way to pull out a certain on and list is first like so:

Seeds
Apples
Firemen
New York
Peaches
Pears

So Seeds is listed First with a simple query something like:

$query = "select * from category order by Category, Seeds First";

I know this isn't right, but is there a way to do something like that?

the_Igel

What for? Do you plan to put these categories into dropdown list, and you need Seeds to be selected? Then you better use 'selected' html attribute than to bother with complicated sql.
Am I right?

juschillinnow

By the way these are not the actual categories.

I have a web site that have about fifty categories and one of these categories is New Items.

If I do a query in alpha order it will list this category towards the bottom.

But I would like my customers to be able to see this category first, hence wanting to do a query that will list it in this way.

the_Igel

If it's always the same category, there's no need to take it from db, you can hard-code it.

Anyway, you can make user-defined sorting in this way:

select category, IF(STRCMP(category,'NewItem'),category, '') as mysort
from somewhere
order by mysort

Maybe there are some easier ways, but I can't say at the moment.

juschillinnow

Here's what I have:

$query = "select productcategoryName, IF(STRCMP(productcategoryName,'New Items'),productcategoryName, '') as
mysort from storeProductCategory order by mysort";
order by productcategoryName";

I believe this is what you meant, but when I do this is puts a blank where the New Items word is suppose to be.

Here's why I can't hard code it, because it changes all the time:

$mysql_result = mysql_query($query, $mysql_link);
while($row = mysql_fetch_row($mysql_result))
{
   printf("<a href=\"store.php?categoryID=%s&categoryName=%s&current=1\">%s</a><br>\n", $row[0], $row[1], $row[1]);
}

Here's the address of the site where I need this to work so you can get a better idea..... https://www2.serve.com/ttt/php/store.php

juschillinnow

Anybody? Anybody?

Weedpacket

Originally posted by juschillinnow
Here's what I have:

$query = "select productcategoryName, IF(STRCMP(productcategoryName,'New Items'),productcategoryName, '') as
mysort from storeProductCategory order by mysort";
order by productcategoryName"; 
....
   printf("<a href=\"store.php?categoryID=%s&categoryName=%s&current=1\">%s</a><br>\n", $row[0], $row[1], $row[1]);
}

Just because you're using the synthetic mysort column to sort the entries, you're not beholden to display it.

$row[1] is the mysort column; $row[0] is the productcategoryName (this would be more obvious if you referred to the fields by name instead of array index). To ask a rhetorical question: which one do you want to display?