MySQL/PHP ORDER BY

[TIGR]

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]

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]

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

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]

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]

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"

I'm an idiot. Haha thank again. Will let you know if the first option works.

 

[FordGT90Concept]

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]

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