Deleting tables with mysqli_multi_query()

Question

I'm trying to delete my principle table 'eleve', with deleting others table which has their primary key in this one. I tried like in the attached code, but I got an error:

(Erreur de syntaxe pr�s de 'from bource where ID_BOURCE = 1delete from class where ID_CLASS = 1delete from p' � la ligne 1)

Any ideas?

if (isset($_POST['butAj4'])) {
  $queryDel = "delete from inscription where NUM_INSCRIPTION = $NUM_INSCRIPTION";
  $queryDel. = "delete from bource where ID_BOURCE = $ID_BOURCE";
  $queryDel. = "delete from class where ID_CLASS = $ID_CLASS";
  $queryDel. = "delete from project where ID_PROJECT = $ID_PROJECT";
  $queryDel. = "delete from annee_scolaire where ID_ANNEE = $ID_ANNEE";
  $queryDel. = "delete from eleve where CIN_ELEVE = '$InputCIN'";

  if (mysqli_multi_query($con, $queryDel)) {
    $msg3 = "<div class='alert alert-success'>Bien suppression</div>";
  } else {
    $msg3 = "<div class='alert alert-danger'>error dans la suppression</div>".mysqli_error($con);

  }
}

That should work you're just forgetting the `;` at the end of each query. For example: `"delete from eleve where CIN_ELEVE = '$InputCIN';"` — MalcolmInTheCenter, Feb 04 '18 at 05:39

Bobby Axe · Accepted Answer · 2018-02-04T03:54:29.257

2

Not sure why you want to do it like this there are better ways but to answer your question do it like this:

$queryDel = "
delete from inscription where NUM_INSCRIPTION= $NUM_INSCRIPTION ;
delete from bource where ID_BOURCE = $ID_BOURCE ;
delete from class where ID_CLASS = $ID_CLASS ;
delete from project where ID_PROJECT = $ID_PROJECT ;
delete from annee_scolaire where ID_ANNEE = $ID_ANNEE ;
delete from eleve where CIN_ELEVE = '$InputCIN'; ";

$result=mysqli_multi_query($con,$queryDel);

and also remember to clear the results else you wont be able to perform another query but i don't think delete will return a result.

while(mysqli_next_result($con)){;} //clear any remaining query results.

also remember that if one query fails all the rest will not run. so to debug try running each query separately first and make sure it all works since its a delete statement back up your database before running the query and restore as when needed.

edited Feb 04 '18 at 03:54

answered Feb 04 '18 at 03:47

Bobby Axe

1,491
13
29

thanks man its working well now,can you suggest a better way to do it ? – medo100000S Feb 04 '18 at 04:11
i will suggest using PDO or if your not too good with classes just make sure you implement mysqli_stmt_bind_param(); see: https://secure.php.net/manual/en/mysqli-stmt.bind-param.php A delete statement can be deadly if injected so take all necessary steps to protect your database – Bobby Axe Feb 04 '18 at 04:22
i really appreciate that,i will check about it – medo100000S Feb 04 '18 at 04:24
@medo100000S multi-query is dangerous. Multi-query with string interpolation is fundamentally and critically flawed. If a malicious user could find a way to set the value of `$NUM_INSCRIPTION` to a string such as `0; DROP TABLE inscription;` then you should be able to see what could go terribly, terribly wrong here. – Michael - sqlbot Feb 04 '18 at 17:18
@Michael-sqlbot you are right man,i used to exploit single query in each request – medo100000S Feb 09 '18 at 22:10

Deleting tables with mysqli_multi_query()

1 Answers1

Linked