PHP & MySQL: Truncate multiple tables
18,439
Solution 1
thanks for the help guys! here is my answer,
# truncate data from all table
# $sql = "SHOW TABLES IN 1hundred_2011";
# or,
$sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE '".DB_NAME."'";
# use the instantiated db connection object from the init.php, to process the query
$tables = $connection -> fetch_all($sql);
//print_r($tables);
foreach($tables as $table)
{
//echo $table['TABLE_NAME'].'<br/>';
# truncate data from this table
# $sql = "TRUNCATE TABLE `developer_configurations_cms`";
# use the instantiated db connection object from the init.php, to process the query
# $result = $connection -> query($sql);
# truncate data from this table
$sql = "TRUNCATE TABLE `".$table['TABLE_NAME']."`";
# use the instantiated db connection object from the init.php, to process the query
$result = $connection -> query($sql);
}
Solution 2
By the MySQL Reference Manual
http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html
You can only delete one table at a time using TRUNCATE.
You could try executing multiple queries in one PHP query by using a ";" delimiter between them.
Comments
-
Run almost 2 years
I tried to truncate a table but why is it not working? must something wrong in the database query?
$sql = "TRUNCATE TABLE `table_name`"; $result = $connection -> query($sql);
Ideally, I want to truncate all tables in one go - is it possible?
if you wonder what is inside the class that I use to make the database queries, here is it,
#connects the database and handling the result class __database { protected $connection = null; protected $error = null; #make a connection public function __construct($hostname,$username,$password,$database) { $this -> connection = new mysqli($hostname,$username,$password,$database); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } } ... #performs a query on the database public function query($query) { $result = $this -> connection -> query($query); if($result) { return $result; } else { $this -> error = $this -> connection -> error; return false; } } #display error public function get_error() { return $this -> error; } #closes the database connection when object is destroyed. public function __destruct() { $this -> connection -> close(); } }
thanks.
edit:
below is how I call the db object,
# the host used to access DB define('DB_HOST', 'localhost'); # the username used to access DB define('DB_USER', 'root'); # the password for the username define('DB_PASS', 'xxx'); # the name of your databse define('DB_NAME', 'xxx'); $connection = new __database(DB_HOST,DB_USER,DB_PASS,DB_NAME);