Execute SQL Query with PHP

Question

I am new to using PHP to run SQL commands but what I'm trying to do is truncate specific tables within my database when the script is run. I can do this fine truncating just one table but when I attempt multiple table I run into issues! Code is below, any pointers?! Thanks in advance

 <?php
    var_dump($_POST);
    $myServer = $_POST['host'];
    $myUser = $_POST['user'];
    $myPass = $_POST['password'];
    $myDB = $_POST['db'];

    $con = mysqli_connect($myServer, $myUser, $myPass) or die("Connection 
    Failed");
    mysqli_select_db($con, $myDB)or die("Connection Failed");


    $query = ("
        SET FOREIGN_KEY_CHECKS = 0;

        TRUNCATE table customers;

        TRUNCATE table customers2;

        SET FOREIGN_KEY_CHECKS = 1;


    ");

    if(mysqli_query($con, $query)){
    echo "table empty";}
    else{
     echo("Error description: " . mysqli_error($con));}


    ?>

Possible duplicate of [Two mysqli queries](https://stackoverflow.com/questions/10924127/two-mysqli-queries) — Jens, Oct 04 '17 at 14:04
Your query should not have parenthesis surrounding it. You cannot pass multiple statements into a mysqli query. Either do multiple queries or use mysqli_multi_query. — aynber, Oct 04 '17 at 14:04
You said you run into issues. "issues" as? any error messages or something? — sequel, Oct 04 '17 at 14:05
try executing one query per call to mysqli_query(). not 4 at once — Ivo P, Oct 04 '17 at 14:05

score 2 · Accepted Answer · answered Oct 04 '17 at 14:13

execute one query at a time

mysqli_query($con, "SET FOREIGN_KEY_CHECKS = 0;");
mysqli_query($con, "TRUNCATE table customers;");
mysqli_query($con, "TRUNCATE table customers2;");
mysqli_query($con, "SET FOREIGN_KEY_CHECKS = 1;");

or use mysqli_multi_query

mysqli_multi_query($con, "
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table customers;
TRUNCATE table customers2;
SET FOREIGN_KEY_CHECKS = 1;
");

score 0 · Answer 2 · edited Oct 05 '17 at 07:16

0

mysqli_query only allows one query at a time. If you want to use multiple queries at once, use mysqli_multi_query. Documentation: http://php.net/manual/en/mysqli.multi-query.php

In your code, you would change

mysqli_query($con, $query)

to

mysqli_multi_query($con, $query)

edited Oct 05 '17 at 07:16

Filnor

1,290
2
23
28

answered Oct 04 '17 at 14:05

KingNED

28
5

Execute SQL Query with PHP

2 Answers2

Linked