Order of loading in query

[sticks]

First off - I am a novice so please excuse any dumb questions. We have a site which displays travel products from a mysql database through php. Each page that queries shows the products in order of id (product no). Clients have now requested that they load a product for each region that they wish to remain at the top of the list no matter what other products are loaded or have been loaded before.

In other words they want one certain product to display before all others even though this product will have an id number later than those loaded first.

Any help would be greatly appreciated.

Cheers, Sheryll.

[guelphdad]

Okay and do you know what those product ids are? how do you determine which those rows are?

The good news is once you can explain that it is pretty easy to do.

Lets say it is by productid and you want these rows listed first and in exactly this order:

45
37
18
101

and then the rest of the products below that in regular product id order.

ORDER BY
CASE WHEN productid IN (45,37,18,101) then 0 else 1 end,
field(productid,45,37,18,101),
productid

To explain that the order by says check for productid in this specific list, if those numbers exist they are to be ordered at the top (assigned the 0), otherwise assign them in any order (assigned the 1),

next the field(productid,45,37,18,101) says, hey if the product ids are this, then I want them in exactly this order

and finally the productid says to order all other values by their productid

so if you have a list of

18
22
37
38
45
96
101

then they will be sorted by the above as follows:
45,37,18,101,22,38,96

NOTE that the order by FIELD is mysql specific. if you want to stick to the sql standard then do this:

ORDER BY
CASE WHEN 
     productid = 45 then 0 
     when productid =37 then 1
     when productid=18 then 2
     when productid=101 then 3 
     else 4 end,
productid

that will do the exact same as described above except without the proprietary code.

[sticks]

Thank you very much for your reply. The code in our php page reads as:

PHP Code:

<?
  $row1 = mysql_query('select id, title, imgname, info_short from products where active = 1 and region="'. $_GET['region'] . '"');
?> <?
     while($result = mysql_fetch_array($row1)) {
?> <tr onMouseOver="this.style.cursor='hand'" onClick="document.location.href='viewproduct.php?id=<? echo $result['id']; ?>'" BGCOLOR="#336699" VALIGN="MIDDLE"> 
<td CLASS="text"> <p><font color=#ffcc00><b> <br> <br> <? echo($result['title']); ?> 
</b></font><br /><br /> <img style="float:left;" src="<? echo($result['imgname']); ?>" HSPACE="10"/> 
<?echo(nl2br($result['info_short']));?> </p><p align="left"><br> </p><div align="center"> 
<p>&nbsp;</p></div></td></tr> <tr><td><div align="center"><img src="/images/yellowbar.gif" width="600" height="1"></div></td></tr> 
<? } ?>

To me (as a novice) this says that the products are displayed in order of id. It will be a certain id number that we wish to display at the top of each page.

Again, thank you, Sheryll.

[guelphdad]

Do yourself one other favour, in the php manual look into using mysql_real_escape_string as it is now you are leaving your database open to attack by passing the data input from the user directly into your database.

[sticks]

Hi all and thank you for your reply.

guelphdad: Our code is as follows:

PHP Code:

 <?
  $row1 = mysql_query('select id, title, imgname, info_short from products ORDER BY id ASC where active = 1 and region="'. $_GET['region'] . '"' );
?> <?
     while($result = mysql_fetch_array($row1)) {
?> <tr onMouseOver="this.style.cursor='hand'" onClick="document.location.href='viewproduct.php?id=<? echo $result['id']; ?>'"

I am not sure where to add the order by addition to the code. Have tried many times but to no avail.

Any help appreciated.

[guelphdad]

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

are the order of the clauses you can use. The order by obviously has to go at the end in your case after your WHERE clause.