Can't delete from a table with two conditions using PHP

Question

I am trying to delete a row from a table using two conditions. this is my current code :

<?php 
require 'database.php'; 
$id=0;
$od=0; 
if(!empty($_GET['id_espece'])){ 
    $id=$_REQUEST['id_espece']; 
}
if(!empty($_GET['id_valor'])){ 
    $od=$_REQUEST['id_valor']; 
} 
if(!empty($_POST)){ 
    $id= $_POST['id_espece'];
    $od= $_POST ['id_valor']; 
    $pdo=Database::connect(); 
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


         $sql = 'DELETE FROM mode_valorisation WHERE "id_espece" = ? and "id_valor" = ? ';
            $q = $pdo->prepare($sql);
            $q->execute(array($id,$od));
            Database::disconnect();

             header("Location: page-valo.php");
        }
    ?>

<form class="form-horizontal" action="page-supprimervalo.php" method="post" style="background: #89e29a; border-color: black; border-radius: 20px; padding: 5px;width: 500px; position: relative;right: -480px;" >
      <input type="hidden" name="id_espece" value="<?php echo $id;?>"/>
      <input type="hidden" name="id_valor" value="<?php echo $od;?>"/>
</form>

I Tried the solution proposed here: How to Delete a Record in php that has composite (multi-column) primary key

$sql = "DELETE FROM mode_valorisation WHERE id_espece = ? and id_valor = ? limit 1";

but it gives me the following error :

syntax error at or near "limit" LINE 1: ..._valorisation WHERE id_espece = $1 and id_valor = $2 limit 1 ^

Thank you ;

The error makes no sense though, there is no `limit` in your query. You sure that error is for this code? — chris85, Jul 09 '17 at 14:40
Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — chris85, Jul 09 '17 at 14:47
Which is your code, the original or the update? Please update the full code block. — chris85, Jul 09 '17 at 15:03
Your larger code block still has `$sql = 'DELETE FROM mode_valorisation WHERE "id_espece" = ? and "id_valor" = ? ';` though, can you make that match your current code so we aren't debugging the wrong code block. — chris85, Jul 09 '17 at 15:12

score 1 · Answer 1 · answered Jul 09 '17 at 18:50

You cannot use LIMIT in DELETE statement.

So the correct SQL with placeholders is:

DELETE FROM mode_valorisation WHERE id_espece = ? and id_valor = ?;

-- this will delete all rows (if any) with specified id_espece and id_valor.

If you need to delete only single row, use DELETE with SELECT sub-query (I suppose mode_valorisation has PK id; if not, edit the query to use the correct PK name):

delete from mode_valorisation where id in ( 
  select id from mode_valorisation
  where id_espece = ? and id_valor = ?
  order by id desc
  limit 1
);

-- here you can control, which single row among several (if any) you will delete, using ORDER BY statement; I used order by id desc to delete "the latest" row.

score -1 · Answer 2 · answered Jul 09 '17 at 14:45

-1

First of all, do not use quotes for table names/column names. Second, there is no 'limit' in your query. I run the code at my end with the same parameters and table and it worked just fine.

answered Jul 09 '17 at 14:45

Niv Apo

983
1
9
18

I don't thgink it's a problem of quotes because I just worked the same code with one condition in the query and it works – Issam Jul 09 '17 at 15:10
"do not use quotes for table names/column names" is irrelevant and in some cases even wrong advice (for example, if table/column names are camel-style; it's not the case here, but double quoting is "good" thing for many cases, it's definitely not a bad thing here; however, it's optional here). – Nick Jul 09 '17 at 18:44

Can't delete from a table with two conditions using PHP

2 Answers2