Truncate (Clean) All Tables with MySQL/PHP
I need to clear all tables when installing in a project. It would be silly if I didn't do it manually one by one, but cast the table names into an array and truncate them. For this, I pulled the tables using the information_schema and cleaned it by looping it. So my sql query is like this;
SELECT CONCAT("TRUNCATE TABLE ", table_schema, ".", TABLE_NAME, ";") FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema IN("db_name", "db_name2")
Here, when I combined the values with concat, the automatic truncate code for all my tables came out. Now when we combine it with php;
<?php
// pdo db connection operations..
$query = $db->query('SELECT CONCAT("TRUNCATE TABLE ", table_schema, ".", TABLE_NAME, ";") FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN("mvc")')->fetchAll(PDO::FETCH_ASSOC);
foreach ($query as $row){
echo $db->query(current($row))->fetch();
}
Note: If you get SQLSTATE[42000]: Syntax error or access violation error, try setting FOREIGN_KEY_CHECKS to 0 before foreach, i.e. before running queries. Well;
<?php
// pdo db connection operations..
$query = $db->query('SELECT CONCAT("TRUNCATE TABLE ", table_schema, ".", TABLE_NAME, ";") FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN("mvc")')->fetchAll(PDO::FETCH_ASSOC);
$db->query('SET FOREIGN_KEY_CHECKS=0')->fetch();
foreach ($query as $row){
echo $db->query(current($row))->fetch();
}
If you wish, you can also set the starting values of your tables with the following sql query.
ALTER TABLE `db_name`.`table_name` AUTO_INCREMENT = 1; # 1 or what time it starts
Comments
Popular Articles
Popular Tags
- #directive
- #Function
- #React
- #Class
- #centOS
- #Json
- #Text
- #API
- #URL
- #Encrypting
- #Redirect
- #Form
- #Special Characters
- #Special
- #Random
- #Generating
- #SASS
- #Version
- #Installation
- #Show
- #
- #Ajax
- #method
- #Logic For Displaying Posts
- #Key
- #DDOS
- #Default Value
- #Comment Line
- #1364 Field
- #Error
- #Abbreviation
- #QR Code Generating
- #Date
- #timeAgo
- #cURL
- #Array
- #Arrays with Key Values to String Statement
- #Short Tag
- #Vite
- #Reading Excel Files
- #Activate
- #Reading
- #Composer
- #Autoloader
- #PSR-4
- #Insert
- #Record
- #Real Time
- #Characters
- #Socket.io
There are no comments, make the firs comment