CODEWITHSUNDEEP
phpmysql
Devy
Devy

Reputation: 83

INSERT... NOT EXISTS works on MAMP, but doesn't work on real server

I'm trying to migrate my site, which I was developing localy on MAMP server, to live server. All queries works on MAMP as expected. On live server I'm getting errors on queries which have "INPUT... NOT EXISTS".

MAMP server: PHP - 7.4.2 MySQL - 5.7.26 InnoDB

Live server: PHP - 7.4 MariaDB - 10.2 InnoDB

structure and primary keys are the same - checked all tables

$user_id, $id, $date, $summ - are variables, which I'm getting vis POST method.

Query:

if ($result = $link->prepare("INSERT INTO sb_user_checks (user_id, check_id, date_update, summ, updated_by) 
    SELECT ?, ?, ?, ?, 'You'
    WHERE NOT EXISTS (SELECT date_update FROM sb_user_checks WHERE user_id=? AND check_id=? AND date_update=?)
    ")) { 
        $result->bind_param('iisiiis', $user_id, $id, $date, $summ, $user_id, $id, $date);
        $result->execute();
        if (mysqli_affected_rows($link)>0) {echo 'added';}
        $result->close();
    }

I'm getting following error:

#1064 - 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 'WHERE NOT EXISTS (SELECT date_update FROM sb_user_checks WHERE user_i' at line 3 Is it anything to do with MySQL version, or it's incorrect query? But, again, it works correctly on MAMP.

Upvotes: 1

Views: 158

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

In MySQL, and I believe MariaDB, a query may make use of DUAL as a stand in for a table when the syntax requires it but the overall logic does not. For example, in this case:

SELECT ?, ?, ?, ?, 'You'
FROM DUAL
WHERE NOT EXISTS ...

DUAL acts similarly to a single row, no column table; and is really only meant as a stand in for scenarios like this. These factors have a number of implications:

  • It cannot be used with SELECT *
  • It cannot be aliased
  • It cannot be JOINed to.
  • more... those limitations are just ones I discovered from running quick tests manually as I wrote this answer.

Upvotes: 1

Related Questions