save new divs order avoiding to repeat sql query

Question

<div class="artitle" data-id=1>lorem</div>
<div class="artitle" data-id=2>ipsum</div>
//and so on - about 500 divs

using javascript I reordered the divs up and down and need to save this new order in database(column ind).

let ids = $.map($('.artitle'), (e) => $(e).data('id')).join(',');
let inds = $.map($('.artitle'), (e) => $(e).index()).join(',');
//$.post... - send the variables on remote server

php

$aids = explode(',', $ids);
$ainds = explode(',', $inds);
foreach($aids as $key=>$val){
    $sql = "update arts set ind = :aind where id = :aid";
    $st = $db->prepare($sql);
    $st->execute([
        ":aind" => $ainds[$key],
        ":aid" => $val
    ]);
}

It works but is there a better way - to avoid repeating sql query 500 times?

Well you certainly should not prepare the query inside of the loop. This causes the DB more work as it has to prepare the query each time. This (basically) is how SQL Injection is prevented, the instructions (SQL) are sent separate from the Data, this way the DB knows when it's time for Data that there are no instructions in it etc... so by preparing the query again your asking the DB to re-evaluate the same instructions. That is baring any caching of it, I am not sure about caching, but it's a good practice none-the-less. — ArtisticPhoenix, Mar 28 '19 at 07:00
it's not worthy of an answer, but just put these 2 lines above the foreach `$sql = "update arts set ind = :aind where id = :aid";` and `$st = $db->prepare($sql);` — ArtisticPhoenix, Mar 28 '19 at 07:05
@ArtisticPhoenix, thanks a lot, prepare procedures are not now repeated, but execution still **is**. — qadenza, Mar 28 '19 at 07:08
As I said the server may cache the prepared statement, I am not sure as to what extent that has but.... Have you actually done any bench marking of the code to see where the time is being spent? It wouldn't be a bad idea to time it before and after moving that etc... — ArtisticPhoenix, Mar 28 '19 at 07:09
Eh? Preparing the query in the loop is EXACTLY what you SHOULD do! Prepare it in the loop, then execute, once, outside the loop. — Strawberry, Mar 28 '19 at 07:12
@Strawberry, i tried - `prepare inside foreach` and `execute outside` - doesn't work. — qadenza, Mar 28 '19 at 07:15
Can you use `INSERT... ON DUPLICATE KEY ` with the VALUES option — Strawberry, Mar 28 '19 at 07:17
I am working on an answer, will provide soon with the above `INSERT ... ON DUPLICATE KEY` — shivgre, Mar 28 '19 at 07:21

shivgre · Accepted Answer · 2019-03-28T07:44:23.143

You need to build a single mysql query using PHP and use the INSERT INTO mysql feature(multiple updates in one query to update multiple rows, below is how you should do it(I haven't used prepared statement, that's for you to figure out since now you have an idea how to build the query pro grammatically) :

PHP

$aids = explode(',', $ids);
$ainds = explode(',', $inds);

$timeStart = microtime(true);

$sqlQuery = 'INSERT INTO arts (id,ind) ';

foreach($aids as $key=>$val){

    $sqlQuery .= "VALUES ($val,$ainds[$key]),";

}

#REMOVE THE LAST COMA(,)
$sqlQuery = substr($sqlQuery, 0, -1);

$sqlQuery .= 'ON DUPLICATE KEY UPDATE id=VALUES(id),ind=VALUES(ind);';

#RUN THIS AS REQUIRED, YOU GOT YOUR DYNAMICALLY GENERATED MYSQL MULTIPLE UPDATE STATEMENT#
#$sqlQuery;

$timeEnd = microtime(true);
$time = $timeEnd - $timeStart;

echo "Took $time seconds\n";

thanks a lot. How to check the time of execution of a query. Are you sure that `INSERT INTO` and `ON DUPLICATE KEY UPDATE` is faster than original? — qadenza, Mar 28 '19 at 07:33
You could use microtime(true), Updating my answer. But not sure if that will tell the mysql query time too. — shivgre, Mar 28 '19 at 07:42

save new divs order avoiding to repeat sql query

1 Answers1