php - 检查行是否存在的更好方法更新否则插入新记录

Question

如果行存在更新或插入新记录，编写查询的最佳方法是什么？我正在尝试下面列出的垃圾代码:

<?php
for ($i = 0; $i < ($_POST['count']); $i++) {
    $form_details_subquestion_id = $_POST['form_details_subquestion_id'][$i];
    $form_details_section_id = $_POST['form_details_section_id'][$i];
    $mark = $_POST['mark'][$i];
    $remark = $_POST['remark'][$i];

    $result = $db->query("
        SELECT * 
        FROM audit_section_markrecord 
        WHERE 
          form_details_subquestion_id = $form_details_subquestion_id AND 
          audit_section_no = $audit_no
    ");
    if ($result->num_rows == 1) {
        $query2 = "
            UPDATE audit_section_markrecord 
            SET 
                mark = '$mark', 
                dateofmodify = '$date' 
            WHERE 
              form_details_subquestion_id = '$form_details_subquestion_id' AND 
              audit_section_no = '$audit_no'
        ";
        $result2 = $db->query($query2);

        $query3 = "
            INSERT INTO markrecord_update_details (
                audit_section_no,
                form_details_subquestion_id, 
                form_details_section_id, 
                mark,
                userlog,
                ipaddress
            ) VALUES (
                '$audit_no', 
                '$form_details_subquestion_id', 
                '$form_details_section_id', 
                '$mark', 
                '$user_staff', 
                '$ip'
            )
        ";
        $result3 = $db->query($query3);
    } else if ($result->num_rows == 0) {
        $query4 = "
            INSERT INTO audit_section_markrecord (
                audit_section_no,
                form_details_subquestion_id, 
                form_details_section_id, 
                mark
            ) VALUES (
                '$audit_no', 
                '$form_details_subquestion_id', 
                '$form_details_section_id', 
                '$mark'
            ) 
        ";
        $result4 = $db->query($query4);
    } else {
        echo "<script>alert('Error. Please contact IT support.')</script>";
        echo "<script language='javascript'>window.location.href='index.php'</script>";
    }
}

上面的代码写在for循环里面。我尝试获取 form_details_subquestion_id AND audit_no 是否存在，然后更新旧记录并插入到 markrecord_update_details；否则插入新记录。

不知道怎么用最容易写的代码。我只是想从这次旅程中学习。

Answer 1

最好的方法是 insert on duplicate key update .

第一个查询的解释。

/* execute this directly in MySQL to add unique key constraint*/
ALTER TABLE audit_section_markrecord
ADD UNIQUE(form_details_subquestion_id, audit_section_no);

/* this query is for calling from php */
INSERT INTO audit_section_markrecord
SET
  /* try to insert value */
  mark = '$mark', 
  dateofmodify = '$date',
  form_details_subquestion_id = '$form_details_subquestion_id',
  audit_section_no = '$audit_no'

/* if constraint check fails, i.e. */
/* there is a row with this form_details_subquestion_id and audit_section_no */
/* then just update mark and dateofmodify */
ON DUPLICATE KEY UPDATE
  mark = '$mark', 
  dateofmodify = '$date';

附言转义您的数据以防止sql-injection !

P.S2.要检查它是否有效，请在 MySQL 中运行 2 个查询:

INSERT INTO audit_section_markrecord
SET
  mark = 'good', 
  dateofmodify = '2015-11-10',
  form_details_subquestion_id = 10,
  audit_section_no = 23;

INSERT INTO audit_section_markrecord
SET
  mark = 'good', 
  dateofmodify = '2015-11-10',
  form_details_subquestion_id = 10,
  audit_section_no = 23
ON DUPLICATE KEY UPDATE
  mark = 'excellent', 
  dateofmodify = '2015-11-26';

行应该有 mark=excellent 和 dateofmodify=2015-11-26。

P.S3.如果它插入重复行，则添加唯一键时出现问题。查看 SHOW CREATE TABLE audit_section_markrecord。 (感谢 Barmar)。