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));}


    ?>

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
– aynber, Commented Oct 4, 2017 at 14:04
You said you run into issues. "issues" as? any error messages or something? — sequel
– sequel, Commented Oct 4, 2017 at 14:05
try executing one query per call to mysqli_query(). not 4 at once — Ivo P
– Ivo P, Commented Oct 4, 2017 at 14:05

AZinkey · Accepted Answer · 2017-10-04 14:13:11Z

2

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;
");

answered Oct 4, 2017 at 14:13

AZinkey

5,3595 gold badges30 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Filnor · Accepted Answer · 2017-10-05 07:16:29Z

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 5, 2017 at 7:16

Filnor

1,2882 gold badges23 silver badges28 bronze badges

answered Oct 4, 2017 at 14:05

KingNED

285 bronze badges

Collectives™ on Stack Overflow

Execute SQL Query with PHP

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related