Is there any way to find out duplicates value in mysql while inserting records?

Question

I read many questions but i didn't found my solution to my question.

My concern:

I am inserting new records in My SQL, using foreach loop in PHP. So, before inserting is there any way to find out, the records i am inserting in my sql is already present or not. If it is present then it should return "ALREADY EXIST".

AS PER MY CODE:

As i am using FOR-EACH LOOP , i want which qstonesid is already exist in my sql tbl_stickering. If exist then it should return ECHO "$qstonesid already exist" and if $qstonesid not exist then call my STORED PROCEDURE sp_stickering.

I hope i made my concern clear, and i am totally new to PHP and mySql, any help will be appreciated,

Below, I am sharing my PHP code

<?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...';
            }

below is my Stored Procedure I am using,

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

One approach would be to retrieve a listing of qstonesid from the array and check the database prior to calling the procedure.

To get a list of qstonesid values from $StoneArr you can use array_column.

$ids = array_column($StoneArr, 'qstonesid');

Next retrieve the listing of those $ids that exists in the database and build another listing of those that exist.

I am not sure what database extension you are using, but I presume something like this. See the working example link below for a PDO demonstration with prepared statements.

$rs = query('SELECT qstonesid FROM tbl_stickering WHERE qstonesid IN(' . implode(',', $ids) . ')');
$exists = array_column($rs, 'qstonesid');

if qstonesid is an array of string values, you can wrap each value with quotes by using.

'"' . implode('","', $ids) . ') . '"';

Lastly in your foreach loop use in_array to check in your resulting $exists array for the $Stone['qstonesid'] and use continue to move on to the next iteration if it is.

Working Example: https://3v4l.org/eJXu5

foreach ($StoneArr as $Stone) {
    if (in_array($Stone['qstonesid'], $exists)) {
        echo $Stone['qstonesid'] . ' Already Exists.';
        continue;
    }

    //...
}

Full Example:

  $stones = $_POST['stones'];
  $StoneArr = json_decode($stones, true);

  //retrieve listing of submitted qstonesid values
  $StoneArrIds = array_column($StoneArr, 'qstonesid');

  //retrieve listing of existing qstonesid
  /**
   * modify to suit your database extension
   * !! Subject to SQL Injection !!
   * !! HIGHLY RECOMMEND USING PREPARED STATEMENTS !!
   */
  $rs = query('SELECT qstonesid 
FROM tbl_stickering 
WHERE client_name = "' . $_REQUEST['clientname'] . '" 
AND qstonesid IN("' . implode('","', $StoneArrIds) . '")');
  $exists = array_column($rs, 'qstonesid');

  $updstmt = '';
  foreach ($StoneArr as $Stone) {
    //qstonesid already exists, display a message and skip insertion
    if (in_array($Stone['qstonesid'], $exists)) {
        echo $Stone['qstonesid'] . ' already exist';
        continue;
    }

    $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.'"'.'
    );';
  }

  if ($updstmt) {
      $res = query($updstmt);
      if (strlen($res) > 0) {
          echo $res;
      } else {
          echo 'Records added successfully...';
      }
  }