Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Oct 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Order of loading in query

    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.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

    Code:
    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:

    Code:
    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.

  • #3
    New Coder
    Join Date
    Oct 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Order of loading

    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.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #5
    New Coder
    Join Date
    Oct 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile I still don't get it

    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.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •