MySQLi loop query to variables

Question

Good evening all.

I'm currently working on a small personal project. It's purpose is to retrieve numerous values from a database on my backend and store them as variables. These variables are then used to modify the appearance of some HTML5 Canvas objects (in this case, i'm using arcs).

Please note that the values in the database are Text and thus my bind statements refer to that. The queries i'm calling (AVG, MIN, MAX) work fine with the values i've got as the fields store numerical data (this is merely due to another script that deals with adding or updating the data -- that's already running MySQLi, and using Text was the best solution for my situation).

Now, i achieved what i wanted with standard MySQL queries, but it's messy code and the performance of it could prove to be terrible as the database grows. For that reason, i want to use loops. I also feel that bind_param of MySQLi would be much better for security. The page doesn't accept ANY user input, it's merely for display and so injection is less of a concern, but at some point in the future, i'll be looking to expand it to allow users to control what is displayed.

Here's a sample of my original MySQL PHP code sample;

$T0A = mysql_query('SELECT AVG(Temp0) FROM VTempStats'); // Average
$T0B = mysql_query('SELECT MIN(Temp0) FROM VTempStats'); // Bottom/MIN
$T0T = mysql_query('SELECT MAX(Temp0) FROM VTempStats'); // Top/MAX
$T1A = mysql_query('SELECT AVG(Temp1) FROM VTempStats'); // Average
$T1B = mysql_query('SELECT MIN(Temp1) FROM VTempStats'); // Bottom/MIN
$T1T = mysql_query('SELECT MAX(Temp1) FROM VTempStats'); // Top/MAX

$r_T0A = mysql_result($T0A, 0);
$r_T0T = mysql_result($T0T, 0);
$r_T0B = mysql_result($T0B, 0);
$r_T1A = mysql_result($T1A, 0);
$r_T1T = mysql_result($T1T, 0);
$r_T1B = mysql_result($T1B, 0);

if ($r_T0A == "" ) {$r_T0A = 0;}
if ($r_T1A == "" ) {$r_T1A = 0;}

if ($r_T0B == "" ) {$r_T0B = 0;}
if ($r_T1B == "" ) {$r_T1B = 0;}

if ($r_T0T == "" ) {$r_T0T = 0;}
if ($r_T1T == "" ) {$r_T1T = 0;}

That's shorter than the original, as there's 4x3 sets of queries (Temp0,Temp1,Temp2,Temp3, and min,max,avg for each). Note that the last 6 if statements are merely there to ensure that fields that are null are automatically set to 0 before my canvas script attempts to work with them (see below).

To show that value on the arc, i'd use this in my canvas script (for example);

var endAngle = startAngle + (<?= $r_T0A ?> / 36+0.02);

It worked for me, and what was displayed was exactly what i expected.

Now, in trying to clean up my code and move to a loop and MySQLi, i'm running into problems. Being very new to both SQL and PHP, i could use some assistance.

This is what i tried;

$q_avg = "SELECT AVG(Temp?) FROM VTempStats";
    for ($i_avg = 0; $i_avg <= 3; ++$i_avg)
    {
        if ($s_avg = $mysqli->prepare($q_avg))
        {
            $s_avg->bind_param('s',$i_avg);
            $s_avg->execute();
            $s_avg->bind_result($avg);
            $s_avg->fetch();
            echo $avg;
        }
    }

Note: mysqli is the MySQLi connection. I've cut the code down to only show the AVG query loop, but the MIN and MAX loops are nearly identical.

Obviously, that won't work as it's only assigning one variable for each set of queries, instead of 4 variables for each loop.

As you can imagine, what i want to do is assign all 12 values to individual variables so that i can work with them in my canvas script. I'm not entirely sure how i go about this though.

I can echo individual values out through MySQLi, or i can query the database to change or add data through MySQLi, but trying to make a loop that does what i intend with MySQLi (or even MySQL), that's something i need help with.

Answer 1

From my reading of your code, you have a fixed number of columns and know their names, and you are applying the AVG(), MIN(), MAX() aggregates to the same table over the same aggregate group, with no WHERE clause applied. Therefore, they can all be done in one query from which you just need to fetch one single row.

SELECT
  AVG(Temp0) AS a0,
  MIN(Temp0) AS min0,
  MAX(Temp0) AS max0,
  AVG(Temp1) AS a1,
  MIN(Temp1) AS min1,
  MAX(Temp1) AS max1,
  AVG(Temp2) AS a2,
  MIN(Temp2) AS min2,
  MAX(Temp2) AS max2,
  AVG(Temp3) AS a3,
  MIN(Temp3) AS min3,
  MAX(Temp3) AS max3
FROM VTempStats

This can be done in a single call to $mysqli->query(), and no parameter binding is necessary so you don't need the overhead of prepare(). One call to fetch_assoc() is needed to retrieve a single row, with columns aliased like a0, min0, max0, etc... as I have done above.

// Fetch one row
$values = $result_resource->fetch_assoc();
print_r($values);
printf("Avg 0: %s, Min 0: %s, Max 0: %s... etc....", $values['a0'], $values['min0'], $values['max0']);

These can be pulled into the global scope with extract(), but I recommend against that. Keeping them in their $values array makes their source more explicit.

Answer 2

As you can imagine, what i want to do is assign all 12 values to individual variables so that i can work with them in my canvas script. I'm not entirely sure how i go about this though.

Understood. Here is what I would do.

<?php // RAY_temp_scottprichard.php
error_reporting(E_ALL);
echo '<pre>';

// RANGE OF TEMPS
$temps = range(0,3);

// RANGE OF VALUES
$funcs = array
( 'A' => 'AVG'
, 'B' => 'MIN'
, 'T' => 'MAX'
)
;

// CONSTRUCT THE QUERY STRING
$query = 'SELECT ';
foreach ($temps as $t)
{
    foreach ($funcs as $key => $func)
    {
        $query .= PHP_EOL
        . $func
        . '(Temp'
        . $t
        . ') AS '
        . 'T'
        . $t
        . $key
        . ', '
        ;
    }
}

// DECLOP THE UNWANTED TRAILING COMMA
$query = rtrim($query, ', ');

// ADD THE TABLE NAME
$query .= ' FROM VTempStats';

// ADD ANY ORDER, LIMIT, WHERE CLAUSES HERE
$query .= ' WHERE 1=1';

// SHOW THE WORK PRODUCT
var_dump($query);

See the output query string here: http://www.laprbass.com/RAY_temp_scottpritchard.php

When you run this query, you will fetch one row with *mysql_fetch_assoc()* or equivalent, and it will have all the variables you want in that row, with named keys. Then you can use something like this to inject the variable names and values into your script. http://php.net/manual/en/function.extract.php

PHP extract() allows the use of a prefix, so you should be able to avoid having to make too many changes to your existing script.

HTH, ~Ray