SQL updating column where column value equal to temp table id

[hockletrain96]

Hey guys, I’m trying to update my table values based on look up values from a CSV file.But I’m having trouble, I have tried joins which work to a certain extent only allowing me to match one record at a time and also an update query which does the same thing. I would like to match every value and update appropriately. Firstly I run a query to get the db table details e.g columns to match. I then update the data table with the matched data.

CSV Data:
162.03,1000
2,1500
3,1832
4,2176

Temp_table:

Array ( [temp_id] => 162.03 [value] => 1000 ) 
Array ( [temp_id] => 2 [value] => 1500 ) 
Array ( [temp_id] => 3 [value] => 1832 ) 
Array ( [temp_id] => 4 [value] => 2176 )

SQL&PHP Query:

  include '../database.php';
include '../lib/library.php';

$db = DB();
$query = $db->prepare("SHOW COLUMNS FROM `data`");
$query->execute();
$fields = array();
while($result = $query->fetch(PDO::FETCH_ASSOC)){
    $fields[] = $result['Field'];
   
}
$num = rand(1, 1000000);
$table = 'temp_sensor'.$num;

$query = mysqli_query($mysqli, "SELECT * from calculation 
where formula  = 'lookup'");
    while($row = mysqli_fetch_array($query))
    {
      $title = $row['title'];  ///custom_name column
      $sensor = $row['RTU1sensor'];  //WL Column
      $time = $row['time'];
       $path = $row['lookup_path'];
        
             if(!in_array($title,$fields)){

           $sql="ALTER TABLE data ADD $title VARCHAR(60) NULL ";
$result = $mysqli->query($sql);
           $sql="INSERT INTO rtu_sensors (sn,sensor) values($rtu,'$title')";
$result = $mysqli->query($sql);
       } 
     $query_createTemporaryTable = "CREATE TEMPORARY TABLE `$table` (temp_id VARCHAR(20),value VARCHAR(20))";

 $result_createtemptable = mysqli_query($mysqli, $query_createTemporaryTable);

 $query_insertintotable = "                        
LOAD DATA INFILE '$path' INTO TABLE $table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r'  (temp_id,value);";

 $result_insertintotable = mysqli_query($mysqli, $query_insertintotable) or die(mysqli_error($mysqli));

        $sql="UPDATE data,$table SET data.$title = $table.value where data.$sensor = 
        $table.temp_id ";
            $stmt = $mysqli-> prepare($sql);
            $stmt -> execute();

 $delete_temptable = "DROP TABLE `$table`";
 $result_temptable = mysqli_query($mysqli, $delete_temptable) or die(mysqli_error());
        
 mysqli_close($mysqli);
    }
    }

Note: Only showing the columns I’m working with

Data table before Update:

Data table after Update:

Any help would be greatly appreciated , thanks .

[spaceshiptrooper]

@hockletrain96

mysqli_query

@hockletrain96

$mysqli-> prepare

You should be using the prepared ones.

@hockletrain96

UPDATE data,$table SET data.$title = $table.value where data.$sensor = $table.temp_id

You should also stop doing this when you use the prepared statements because you are using them incorrectly.

[hockletrain96]

Yes , I know some of the code is being used incorrectly at the moment. I have been removing/editing a lot and I’m just trying to focus on the output at the moment. After I can figure out the problem i will re code the document a lot neater.

[benanamen]

Tell us about the real problem you are trying to solve instead of your attempt at solving it.

[hockletrain96]

Okay to sum it up i want to match two columns values , one column from the data table and one from the temp table. Where there is a match insert/update the temp table value into the data table column (custom_name in this case).

[benanamen]

OK, that is a little better. How about a little higher level description of what you have going on. The overall broader task task at hand. Your still describing how to solve the real problem, not what the real problem actually is. Let us tell you how to go about solving the real problem.

[hockletrain96]

Sorry I’m new and still learning . The CSV’s are used as a look up table, the first id values in the CSV are used as the lookup id which is the raw value and should match the value in the database data table. E.g in this case 162.03 is the raw value in the database so the CSV ID which is also 162.03 matches that. The value in the CSV is the lookup value (e.g 1000) which we put it in a new custom column and the same record as the raw value. This process is dynamic and users can basically create a custom column which they want to store their lookup data in,which is mainly for measuring water tank levels.

Process
Example CSV has ID 162.03 and value 1000 , WL value is equal to 162.03 so value 1000 is inserted into the same record custom column.

I hope that is enough information for you?

[benanamen]

@hockletrain96

measuring water tank levels.

Yes and no, but you have opened the door to what I have been trying to get from you. Ok, the REAL task at hand has something to do with measuring water tank levels. THAT is what I am referring to as the real problem/task. Tell us about that. I dont care about how you are trying to handle at this point.

Along the lines of… users measure water tank levels by xxxx. This measurement is logged/saved/whatever by whatever. We have a csv file where the data came from xxx by doing xxxx.

Hopefully this clears up what I am trying to get from you.

[hockletrain96]

Oh right well the water tank levels are logged and recorded by automated sensors, which we collect and store in the database. The CSV is manually written by users to show what values they want converted e.g the raw values to new values.

[benanamen]

Ok, now we are getting somewhere. Expound on this like I am the new guy on the job.

What format do the sensors record the data? How does the data get from sensor to database? How exactly are you storing the sensor data in the DB?

Expound on what this conversion is all about.

[hockletrain96]

The sensor data is recorded and stored in the database with a decimal format. We use an auto script to import the data to the database which runs through a scheduled task. The files are sent from the station which controls the sensors into a local folder (JSON format) and the scheduled script imports them. Data is imported through parsing the JSON and importing where the field names are the same. Sensor data is stored based on the unit it comes from which also has the unit ID and the dates collected. Sorry i didn’t think we needed to go this in depth? I guess it is best to understand the problem at hand.

[benanamen]

@hockletrain96

Sorry i didn’t think we needed to go this in depth? I guess it is best to understand the problem at hand.

PERFECT! This is exactly what I was asking for! Yes, it is paramount to understand the actual situation we are writing code for. What you were doing is known as an XY Problem. Basically asking about a solution to your solution rather than asking what the solution should be. No big. Happens all the time on forums. More info http://xyproblem.info/

We are just about ready to tackle a solution. As previously asked, I need to know what this conversion is all about in detail. I would also like to see a sample of a raw sensor record and an SQL dump of your DB Schema so I can recreate on my end for testing if need be. I would also like to know about the sensor. Is the only sensor option to export to a JSON file? If possible, it would be optimum to go straight from the sensor into the DB.

[hockletrain96]

I can save you the time now , thanks for the help and effort in your responses. I figured out how to solve the problem, I used an SQL loop to chuck the temporary table into an associative array , then i just used a foreach loop to cycle through and update the custom column on each key and value. Once again thanks a lot.

[benanamen]

While that may work, my experience tells me that is a hack for how it should actually be done. We came so far. Wouldn’t you like to know how it should actually be done? Pretty sure whatever it is that needs to happen can all be done in the DB with a proper query.