mysql选择foreach后更新行 [英] Update rows after mysql select foreach

问题描述

我选择了3个结果:

$stmt = $handler->prepare("SELECT id,comments,likes,views FROM sites WHERE usr_id = '$usr_id'");
$stmt->execute();

此选择之后，我有3个结果.现在我要在另一个表中更新或为每个结果插入新行

After this select I have 3 results. Now I want in another table update or insert a new row for each result

这是我完整的代码

表中没有任何更新或新插入内容.有人可以帮我吗?

I don't have any update or new insert in table. Can anybody please help me?

  $stmt = $handler->prepare("SELECT id,comments,likes,views FROM sites WHERE usr_id = '$usr_id'");
  $stmt->execute();
  while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $rows[]=$row;
    foreach($rows as $row){
    $site_id = $row[id];    

            $stmt = $handler->prepare("SELECT id FROM session WHERE site_id = '$site_id' AND usr_id = '$usr_id'");
            $stmt->execute();
            $no=$stmt->rowCount(); 

            if ($no > 0)
            {
                $stmt = $handler->prepare("UPDATE session SET comments = '$comments' , likes = '$likes' , views = '$views'  WHERE usr_id = $usr_id AND site_id = $site_id");
                $stmt->execute();

            }
            else
            {
                $stmt = $handler->prepare("INSERT INTO session(user_id,site_id,comments,likes,views)VALUES('$user_id','$site_id','$comments','$likes','$views')");
                $stmt->execute();

            }
        }
  }

推荐答案

@ Miken32的答案将是理想的方法.

@Miken32's answer would be the ideal way.

直接修改代码的方法如下:

A direct fix to your code would be this way:

$stmt1 = $handler->prepare("SELECT id,comments,likes,views FROM sites WHERE usr_id = :usr_id");
$stmt1->bindValue(':usr_id', $usr_id);
$stmt1->execute();
while ($row = $stmt1->fetch(PDO::FETCH_ASSOC)) {
    $stmt2 = $handler->prepare("SELECT id FROM session WHERE site_id = :site_id AND usr_id = :usr_id");
    $stmt2->bindValue(':usr_id', $usr_id);
    $stmt2->bindValue(':site_id', $row['id']);
    $stmt2->execute();

    if ($stmt2->rowCount() > 0) {
        $stmt3 = $handler->prepare("UPDATE session SET comments = :comments , likes = :likes , views = :views  WHERE usr_id = :usr_id AND site_id = :site_id");
    } else {
        $stmt3 = $handler->prepare("INSERT INTO session(user_id,site_id,comments,likes,views)VALUES(:usr_id,:site_id,:comments,:likes,:views)");
    }
    $stmt3->bindValue(':comments', $row['comments']);
    $stmt3->bindValue(':likes', $row['likes']);
    $stmt3->bindValue(':views', $row['views']);
    $stmt3->bindValue(':usr_id', $usr_id);
    $stmt3->bindValue(':site_id', $row['id']);
    $stmt3->execute();
}

但这不是解决问题的最佳方法. INSERT ...更新DUPLICATE KEY 会更好.

But this is not the best way to go about it. INSERT ...UPDATE ON DUPLICATE KEY would be better.

这篇关于mysql选择foreach后更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！