1. Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

MySQL/PHP ORDER BY

Discussion in 'Programming & Webmastering' started by TIGR, Jun 8, 2010.

  1. TIGR

    Joined:
    Aug 17, 2008
    Messages:
    2,183 (0.99/day)
    Thanks Received:
    1,029
    Location:
    Minnesota, USA
    WARNING Dumb questions and poor coding probably follow because I have no formal education in PHP. I know only what I've needed to learn (usually through reverse engineering of others' code). I'm a hardware guy, not a coder/programmer!

    I have a large MySQL database of computer component specs that I maintain and refer to for my professional builds, with a table dedicated to each type of component (CPU, HDD, memory, etc.). Let's simplify this by focusing on my DDR3 memory table (comp_mem_ddr3). Then I can apply the resolution to the others.

    Previously, the comp_mem_ddr3 table had a field (cl) for CAS latency expressed in clocks and another field (casns) for CAS latency expressed in nanoseconds. Because I'm freakishly obsessed with streamlining, I eliminated the casns field and have switched to calculating it from variables storing the contents of two other fields: the DDR3 speed ($mem_ddr3_chiprate) and clock latency ($mem_ddr3_cl):

    $mem_ddr3_casns = round(1 / $mem_ddr3_chiprate * $mem_ddr3_cl * 2000, 1);

    This is all well and good. However, now I wish to add the ability to ORDER BY any column retrieved from the MySQL query (sorting by hyperlink, ascending or descending on click in the browser). When I had a MySQL database field dedicated to casns, I could have simply used "SELECT * FROM comp_mem_ddr3 ORDER BY $orderby" for the MySQL query and then set the $orderby variable via hyperlink. Now that there is no casns field as it is calculated later, what would be the best way to get the sorting I desire?

    I have a feeling this will involve arrays....

    Assistance would be much appreciated; will be happy to "thank" anyone who contributes. :toast:
  2. parelem

    parelem

    Joined:
    Jun 24, 2009
    Messages:
    228 (0.12/day)
    Thanks Received:
    50
    Location:
    Chicago
    you can create a calculated field in the mysql query itself, so you can use the casns as any other output field.

    ie

    "SELECT ROUND(1/chiprate*cl*2000, 1) as CASNS, cl, chiprate FROM comp_mem_ddr3 ORDER BY $orderby"

    that will output the calculated casns, cl, and chiprate and order by whatever the $orderby value is
    TIGR says thanks.
  3. TIGR

    Joined:
    Aug 17, 2008
    Messages:
    2,183 (0.99/day)
    Thanks Received:
    1,029
    Location:
    Minnesota, USA
    Thank you!

    Edit: how can I do this while still pulling other columns from the MySQL query? I have other columns for memory brand, model, product link, etc. and want to be able to sort by any of them in addition to the casns, cl, and chiprate ones.
  4. parelem

    parelem

    Joined:
    Jun 24, 2009
    Messages:
    228 (0.12/day)
    Thanks Received:
    50
    Location:
    Chicago
    i'm going to guess on this, but you might be able to do

    "select *, ROUND(1/chiprate*cl*2000, 1) as CASNS FROM comp_mem_ddr3 ORDER BY $orderby"

    i've never tried like that but it may work, otherwise you would just list the other fields separated by a comma

    "SELECT ROUND(1/chiprate*cl*2000, 1) as CASNS, cl, chiprate, brand, model, link FROM comp_mem_ddr3 ORDER BY $orderby"
    TIGR says thanks.
  5. TIGR

    Joined:
    Aug 17, 2008
    Messages:
    2,183 (0.99/day)
    Thanks Received:
    1,029
    Location:
    Minnesota, USA
    :banghead: I'm an idiot. Haha thank again. Will let you know if the first option works.
  6. FordGT90Concept

    FordGT90Concept "I go fast!1!11!1!"

    Joined:
    Oct 13, 2008
    Messages:
    13,445 (6.27/day)
    Thanks Received:
    3,415
    Location:
    IA, USA
    Warning: it is not advised to plug hyperlink text directly into a query. Make sure to validate the input first so you don't get any SQL injections.
    Crunching for Team TPU
  7. TIGR

    Joined:
    Aug 17, 2008
    Messages:
    2,183 (0.99/day)
    Thanks Received:
    1,029
    Location:
    Minnesota, USA
    Thanks for the reminder Ford, all input is validated!

    The following is working perfectly with "hyperlink-powered sorting":

    Code:
    $q_mem_ddr3="select *, ROUND(1/chiprate*cl*2000, 1) as casns FROM comp_mem_ddr3 ORDER BY $col $dir";
    Thank you for that paralem!

    New Question (Resolved)
    How do price comparison web sites aggregate/retrieve product info and prices from retailers?

    For anyone else interested, here is a start to answering that question. Beyond that, check out Commission Junction, which is one service that facilitates precisely this (with Newegg being among its affiliates).
    Last edited: Jun 9, 2010

Currently Active Users Viewing This Thread: 1 (0 members and 1 guest)

Share This Page