0

I Have a MySQL database with a number of tables. One of these tables (composed of around 3000 rows) needs to be updated with a semi-complicated function that I've put together in PHP. Currently my solution is where I first read the desired column from the table, and then iterate through the column updating each value for each step:

$result = mysqli_query($con,"SELECT id, column1,column2 FROM $table");

$u=0; //Counter for loop

//Calculate value for each Row
if(mysqli_num_rows($result) > 0 ){
        while($row = mysqli_fetch_assoc($result)){

            $dataArray[] = $row ;                   
            $dataArray[$u]['columnx'] = test_function($dataArray[$u]['column2']);

            $id_des = $dataArray[$u]['id'];
            $column1 = $dataArray[$u]['column1'];

            $query11 = mysqli_query($con, "UPDATE $table SET $table.column1= ".$dataArray[$u]['column1']." WHERE id = $id_des;");
            $u = $u+1;

        }
}

Ive also done something similar with using a loop to build a very big Update Query, using a CASE clause, which is a little faster. Both methods however are very slow, and seem crude.

Note : The function used above is to convert a percentage to z score if thats any help.

Is there any way to do this without a loop or speed up execution? Even a single SQL statement perhaps ? Any help would be greatly appreciated.

masterofimps
  • 49
  • 6
  • check out my answer here: http://stackoverflow.com/a/35727615/4354249 – Farside Mar 01 '16 at 18:30
  • @Farside Good suggestion in your other answer, but I had already tried building a CASE construction the same as your answer, however as it would involve over 3000 values, its still a bit on the slow side (though I may have to accept this is the fastest Il achieve!). Is the ON DUPLICATE KEY UPDATE construction any faster than then CASE method ? – masterofimps Mar 01 '16 at 18:40
  • Yes, I'd recommend to go ON DUPLICATE KEY UPDATE way, it would be the good solution. CASE I think would be slower for big data in MySQL. – Farside Mar 01 '16 at 19:59

0 Answers0