# MySQL/PHP ORDER BY



## TIGR (Jun 8, 2010)

*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.


----------



## parelem (Jun 8, 2010)

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 (Jun 8, 2010)

parelem said:


> you can create a calculated field in the mysql query itself, so you can use the casns as any other output field.
> 
> ie
> 
> ...



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.


----------



## parelem (Jun 8, 2010)

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 (Jun 8, 2010)

parelem said:


> 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'm an idiot. Haha thank again. Will let you know if the first option works.


----------



## FordGT90Concept (Jun 8, 2010)

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.


----------



## TIGR (Jun 9, 2010)

FordGT90Concept said:


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



Thanks for the reminder Ford, all input is validated!

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


```
$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).


----------

