Empty multiple tables in a single run

Question

Via PDO, can do the TRUNCATE ^(English) of a table as follows:

// Matriz de definições
$dbcon_new = array(
  "host"     => "localhost",
  "dbname"   => "bubu",
  "username" => "super_dragon",
  "password" => "balls"
);

// estabelece a ligação
$dbh = new PDO(
  'mysql:host='.$dbcon_new['host'].';dbname='.$dbcon_new['dbname'].';',
  $dbcon_new['username'],
  $dbcon_new['password'],
  array(
    PDO::ATTR_PERSISTENT               => false,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    PDO::ATTR_ERRMODE                  => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND       => "SET NAMES utf8"
  )
);

// esvazia a tabela xpto
$dbh->exec("TRUNCATE TABLE xpto");

Question

How can I in a single execution TRUNCATE to multiple tables?

Answer 1

According to the syntax of TRUNCATE in mysql documentation, this is not possible. What you can do is pass several commands of TRUNCATE in the same query, separated by a semicolon:

TRUNCATE tabela1; TRUNCATE tabela2; TRUNCATE tabela3; --etc...

Just look at the order of the tables not to clear before tables that other tables depend on.

Answer 2

There’s no way you can run a truncate for several tables, because there can be different triggers and different responses for each table and there would be no way the database would test the commands then validate in all and return the occurred at the end.

What you can do is have several commands truncate in the same exec:

$dbh->exec("TRUNCATE TABLE xpto; TRUNCATE TABLE xpty;");

Or run a loop to run multiple times until the operation is complete:

$arr = array('xpto', 'xpty');
foreach ($arr as $table) {
    $dbh->exec("TRUNCATE TABLE $table;");
}

The second option is more advisable, because in the first case a failure occurs during the truncate, the next commands may be ignored depending on the database engine in use.

Answer 3

I recommend using the DELETEinstead of TRUCANTE for it is not possible to give TRUNCATE in tables that have foreign keys(FK)

pass an array($tables).

function deleteTabela($tabelas){

    $db = new PDO();

    forach($tabelas as $item){
        $item = limparEntrada($item);
        $sql  = "DELETE FROM $item";
        $db->query($sql);
    }

}