保存新的div顺序以避免重复执行SQL查询

Question

<div class="artitle" data-id=1>lorem</div>

<div class="artitle" data-id=2>ipsum</div>

//and so on - about 500 divs

使用javascript，我对div上下进行了重新排序，并且需要将此新订单保存在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

    ]);

}

它有效，但是有更好的方法-避免重复执行sql查询500次？

Answer 1

您需要使用PHP构建一个mysql查询并使用INSERT INTOmysql功能（在一个查询中进行多次更新以更新多行，以下是您应该如何做的操作（我没有使用过预处理语句，自那以后您便可以找到答案）您有一个如何以语法方式构建查询的想法）：的PHP$aids = explode(',', $ids);$ainds = explode(',', $inds);$timeStart = microtime(true);$sqlQuery = 'INSERT INTO arts (id,ind) ';foreach($aids as $key=>$val){&nbsp; &nbsp; $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";