有没有办法在插入记录时找出mysql中的重复值？

Question

我读了很多问题，但我没有找到我的问题的解决方案。

我的顾虑：

我正在使用 PHP 中的 foreach 循环在 My SQL 中插入新记录。因此，在插入之前有什么方法可以找出我在我的 sql 中插入的记录是否已经存在。如果它存在，那么它应该返回“ALREADY EXIST”。

根据我的代码：

当我使用 FOR-EACH LOOP 时，我想要哪个qstonesid已经存在于我的 sql tbl_stickering中。如果存在，那么它应该返回 ECHO “ $qstonesid 已经存在”，如果$qstonesid不存在，那么调用我的 STORED PROCEDURE sp_stickering。

我希望我把我的担忧说清楚了，我对 PHP 和 mySql 完全陌生，任何帮助将不胜感激，

下面，我分享我的PHP代码

<?php

error_reporting(E_ERROR | E_PARSE);

include "config.php";

$stones = $_POST['stones'];

//echo json_encode($stones, true);

$StoneArr = json_decode($stones, true);

$updstmt = '';

  foreach ($StoneArr as $Stone)

  {

    $currentdate=!empty( $Stone['currentdate'] ) ? $Stone['currentdate'] : '0000-00-00 00:00:00';

    $qstonesid=$Stone['qstonesid'];

    $clientname = $_REQUEST['clientname'];

    $empid = $_REQUEST['empid'];

     $updstmt .= 'CALL sp_stickering('.'"'.$currentdate.'"'.',

                                          '.'"'.$clientname.'"'.',

                                          '.'"'.$qstonesid.'"'.',

                                          '.'"'.$empid.'"'.'

                                        );';

  }

    //echo $updstmt;

       $res = query($updstmt);

       if (strlen($res) > 0) {

           echo $res;

        } else {

            echo 'Records added successfully...';

            }

下面是我正在使用的存储过程，

CREATE DEFINER=`root`@`%` PROCEDURE `sp_stickering`(

 IN `_sdate_stickering` DATETIME,

 IN `_client_name` VARCHAR(100),

 IN `_qstonesid` VARCHAR(100),

 IN `_empid` INT

)

BEGIN

IF EXISTS (SELECT qstonesid FROM tbl_stickering WHERE qstonesid = _qstonesid AND client_name = _client_name) THEN

BEGIN

    UPDATE tbl_stickering SET empid = _empid WHERE qstonesid = _qstonesid AND client_name = _client_name;

END;

ELSE

BEGIN

    INSERT INTO tbl_stickering

    (sdate_stickering, client_name, qstonesid, empid)

    VALUES

    (_sdate_stickering, _client_name, _qstonesid, _empid);

END;

END IF;

END

Answer 1

一种方法是qstonesid从数组中检索列表并在调用过程之前检查数据库。要从您那里获取qstonesid值列表，$StoneArr可以使用array_column.$ids = array_column($StoneArr, 'qstonesid');接下来检索$ids数据库中存在的列表并构建另一个列表。我不确定您使用的是什么数据库扩展，但我认为是这样的。请参阅下面的工作示例链接，以获取PDO带有准备好的语句的演示。$rs = query('SELECT qstonesid FROM tbl_stickering WHERE qstonesid IN(' . implode(',', $ids) . ')');$exists = array_column($rs, 'qstonesid');ifqstonesid是一个字符串值数组，您可以使用引号将每个值括起来。'"' . implode('","', $ids) . ') . '"';最后，在您的foreach循环in_array中，用于检查结果$exists数组是否存在，如果是，则$Stone['qstonesid']用于continue继续进行下一次迭代。工作示例：https ://3v4l.org/eJXu5foreach ($StoneArr as $Stone) {&nbsp; &nbsp; if (in_array($Stone['qstonesid'], $exists)) {&nbsp; &nbsp; &nbsp; &nbsp; echo $Stone['qstonesid'] . ' Already Exists.';&nbsp; &nbsp; &nbsp; &nbsp; continue;&nbsp; &nbsp; }&nbsp; &nbsp; //...}完整示例：&nbsp; $stones = $_POST['stones'];&nbsp; $StoneArr = json_decode($stones, true);&nbsp; //retrieve listing of submitted qstonesid values&nbsp; $StoneArrIds = array_column($StoneArr, 'qstonesid');&nbsp; //retrieve listing of existing qstonesid&nbsp; /**&nbsp; &nbsp;* modify to suit your database extension&nbsp; &nbsp;* !! Subject to SQL Injection !!&nbsp; &nbsp;* !! HIGHLY RECOMMEND USING PREPARED STATEMENTS !!&nbsp; &nbsp;*/&nbsp; $rs = query('SELECT qstonesid&nbsp;FROM tbl_stickering&nbsp;WHERE client_name = "' . $_REQUEST['clientname'] . '"&nbsp;AND qstonesid IN("' . implode('","', $StoneArrIds) . '")');&nbsp; $exists = array_column($rs, 'qstonesid');&nbsp; $updstmt = '';&nbsp; foreach ($StoneArr as $Stone) {&nbsp; &nbsp; //qstonesid already exists, display a message and skip insertion&nbsp; &nbsp; if (in_array($Stone['qstonesid'], $exists)) {&nbsp; &nbsp; &nbsp; &nbsp; echo $Stone['qstonesid'] . ' already exist';&nbsp; &nbsp; &nbsp; &nbsp; continue;&nbsp; &nbsp; }&nbsp; &nbsp; $currentdate= !empty($Stone['currentdate'] ) ? $Stone['currentdate'] : '0000-00-00 00:00:00';&nbsp; &nbsp; $qstonesid = $Stone['qstonesid'];&nbsp; &nbsp; $clientname = $_REQUEST['clientname'];&nbsp; &nbsp; $empid = $_REQUEST['empid'];&nbsp; &nbsp; $updstmt .= 'CALL sp_stickering('.'"'.$currentdate.'"'.',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '.'"'.$clientname.'"'.',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '.'"'.$qstonesid.'"'.',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '.'"'.$empid.'"'.'&nbsp; &nbsp; );';&nbsp; }&nbsp; if ($updstmt) {&nbsp; &nbsp; &nbsp; $res = query($updstmt);&nbsp; &nbsp; &nbsp; if (strlen($res) > 0) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo $res;&nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo 'Records added successfully...';&nbsp; &nbsp; &nbsp; }&nbsp; }