Php Mysql DELETE From 2 Tables Using Id

Question

In my admin dashboard I created a page that show information from 2 tables using a foreign key Id_clt, now I created the delete query but it didn't work Please correct me if I did something wrong, Thanks.

 $conn = mysqli_connect("localhost", "root", "", "vm");
    // DELETE QUERY
    if(isset($_GET['id'])){

        $de=$_GET['id'];    
        $sql_de="DELETE FROM clt_t1,t2 INNER JOIN t2.Id_clt ON clt_t1.Id_clt=t2.Id_clt WHERE Id_clt=$de ";

        $rd=mysqli_query($conn,$sql_de);

        if($rd){
            header("refresh:1; url=welcomeadmin.php");
        }
        else{
            mysqli_error($conn);
            echo "error";
        }

    }

admin.php page:

 <form method="GET" action="deletecomm.php">
    ..
    <td><a href="javascript:delete_data(<?php echo $data['Id_clt'];   ?>)">X</a></td>
    ..
    </form>

Looks like your delete query is not proper please visit http://stackoverflow.com/a/16481475/3568847 Also please add `echo $sql_de;` in your code and run the printed query directly in phpmyadmin see if that has an error. — Praveen Kumar, Mar 15 '17 at 10:49
Yes sir @Kumar , it shows "errorDELETE FROM t1,t2 INNER JOIN t2.Id_clt ON clt_t1.Id_clt=t2.Id_clt WHERE Id_clt=30 " so the error is from the query itself.. — yassir.r, Mar 15 '17 at 10:55
@yassir.r please visite the link i have provided and make your query look like that :) — Praveen Kumar, Mar 15 '17 at 11:01
Yes mr @kumar i did thanks, but in this subject they are working on (sql server syntaxe) i tried doing the same method for my (deletecomm.php)query (php/mysql) .. i'm still searching where the problem is. — yassir.r, Mar 15 '17 at 11:17

score 0 · Answer 1 · answered Mar 15 '17 at 10:52

0

The syntax of DELETE ... FROM ... JOIN in MySQL is not quite that.

Instead of

DELETE FROM clt_t1,t2  
INNER JOIN t2.Id_clt ON clt_t1.Id_clt=t2.Id_clt 
WHERE Id_clt=:de

You should use

DELETE clt_t1
FROM clt_t1
INNER JOIN t2 ON clt_t1.Id_clt=t2.Id_clt 
WHERE clt_t1.Id_clt=:de

And avoid direct variable interpolation by the way.

answered Mar 15 '17 at 10:52

ffflabs

17,166
5
51
77

Hi @amenadiel thanks for your quick reply, i tested the syntax using this method, but nothings happen, there is still errors in the query.. – yassir.r Mar 15 '17 at 11:04
what error exactly? Try running that same query in phpmyadmin – ffflabs Mar 15 '17 at 11:07
Yes i did the same but the query in my deletecomm.php don't work – yassir.r Mar 15 '17 at 11:12
" #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 'INNER JOIN salades ON clt_vertmidi.Id_clt=salades.Id_clt WHERE clt_vertmidi.I' at line 3 " – yassir.r Mar 15 '17 at 11:20
Thank you very much @amenadiel when you shared with me this syntaxe and didn't work i searched for similar situations now all is perfect – yassir.r Mar 15 '17 at 14:39

score 0 · Answer 2 · answered Mar 15 '17 at 14:37

0

Thanks to everyone who tried to help me & gave me advice I FOUND THE RIGHT SOLUTION:

$sql_de="
DELETE t2 
FROM t2
LEFT JOIN clt_t1 ON clt_t1.Id_clt=t2.Id_clt 
WHERE clt_t1.Id_clt='".$de."'
";

answered Mar 15 '17 at 14:37

yassir.r

73
8

Php Mysql DELETE From 2 Tables Using Id

2 Answers2