0

There is a table with columns: id (auto_increment), abc and xyz. Then the user fill in a form (I have checked the input) and if the variable combination does not exist in the table (to avoid duplicate entries), it has to be inserted in the table.

This code should check if the combination abc and xyz are already in the database and then insert it.

<?php
$abc = 13;
$xyz = 54;
if ($stmt = mysqli_prepare($link, "IF NOT EXISTS (SELECT 1 FROM table1 WHERE abc = ? AND xyz = ?) BEGIN INSERT table1 (abc, xyz) VALUES (?, ?) END"))
            {
                mysqli_stmt_bind_param($stmt, "iiii", $abc, $xyz, $abc, $xyz);
                mysqli_stmt_execute($stmt);
                mysqli_stmt_close($stmt);
            }
?>

Problem: no records are coming into the database. Why?

Helena
  • 13
  • 3
  • Aren't you missing like half of the SQL statement? – Dharman Jul 15 '21 at 12:21
  • Because that is not how `if not exists` is used, and you have a SQL error. (Not to mention the insert is wrong too) you probably want a `replace into` or `on duplicate key update` type of query instead? – Paul T. Jul 15 '21 at 12:22
  • I'm not sure what you thought would happen when you used `IF NOT EXISTS` like this but the solution is simple. Create a UNIQUE constraint and then INSERT like you normally would – Dharman Jul 15 '21 at 12:23
  • Thank you for thinking about this. I will use the old method; first check if the row exists and then insert it. I was wondering if I could do this in 1 statement, but it's too difficult. – Helena Jul 15 '21 at 12:43

0 Answers0