PHP/MYSQL using table data to loop through multiple other tables

[hokletrain]

Hey guys, i have been working on a script for a little while now and just can't seem to find a solution.The issue I'm having is that i can't seem to loop through multiple tables using a primary tables information. In the bottom case i have two records with the same title 'fire_index'  which has the same formula, the only thing that differs is the sensors and unit_id's have changed.  

 

Breakdown of steps:

 

1.) Automatically loop through calculation table retrieve all records with desired title e.g 2 records , fire_index

2.) Use the information retrieved from the calculation table to build query's for getting data

3.) Loop through data queries to get results , apply results to formula (string replace x,y,z) and output to output table

 

Firstly i have looped through the calculation table which contains pre-made formulas, types , company names, unit id's, sensors. The user chooses the sensor and the unit they want to apply the formula to. The script is based on the formula type e.g fire index. 

 $query = mysqli_query($mysqli, "SELECT * FROM calculation where title = 'fire_index'");
    while($row = mysqli_fetch_array($query))
    {
      $title = $row['title']; // fire_index
      $time = $row['time']; // 2017-12-3
      $formula = $row['formula']; // e.g 10^(0.009254 + (0.01201*x) + (0.2789*(SQRT(y)) - 0.09577*(SQRT(z))))
      $unit= $row['unit_id']; //23223
      $sensor = $row['sensor'];  // AT - Air Temp
      $unit1 = $row['unit1_id'];  //123424
      $sensor1 = $row['sensor1']; //RH - Relative Humidity
      $unit2 = $row['unit2_id']; //123525
      $sensor2 = $row['sensor2']; //AVGWN - Avg Winspeed
      $unit3 = $row['unit3_id']; // Etc..
      $sensor3 = $row['sensor3']; //Etc... 
      $unit4 = $row['unit4_id']; //Etc.. 
      $sensor4 = $row['sensor4']; //Etc..
}
        

The next query's rely on the calculation table to get the correct columns :

  $query1 = mysqli_query($mysqli,"SELECT $sensor as sensor from data where unit_id = '$unit'");
        while($row = mysqli_fetch_array($query1))
    {
    $x = $row['sensor']; /// GETS data from AT column 
        
           
   }
    
  $query2 = mysqli_query($mysqli,"SELECT $sensor1 as sensor1 from test where unit_id = '$unit1'");
        while($row = mysqli_fetch_array($query2))
    {
    $y = $row['sensor1'];  /// GETS data from RH column 
            
   }
$query3 = mysqli_query($mysqli,"SELECT $sensor2 as sensor2 from test where unit_id = '$unit2'");
        while($row = mysqli_fetch_array($query3))
    {
    $z = $row['sensor2']; /// GETS data from WNSAVG column
           
   }

 

I have successfully made the script to work with looping individual units using one unit_id, but when it comes to multiple it doesn't work. 

 

Example output variable storing the finalised formula inside the end of the loop: 

$step1 =  str_replace("x",$x,$formula); 
$step2 =  str_replace("y",$y,$step1);
$step3 =  str_replace("z",$z,$step2);

eval('$output = ('. $step3 .');'); 

After the task i need to insert the output into the output table. 

 

FULL CODE EXAMPLE : Here

MySQL table Example: Here

 

Thanks, any help at all would be amazing.

Edited December 4, 2017 by hokletrain

[BigB]

Hi,

 

Would it not be better to do as much as you can in the SQL?

 

Use a compare SQL statement like:

 

SELECT A.*, B.*

FROM TABLE_A A

INNER JOIN TABLE_B B ON A.ID = B.ID

WHERE A.column1 != B.column1

OR A.column2 != B.column2

 

Not sure if this will help,

[Barand]

Don't run queries in loops, use joins to get the data in one query.

 

Try something like this. (Untested as pictures are notoriously difficult to load data into a test table)

SELECT c.title
     , x
     , y
     , z
     , POW(10, 0.009254 + (0.01201*x) + (0.2789*(SQRT(y)) - 0.09577*(SQRT(z)))) as calc
FROM
    calculation c
    JOIN (
         SELECT `AT` as x
              , unit_id  
         FROM test
         ) tx ON c.unit1_id = tx.unit_id
    JOIN (
         SELECT `RH` as y
              , unit_id  
         FROM test
         ) ty ON c.unit2_id = ty.unit_id
    JOIN (
         SELECT `AVGWN` as z
              , unit_id  
         FROM test
         ) tz ON c.unit3_id = tz.unit_id
WHERE c.title = 'fire_iindex';

[hokletrain]

Thanks for the responses guys. Sorry Barand i can attach some data if it makes it easier.    I would probably prefer using SQL to get the data as a whole anyway. I do like the idea of completing the process in MySQL. If i used joins inside of one query and then looped results could i still process this similar to above -e.g $x = $row['sensor]; . Also the sensors and unit_id's can change completely so the above MySQL statements only really works for the three sensors RH, AT and AVGWN?  This is the major reason i use the calculation table , because of how much information can vary.  

[Barand]

I have a gut feeling that your data could be better organised. That table you posted is a spreadsheet - db tables are not.

 

However, without knowing the full scenario, it is impossible to advise what that better organisation should be, It definitely looks in need of mormalization, perhaps something like

 

unit_id | datetime | type | value

 

(where type would be AT, RH etc) so each value has its own row, rather than being one of many columns in a longer row.

[hokletrain]

The screenshot is just a snippet from PHPmyAdmin. Could i use the same kind of query loops that i have but nest the query loops like such: 

    while($row = mysqli_fetch_array($query1))
    {
    $x = $row['sensor'];  
            
              while($row = mysqli_fetch_array($query2))
              {
              $y = $row['sensor1'];
               
                    while($row = mysqli_fetch_array($query3))
                    {
                    $z = $row['sensor2']; 
      
                    $step1 =  str_replace("x",$x,$formula); 
                    $step2 =  str_replace("y",$y,$step1);
                    $step3 =  str_replace("z",$z,$step2);

                    eval('$output = ('. $step3 .');'); 
                    var_dump($output);
                    
                     }
                }
           }

The guy I'm working for wants the script to be easily changed via front end , e.g why i did the calc table in the first place. So anything that needs to be changed/added can be done so in the CRUD table. I am relatively new to this kind of work , so got chucked in the deep end. 

[BigB]

I think having to many nested loops may use up resources and freeze up your page, may require a longer timeout set_time_limit(60); on the page and possibly a wait(1); depending on how much data, not the best solution I would say, if you could use something like "Barand" first post with the join or organise as much of the your data via SQL, you will have smoother code.

Edited December 5, 2017 by BigB