0

I am trying to delete a primary key item_id from items table which exists in another tables (cart and Wishlist ) as a composite Primary key with other fields.

I tried this way but it not working it's print "notDeleted" as in the condition:

   if(isset($_POST["deleteItem"])){
       try{
             $itemID = $_POST['itemID'];
        
        require('connection.php');
        $sql=$db->exec("DELETE cart,wishlist,items FROM cart ,wishlist, items WHERE cart.iid=wishlist.iid AND wishlist.iid=items.item_id AND items.item_id='$itemID'");
            if($sql==1)
            {
            header('location:index.php');
            }
            else{
                echo "NotDeleted";
            } 
        
        
    
          $db=null;
        }
        catch(PDOException $e){
        die($e->getMessage());
        }
        }

Also this is a screenshot of the error:

Can you know how to solve the problem?

output

Barmar
  • 741,623
  • 53
  • 500
  • 612
DEV
  • 65
  • 9
  • Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. Instead of building queries with string concatenation, always use [**prepared statements**](https://secure.php.net/manual/en/pdo.prepare.php) with [**bound parameters**](https://secure.php.net/manual/en/pdostatement.bindparam.php). See [**this page**](https://phptherightway.com/#databases) and [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for some good examples. – Alex Howansky Dec 30 '20 at 18:19
  • Multi-table `DELETE` requires that you list the tables to delete from between `DELETE` and `FROM`. – Barmar Dec 30 '20 at 18:19
  • [DELETE Syntax](https://mariadb.com/kb/en/delete/). It differs from one for MySQL. – Akina Dec 30 '20 at 18:24
  • what do you mean by listing the tables can you explain please?, is the listing I did wrong? – DEV Dec 30 '20 at 18:27
  • @Barmar The topic which you refer to deals with MySQL and is **NOT** applicable to MariaDB (see error screenshot carefully!). I vote for reopening this question. – Akina Dec 30 '20 at 18:38
  • The syntax is the same. MariaDB is a fork of MySQL. – Barmar Dec 30 '20 at 19:01
  • @Akina the question is tagged as mysql. If the OP uses mariadb and the issue is specific to mariadb, then it should be tagged as mariadb, not as mysql. – Shadow Dec 30 '20 at 19:03
  • 1
    @Akina In what way is it different? The multi-table syntax still requires you to put the table names before `FROM table_references` – Barmar Dec 30 '20 at 19:03
  • I did exactly as in the previous question but still having problem, can you have a look please? – DEV Dec 30 '20 at 19:07
  • You need to enable PDO exceptions so that the `catch` will show the error message. – Barmar Dec 30 '20 at 19:10
  • See https://www.php.net/manual/en/pdo.error-handling.php – Barmar Dec 30 '20 at 19:10
  • Why did you get rid of the `JOIN` syntax? You just needed to add `c, w, i` before `FROM` – Barmar Dec 30 '20 at 19:11
  • `exec()` returns the number of rows that were deleted. Why do you expect it to return 1 when you're deleting from multiple tables? – Barmar Dec 30 '20 at 19:15
  • @Barmar Then how can I make sure that the delete was successful? – DEV Dec 30 '20 at 19:35
  • I did what you said it's the same thing do you have another solution? @Barmar – DEV Dec 30 '20 at 19:36
  • If you don't get an exception, it was successful. – Barmar Dec 30 '20 at 19:46
  • but i already have exception for the try and i didn't see anything @Barmar – DEV Dec 30 '20 at 20:37
  • If you had an exception it would display the MySQL error message like it did before you edited the code. If it executes `echo "NotDeleted";` then you didn't get an exception, that message means it deleted more or less than 1 row. – Barmar Dec 30 '20 at 21:11
  • @Barmar In MariaDB `FROM` clause is compulsory whereas in MySQL it is optional (this is a root of OP's problem). Please open both manuals and compare. The query in OP's code is legal for MySQL but errorneous for MariaDB - is it enouph as a prove? *MariaDB is a fork of MySQL.* And how many years have passed since the separation? Now the amount of differences is large. – Akina Dec 31 '20 at 11:26
  • @Akina The OP had the `FROM` clause. They were missing the list of tables *before* the `FROM` clause. The original code was `DELETE FROM cart c INNER JOIN wishlist w INNER JOIN items i ON c.iid=w.iid AND w.iid=i.item_id AND i.item_id='$itemID'` – Barmar Dec 31 '20 at 14:57
  • It needed to be `DELETE c, w, i FROM cart c INNER JOIN wishlist w INNER JOIN items i ON c.iid=w.iid AND w.iid=i.item_id AND i.item_id='$itemID'` – Barmar Dec 31 '20 at 14:58
  • The best solution to this, though, is to declare foreign keys with `ON DELETE CASCADE`. Then you only have to delete from the master table, and all the related rows will be deleted from other tables automatically. – Barmar Dec 31 '20 at 15:00
  • *It needed to be DELETE **c, w, i** FROM* - yes, this is the difference between MySQL and MariaDB. In MariaDB this is clear FROM whereas in MySQL it may mix DELETE and FROM clauses. Which is not even mentioned in the thread which you refer to. – Akina Dec 31 '20 at 19:45

0 Answers0