Check MySQL if data already exists, if not, INSERT. [PHP+jQuery Ajax]

Question

I'm having trouble creating php code that would insert values into MySQL database but only if they don't already exist.

I send array from javascript to PHP file using $.ajax type POST.

Do I need additional 'SELECT' query to check if values already exist?

PHP File(Works, inserts values):

<?php
SESSION_START();
include('config.php');
if(isset($_POST['predictedMatches'])&&$_SESSION['userid']){
    $predictedMatches=$_POST['predictedMatches'];
    $userid=$_SESSION['userid'];
}else die("ERROR");
$sql="";
foreach($predictedMatches as $predictedMatch){
    $sql.="INSERT INTO predictions(result,userFK,matchFK,tournamentFK) VALUES('".$predictedMatch['result']."','".$userid."','".$predictedMatch['id']."','".$predictedMatch['tourid']."');";
}
    if($conn->multi_query($sql) === TRUE){
        echo "OK";
    }else{
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
$conn->close();
?>

Use INSERT ... ON DUPLICATE KEY UPDATE ... it's faster than a SELECTand than an INSERT. I'd like to post my complete answer - @WilliamMadede It's not a duplicate and you are wrong, please remove your duplicate flag — notes-jj
– notes-jj, Commented Dec 18, 2015 at 14:03
@fred-ii It's not a duplicate of that question please remove your duplicate flag — notes-jj
– notes-jj, Commented Dec 18, 2015 at 14:07
@notes-jj Read this again: "Do I need additional 'SELECT' query to check if values already exist?" - The duplicate stands. If the OP has an issue with the duplicate, it's up to that person to tell me. He most likely visited the Q&A and figured out how to do it. If the OP doesn't come back and personally tells me that my close was a bad call, I'll reopen. Till then, the duplicate stands. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 18, 2015 at 14:11
@Fred-ii- I looked up your answer on similar post, I understand how you did it with SELECT query, but i have for each loop because of array, i can't do SQL SELECT query for each element in my array? — Troix
– Troix, Commented Dec 18, 2015 at 14:37

notes-jj · Accepted Answer · 2015-12-18 17:06:18Z

Use the ON DUPLICATE KEY UPDATE feature. It won't insert, if the primary key exists. But you have to update some value, so use the column which is in no index or in the least indexes () in your case probably result). Your primary key has to be composted out of the three FKs:

ALTER TABLE `predictions` ADD PRIMARY KEY( `userFK`, `matchFK`, `tournamentFK`);

PHP-Code, just the SQL statment (I'm a Java Guy, so i tried my best)

$sql.="INSERT INTO predictions (result, userFK, matchFK, tournamentFK) "
."VALUES('".$predictedMatch['result'] ."','".$userid."','"
.$predictedMatch['id']."','".$predictedMatch['tourid']."') "
."ON DUPLICATE KEY UPDATE result = result ;";

To know if the query was inserted you have to look at the affected row count:

1 Row - Insert
2 Rows - Update

Take a look at $conn->affected_rows after the query.

Performance

INSERT ... ON DUPLICATE KEY UPDATE is definitively faster than a SELECT and INSERT but it's slower than an INSERT of just the needed datasets. The update is done in the database, even if it is the same value. Unfortunately there is no ~~ON DUPLICATE KEY UPDATE INGNORE~~. If you have a lot of inserts, that will result in updates, than it may be better to use a cache, lookup values in an array and compare with the array before inserting. Only use the ON DUPLICATE KEY UPDATE as fallback.

Collectives™ on Stack Overflow

Check MySQL if data already exists, if not, INSERT. [PHP+jQuery Ajax]

1 Answer 1

Performance

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Performance

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related