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

MySQL/PHP ORDER BY

Joined
Aug 17, 2008
Messages
2,184 (0.63/day)
Likes
1,027
Location
Minnesota, USA
System Name TIGRAlpha4
Processor i5 750 @ 3.6GHz
Motherboard ASRock P55 Deluxe
Cooling S1283 + FFB1212VHE
Memory 16GB DDR3 1800, 7-8-7-24
Video Card(s) 560 Ti
Storage 128GB Kingston SSD + 16TB RAID 6
Display(s) Dell P1130 + Samsung 275T
Case Antec Twelve Hundred
Power Supply Silverstone OP1000-E
#1
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:
 
Joined
Jun 24, 2009
Messages
230 (0.07/day)
Likes
51
Location
Chicago
System Name Achilleus
Processor Intel Core i7 4770k
Motherboard GIGABYTE Z87N-WIFI
Memory Patriot Intel Extreme Masters Viper 3 2133MHz (2x4GB)
Video Card(s) EVGA GTX 970
Storage 2x Intel X25-M 160GB, 2x WD Caviar Black 1TB in RAID 1
Display(s) LG 29UB65-P
Case Bitfenix Prodigy
Power Supply PCP&C Silencer MK III 600W
Software Win7 Professional x64
#2
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
 
Joined
Aug 17, 2008
Messages
2,184 (0.63/day)
Likes
1,027
Location
Minnesota, USA
System Name TIGRAlpha4
Processor i5 750 @ 3.6GHz
Motherboard ASRock P55 Deluxe
Cooling S1283 + FFB1212VHE
Memory 16GB DDR3 1800, 7-8-7-24
Video Card(s) 560 Ti
Storage 128GB Kingston SSD + 16TB RAID 6
Display(s) Dell P1130 + Samsung 275T
Case Antec Twelve Hundred
Power Supply Silverstone OP1000-E
#3
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.
 
Joined
Jun 24, 2009
Messages
230 (0.07/day)
Likes
51
Location
Chicago
System Name Achilleus
Processor Intel Core i7 4770k
Motherboard GIGABYTE Z87N-WIFI
Memory Patriot Intel Extreme Masters Viper 3 2133MHz (2x4GB)
Video Card(s) EVGA GTX 970
Storage 2x Intel X25-M 160GB, 2x WD Caviar Black 1TB in RAID 1
Display(s) LG 29UB65-P
Case Bitfenix Prodigy
Power Supply PCP&C Silencer MK III 600W
Software Win7 Professional x64
#4
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"
 
Joined
Aug 17, 2008
Messages
2,184 (0.63/day)
Likes
1,027
Location
Minnesota, USA
System Name TIGRAlpha4
Processor i5 750 @ 3.6GHz
Motherboard ASRock P55 Deluxe
Cooling S1283 + FFB1212VHE
Memory 16GB DDR3 1800, 7-8-7-24
Video Card(s) 560 Ti
Storage 128GB Kingston SSD + 16TB RAID 6
Display(s) Dell P1130 + Samsung 275T
Case Antec Twelve Hundred
Power Supply Silverstone OP1000-E
#5
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"
:banghead: I'm an idiot. Haha thank again. Will let you know if the first option works.
 

FordGT90Concept

"I go fast!1!11!1!"
Joined
Oct 13, 2008
Messages
21,068 (6.22/day)
Likes
10,191
Location
IA, USA
System Name BY-2015
Processor Intel Core i7-6700K (4 x 4.00 GHz) w/ HT and Turbo on
Motherboard MSI Z170A GAMING M7
Cooling Scythe Kotetsu
Memory 2 x Kingston HyperX DDR4-2133 8 GiB
Video Card(s) PowerColor PCS+ 390 8 GiB DVI + HDMI
Storage Crucial MX300 275 GB, Seagate 6 TB 7200 RPM
Display(s) Samsung SyncMaster T240 24" LCD (1920x1200 HDMI) + Samsung SyncMaster 906BW 19" LCD (1440x900 DVI)
Case Coolermaster HAF 932 w/ USB 3.0 5.25" bay
Audio Device(s) Realtek Onboard, Micca OriGen+
Power Supply Enermax Platimax 850w
Mouse SteelSeries Sensei RAW
Keyboard Tesoro Excalibur
Software Windows 10 Pro 64-bit
Benchmark Scores Faster than the tortoise; slower than the hare.
#6
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.
 
Joined
Aug 17, 2008
Messages
2,184 (0.63/day)
Likes
1,027
Location
Minnesota, USA
System Name TIGRAlpha4
Processor i5 750 @ 3.6GHz
Motherboard ASRock P55 Deluxe
Cooling S1283 + FFB1212VHE
Memory 16GB DDR3 1800, 7-8-7-24
Video Card(s) 560 Ti
Storage 128GB Kingston SSD + 16TB RAID 6
Display(s) Dell P1130 + Samsung 275T
Case Antec Twelve Hundred
Power Supply Silverstone OP1000-E
#7
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":

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: