Jeff Jeff - 5 months ago 12
MySQL Question

mysql insert - skip duplicates syntax error - sprintf (WHERE NOT EXISTS )

I would like to insert a row but not insert data that already exists. Is it possible to do the below with sprint? I need help with the syntax for this.

$insertSQL = sprintf("
INSERT INTO post_view (trade_id, user_id, timestamp)
VALUES (%s, %s, %s)
SELECT trade_id,
user_id,
timestamp
FROM post_view
WHERE NOT EXISTS
(SELECT id
FROM post_view
WHERE trade_id = %s
AND user_id = %s)",
GetSQLValueString($_GET['trade_id'], "int"),
GetSQLValueString($_SESSION['user_id'], "int"),
GetSQLValueString(time(), "int"),
GetSQLValueString($_GET['trade_id'], "int"),
GetSQLValueString($_SESSION['user_id'], "int"));

mysqli_select_db($connection, $database_connection);
$Result = mysqli_query($connection, $insertSQL) or die(mysqli_error($connection));


Currently getting below error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT trade_id, user_id, timestamp FROM post_view WHERE NOT' at line 3

Answer

An INSERT statement can either have VALUES to insert literal values, or a SELECT clause to get the values from a query, but it can't have both. Put the values you want into the SELECT statement using the special table DUAL as the source.

$insertSQL = sprintf("
    INSERT INTO post_view (trade_id, user_id, timestamp)
    SELECT %s, %s, %s
    FROM DUAL
    WHERE NOT EXISTS
        (SELECT id
         FROM post_view
         WHERE trade_id = %s
         AND user_id = %s)", 
GetSQLValueString($_GET['trade_id'], "int"),
GetSQLValueString($_SESSION['user_id'], "int"), 
GetSQLValueString(time(), "int"),
GetSQLValueString($_GET['trade_id'], "int"),
GetSQLValueString($_SESSION['user_id'], "int"));