保存新的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){

    $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";