You have an error in my request but which?-CodePudding

The request of my function has a problem:

function rdvDelete($rdv)
{
    $pdo = connexion();
    $sql = $pdo->prepare("DELETE FROM visitors,rdvs WHERE rdvs.idRdv = :rdv AND visitors.idVisitor = rdvs.idVisitor"); <-- line 34
    $sql->execute(['rdv' => $rdv]);
}

$rdv = 3 !!!

And this display this error

[error] 639#639: *9791 FastCGI sent in stderr: "PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 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 rdvs.idRdv = ? AND visitors.idVisitor = rdvs.idVisitor' at line 1 in /var/www/MY-WEBSITE/model/m_manage-rdvs.php:34

Thanks

CodePudding user response：

I believe your prepared statement has an error. You need to specify both parameters in the statement before executing with the actual values of the parameters. Do this

function rdvDelete($rdv)
{

    $pdo = connexion();
    $sql = $pdo->prepare("DELETE FROM visitors,rdvs WHERE rdvs.idRdv = :rdv AND visitors.idVisitor = :rdvs); <-- line 34
    $sql->execute(['rdv' => $rdv, 'rdvs' = $rdvs]);
}

CodePudding user response：

SQLite does not support explicit or implicit joins in DELETE statements. You are attempting the implicit join with comma separated tables in FROM clause and join condition in WHERE. For correlated delete statements, consider using IN or EXISTS operators on a subquery:

DELETE FROM visitors
WHERE idVisitor IN (
    SELECT rdvs.idVisitor
    FROM rdvs
    WHERE rdvs.idRdv = :rdv
)

DELETE FROM visitors
WHERE EXISTS (
    SELECT 1
    FROM rdvs
    WHERE rdvs.idVisitor = visitors.idVisitor
      AND rdvs.idRdv = :rdv
)