Routine results in PHP

Question

Is it possible to run this code 24 times in order to find per hour value without copy and pasting the code 24 times.

<?php
  $result = mysql_query('SELECT date,SUM(value) AS value_sum FROM me Where WEEKDAY(date) >="6" AND HOUR(date)="0" '); 
  $row = mysql_fetch_assoc($result); 
  $h0= $row['value_sum'];
?>

<?php
  $result = mysql_query('SELECT date,SUM(value) AS value_sum FROM me Where WEEKDAY(date) >="6" AND HOUR(date)="1" '); 
  $row = mysql_fetch_assoc($result); 
  $h1= $row['value_sum'];
?>

I need to write this code 24 times to get the sum of the values per hour ($h0,$h1,,$h2...$h23). Is it any easier way to do it ? — Ian, Jan 22 '16 at 13:45
No :( , is what i am asking for. I didnt succed to use loops. — Ian, Jan 22 '16 at 13:47
Stop using the **deprecated and as of PHP7 removed** mysql_* functions. Migrate to PDO and start using Prepared Statements, it really isn't hard. — Charlotte Dunois, Jan 22 '16 at 14:06
So please have a look at my answer below - and maybe get some knowledge out of it. — hherger, Jan 23 '16 at 16:25

hherger · Accepted Answer · 2016-01-22T20:43:19.387

I'd suggest you modify your SQL statement in order to get all tuples in one access, and then loop over the tuples (in memory) to sum up the value items:

// Query the database
$result = mysql_query('SELECT date, value FROM me Where WEEKDAY(date) >="6" AND HOUR(date) >= "0" AND HOUR(date) < "24"');

// Sum values up
$h1 = 0;
while($row = mysql_fetch_assoc($result)) {
    $h1 += $row['value'];
}

Executing an SQL statement - for several reasons - uses considerably more resources, and takes longer, than executing PHP code in a loop over all elements of an array.

This solution would execute one SQL query only instead of 24 queries.

(And, yes: use mysqli or PDO instead of mysql, which is deprecated)

score -1 · Answer 2 · answered Jan 22 '16 at 13:50

Try using a for loop and add your results into an array.

<?php
  $resultArray = array();
  for($i = 0; $i < 24; $i++;) {
     $result = mysql_query('SELECT date,SUM(value) AS value_sum FROM me Where WEEKDAY(date) >="6" AND HOUR(date)="' . $i . '" '); 
     $row = mysql_fetch_assoc($result); 
     resultArray[] = $row['value_sum'];
  }
?>

Routine results in PHP

2 Answers2