Empty multiple tables in a single run

Question

Empty multiple tables in a single run

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

4

Via PDO , I 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 make a single run to TRUNCATE to multiple tables?

php pdo php-5

asked by anonymous 17.02.2014 / 17:25

3 answers

Redirecting HTTP requests Problem with the .blur () effect of JQuery in IE8

score 1 · Answer 1

According to the TRUNCATE syntax itself in the MySQL documentation , that's not possible. What you can do is pass multiple commands of TRUNCATE into the same query, separated by semicolons:

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

Just look at the order of the tables so that they do not clear tables before which other tables depend.

score 0 · Answer 2

There is no way for you to execute a truncate for multiple tables, because there may be different triggers and different responses for each table, and you would not have the database test the commands then validate at all and return the occurrence at the end .

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

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

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

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

Since the second option is more advisable, in the first case if a failure occurs during truncate , the next commands can be ignored depending on the engine of the database in use.

score 0 · Answer 3

I recommend using DELETE instead of TRUCANTE since it is not possible to 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);
    }

}