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 `SELECT`and 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, Dec 18 '15 at 14:03
@fred-ii It's not a duplicate of that question please remove your duplicate flag — notes-jj, Dec 18 '15 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, Dec 18 '15 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, Dec 18 '15 at 14:37
@Troix so what's not working? It's as simple as querying if a row exists and if not, perform the `foreach`. E.g. `if(condition){...} else{ foreach }` - or is there something else that I'm not grasping? — Funk Forty Niner, Dec 18 '15 at 14:39
Yes, I have 4 matches(for example), first I want to check if any of them already exists in database, if not, i want to insert all of them. The way you wrote it in the comment above, I can't check for all of them if they already exist or can I? @Fred-ii- — Troix, Dec 18 '15 at 14:46
@Troix I've reopened the question. I'll let the other person help you out on this one (or someone else), *cheers* — Funk Forty Niner, Dec 18 '15 at 14:47
Maybe this question helps You: http://stackoverflow.com/questions/913841/mysql-conditional-insert — Roman Hocke, Dec 18 '15 at 15:19
@Troix [How to get multiple results from SQL query](http://stackoverflow.com/a/12614682/5330578). Its just one query and then process the results, in that question case it's not 4 queries. — notes-jj, Dec 18 '15 at 17:16

score 2 · Accepted Answer · answered Dec 18 '15 at 17:06

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.

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

1 Answers1

Performance

Linked