Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › PHP, MySQL, and sorting. So much trouble.
New Posts  All Forums:Forum Nav:

PHP, MySQL, and sorting. So much trouble.

post #1 of 15
Thread Starter 
Long story short I have been working on this code for hours. What I am trying to do is pull the latest 36 values from a MYSQL table of over 100, then reverse the pulled values so it goes oldest to newest within the 36 first pulled. I am able to pull the latest 36 without a problem, the issue is with flipping those 36 to be oldest to newest.

Heres what I have so far...
Code:
    $get_info = mysql_query("SELECT * FROM `logs` WHERE `id-number`='0' ORDER BY `timestamp` DESC LIMIT 36;");
    while ($mysql_pull = mysql_fetch_array($get_info))  // I need it to flip here, or before here so when the while happens, it starts with the line with the LOWEST timestamp from the $get_info call.
    {  
            $net_up = $mysql_pull['net-up'];
            $net_down = $mysql_pull['net-down'];
            echo $net_up." - ".$net_down."<br/>";
    }

so ya, thats where I am. Thanks for any help, sorry if my terms are bad, I am not the best PHP/MySQL coder. Self Taught.
post #2 of 15
Quote:
Originally Posted by ddggttff3 View Post

Long story short I have been working on this code for hours. What I am trying to do is pull the latest 36 values from a MYSQL table of over 100, then reverse the pulled values so it goes oldest to newest within the 36 first pulled. I am able to pull the latest 36 without a problem, the issue is with flipping those 36 to be oldest to newest.
Heres what I have so far...
Code:
    $get_info = mysql_query("SELECT * FROM `logs` WHERE `id-number`='0' ORDER BY `timestamp` DESC LIMIT 36;");
    while ($mysql_pull = mysql_fetch_array($get_info))  // I need it to flip here, or before here so when the while happens, it starts with the line with the LOWEST timestamp from the $get_info call.
    {  
            $net_up = $mysql_pull['net-up'];
            $net_down = $mysql_pull['net-down'];
            echo $net_up." - ".$net_down."<br/>";
    }
so ya, thats where I am. Thanks for any help, sorry if my terms are bad, I am not the best PHP/MySQL coder. Self Taught.

Apologize if I missed something, I'm sitting at a long light on my phone, but you have tried ordering in the other direction, right?

ORDER BY `timestamp` ASC

If you want the oldest first, then you're ascending, not descending.
Intellect v2
(9 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7-6700K Processor ASUS ROG MAXIMUS VIII HERO LGA1151 DDR4 M.2 SAT... EVGA GTX 1080 SC ACX 3.0 Crucial Ballistix Sport 32GB DDR4 2400 MT/s (PC... 
CoolingKeyboardPowerCase
Noctua NH-D15 Das Keyboard 4 Professional (Brown) Corsair AX860 Fractal Design Define R5 
Mouse
MIONIX NAOS 7000 
  hide details  
Reply
Intellect v2
(9 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7-6700K Processor ASUS ROG MAXIMUS VIII HERO LGA1151 DDR4 M.2 SAT... EVGA GTX 1080 SC ACX 3.0 Crucial Ballistix Sport 32GB DDR4 2400 MT/s (PC... 
CoolingKeyboardPowerCase
Noctua NH-D15 Das Keyboard 4 Professional (Brown) Corsair AX860 Fractal Design Define R5 
Mouse
MIONIX NAOS 7000 
  hide details  
Reply
post #3 of 15
Thread Starter 
yes, but as I said, the table has over 100 values. So if i sort that way, I will get the 36 oldest of the 100. I need the 36 latest, then out of the 36 latest, the oldest first. Thanks tho
post #4 of 15
http://bugs.mysql.com/bug.php?id=23156

mysql sucks, no CTEs, good luck. you're probably stuck with a subselect, or joining the table to itself, or a temp table. read that link.

really there's about 8 million things wrong with building a real website using php and adhoc mysql stuff littered everywhere, but i'll spare you all that because its very late. At the very least you should be moving those queries into a stored procedure - it looks like you're probably vulnerable to sql injection ... to say the least
stable again
(25 items)
 
  
CPUCPUMotherboardGraphics
E5-2687W E5-2687W ASUS Z9PED8-WS EVGA GTX 570 (Linux host) 
GraphicsRAMHard DriveHard Drive
EVGA GTX 970 FTW (win7 guest) 64GB G.SKILL 2133 2x Crucial M4 256GB raid1 4x 3TB raid 10 
CoolingCoolingCoolingCooling
2x Apogee HD  2x RX 480 2x MCP 655 RP-452x2 rev2 (new) 
CoolingCoolingOSOS
16x Cougar Turbine CFT12SB4 (new) EK FC 580 Gentoo (host) Gentoo (x23 guests) 
OSMonitorMonitorPower
windows 7 (guest w/ vfio-pci) Viewsonic 23" 1080P Viewsonic 19" Antec HCP Platinum 1000 (new) 
CaseOtherOther
Case Labs TH10 (still the best ever) 2x Lamptron FC-5 IOGEAR 2 way DVI KVM Switch 
  hide details  
Reply
stable again
(25 items)
 
  
CPUCPUMotherboardGraphics
E5-2687W E5-2687W ASUS Z9PED8-WS EVGA GTX 570 (Linux host) 
GraphicsRAMHard DriveHard Drive
EVGA GTX 970 FTW (win7 guest) 64GB G.SKILL 2133 2x Crucial M4 256GB raid1 4x 3TB raid 10 
CoolingCoolingCoolingCooling
2x Apogee HD  2x RX 480 2x MCP 655 RP-452x2 rev2 (new) 
CoolingCoolingOSOS
16x Cougar Turbine CFT12SB4 (new) EK FC 580 Gentoo (host) Gentoo (x23 guests) 
OSMonitorMonitorPower
windows 7 (guest w/ vfio-pci) Viewsonic 23" 1080P Viewsonic 19" Antec HCP Platinum 1000 (new) 
CaseOtherOther
Case Labs TH10 (still the best ever) 2x Lamptron FC-5 IOGEAR 2 way DVI KVM Switch 
  hide details  
Reply
post #5 of 15
Quote:
Originally Posted by ddggttff3 View Post

yes, but as I said, the table has over 100 values. So if i sort that way, I will get the 36 oldest of the 100. I need the 36 latest, then out of the 36 latest, the oldest first. Thanks tho

dump it into a variable and read it backwards. Or select into a memory table and read that backwards. or nested selects and order that backwards
post #6 of 15
Quote:
Originally Posted by lloyd mcclendon View Post

http://bugs.mysql.com/bug.php?id=23156
mysql sucks, no CTEs, good luck. you're probably stuck with a subselect, or joining the table to itself, or a temp table. read that link.
really there's about 8 million things wrong with building a real website using php and adhoc mysql stuff littered everywhere, but i'll spare you all that because its very late. At the very least you should be moving those queries into a stored procedure - it looks like you're probably vulnerable to sql injection ... to say the least

He's not vulnerable to SQL injection because he's not importing an variables. There are routines to cleanse user data when dumping vars into SQL (eg mysql_real_escape_string).
Though I'm pretty sure the APIs he's using are considered "bad" anyway and have subsequently been replaced with other SQL calls (another PHP guy would have to verify this though).

Personally I prefer Perl's DBD approach where you have the SQL and variables as separate calls and the DB drivers creates the full SQL. Injection attacks are impossible against this method as the SQL isn't built up from concatenating strings.
post #7 of 15
Thread Starter 
Quote:
Originally Posted by Plan9 View Post

dump it into a variable and read it backwards. Or select into a memory table and read that backwards. or nested selects and order that backwards


OK, sorry for asking but mind pointing me in the direction of where to go to do this? ive tried array_reverse() but that does not seem to work with mysql_fetch_array()
post #8 of 15
Quote:
Originally Posted by ddggttff3 View Post

yes, but as I said, the table has over 100 values. So if i sort that way, I will get the 36 oldest of the 100. I need the 36 latest, then out of the 36 latest, the oldest first. Thanks tho
Can you do SQL blocks?

"SELECT * INTO #TEMP FROM `logs` WHERE `id-number`='0' ORDER BY `timestamp` LIMIT 36
SELECT * FROM #TEMP ORDER BY BY `timestamp` DESC;"

Another solution would read your resultset line by line into a new array. Then manipulate that array.
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #9 of 15
Quote:
Originally Posted by Plan9 View Post

He's not vulnerable to SQL injection because he's not importing an variables. There are routines to cleanse user data when dumping vars into SQL (eg mysql_real_escape_string).
Though I'm pretty sure the APIs he's using are considered "bad" anyway and have subsequently been replaced with other SQL calls (another PHP guy would have to verify this though).
Personally I prefer Perl's DBD approach where you have the SQL and variables as separate calls and the DB drivers creates the full SQL. Injection attacks are impossible against this method as the SQL isn't built up from concatenating strings.

yes that's what i was getting at, sure there are no variables subbed in into that particular example he posted, but more than likely he's got another sql statement somewhere with a parameter being lumped into it. Type ' OR 1 == 1 -- into the form field and watch what happens thumb.gif yeah ok you can do the escaping first, sure. but its still the wrong way to build something.

"DB drivers creates the full SQL "

yes this is much better, for many reasons well beyond the sql injection problem. I cannot stand anything beyond EXECUTE sitting in application code. Or ideally you'd be using something like hibernate or entity framework and allowing the application layer to generate the sql for you - not to say there aren't a lot of challenges with that, but once over them it is very nice. I couldn't imagine building anything very data rich without that.. embedded sql applications are always unmaintainable junk
Edited by lloyd mcclendon - 4/25/12 at 8:59am
stable again
(25 items)
 
  
CPUCPUMotherboardGraphics
E5-2687W E5-2687W ASUS Z9PED8-WS EVGA GTX 570 (Linux host) 
GraphicsRAMHard DriveHard Drive
EVGA GTX 970 FTW (win7 guest) 64GB G.SKILL 2133 2x Crucial M4 256GB raid1 4x 3TB raid 10 
CoolingCoolingCoolingCooling
2x Apogee HD  2x RX 480 2x MCP 655 RP-452x2 rev2 (new) 
CoolingCoolingOSOS
16x Cougar Turbine CFT12SB4 (new) EK FC 580 Gentoo (host) Gentoo (x23 guests) 
OSMonitorMonitorPower
windows 7 (guest w/ vfio-pci) Viewsonic 23" 1080P Viewsonic 19" Antec HCP Platinum 1000 (new) 
CaseOtherOther
Case Labs TH10 (still the best ever) 2x Lamptron FC-5 IOGEAR 2 way DVI KVM Switch 
  hide details  
Reply
stable again
(25 items)
 
  
CPUCPUMotherboardGraphics
E5-2687W E5-2687W ASUS Z9PED8-WS EVGA GTX 570 (Linux host) 
GraphicsRAMHard DriveHard Drive
EVGA GTX 970 FTW (win7 guest) 64GB G.SKILL 2133 2x Crucial M4 256GB raid1 4x 3TB raid 10 
CoolingCoolingCoolingCooling
2x Apogee HD  2x RX 480 2x MCP 655 RP-452x2 rev2 (new) 
CoolingCoolingOSOS
16x Cougar Turbine CFT12SB4 (new) EK FC 580 Gentoo (host) Gentoo (x23 guests) 
OSMonitorMonitorPower
windows 7 (guest w/ vfio-pci) Viewsonic 23" 1080P Viewsonic 19" Antec HCP Platinum 1000 (new) 
CaseOtherOther
Case Labs TH10 (still the best ever) 2x Lamptron FC-5 IOGEAR 2 way DVI KVM Switch 
  hide details  
Reply
post #10 of 15
Quote:
Originally Posted by lloyd mcclendon View Post

yes that's what i was getting at, sure there are no variables subbed in into that particular example he posted, but more than likely he's got another sql statement somewhere with a parameter being lumped into it. Type ' OR 1 == 1 -- into the form field and watch what happens thumb.gif yeah ok you can do the escaping first, sure. but its still the wrong way to build something.
"DB drivers creates the full SQL "
yes this is much better, for many reasons well beyond the sql injection problem. I cannot stand anything beyond EXECUTE sitting in application code. Or ideally you'd be using something like hibernate or entity framework and allowing the application layer to generate the sql for you - not to say there aren't a lot of challenges with that, but once over them it is very nice. I couldn't imagine building anything very data rich without that.. embedded sql applications are always unmaintainable junk

If I'm using embedded SQL then I always leave my SQL in separate files and have a routine that calls them into a $var. I know it's a little slower than having the SQL inside the code itself (or stored procedures), but it's tidier.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Web Coding
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › PHP, MySQL, and sorting. So much trouble.