Skip to content

Instantly share code, notes, and snippets.

@cristiroma
Created November 7, 2014 15:24
Show Gist options
  • Save cristiroma/bf7930da3a99d96aa9d8 to your computer and use it in GitHub Desktop.
Save cristiroma/bf7930da3a99d96aa9d8 to your computer and use it in GitHub Desktop.
PHP script to clean database tables. Add to $preserve the ones you want to keep. Also drop views
<?php
$conn = new mysqli('localhost', 'root', 'root', 'database');
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$conn->autocommit(FALSE);
$preserve = array(
'table_1',
'table_2',
);
if ($cur = $conn->query('SHOW TABLES')) {
$conn->query('SET FOREIGN_KEY_CHECKS = 0');
while ($obj = $cur->fetch_array()) {
$table = $obj[0];
if (!in_array($table, $preserve)) {
$conn->query("DROP VIEW IF EXISTS $table");
$sql = "DELETE FROM $table";
if (!$conn->query($sql)) {
echo "Failed query: " . $sql . ' ' . $conn->error . "\n";
}
$sql = "ALTER TABLE $table AUTO_INCREMENT=1";
if (!$conn->query($sql)) {
echo "Failed query: " . $sql . ' ' . $conn->error . "\n";
}
}
}
$conn->query('SET FOREIGN_KEY_CHECKS = 1');
$conn->commit();
}
$conn->close();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment