Dropping a table in mySQLi db if contains value in name

Question

I'm trying to delete one or multiple tables at once in my mySQLi db if it contains a string variable. So for example lets say I have 5 tables in my db:

Table 1 - name: db_table1_hello

Table 2 - name: db_table2_world

Table 3 - name: db_table3_hello

Table 4 - name: db_table4_world

Table 5 - name: db_table5_hello

Now I have a variable called tableString and has the value hello, in theory it would then be used to drop table 1, 3, and 5.

Not really sure on the best way to go about doing this so I thought I'd post it and get an idea from someone with a little more experience then me.

Thanks in advance guys.

EDIT:

$sql = "";

    if(mysqli_query($conn, $sql)){

        header("Location: " . $_SERVER["HTTP_REFERER"]);

    } else {  

        echo "Table deletion unsuccessfully<br> ";  

    }

You can get table names by `SHOW TABLES [FROM db_name] [LIKE 'pattern']`, and than drop tables that it found. — Evgeny Ruban, Apr 05 '18 at 08:16

kiks73 · Accepted Answer · 2018-04-05T09:26:19.303

Referring to this SO Answer, you can filter tables names from information_schema.tables table like this:

SELECT table_name FROM information_schema.tables WHERE table_name like '%hello%';

So you can create a statement englobing the previous select in this way:

DELIMITER $$
SET @s = SELECT CONCAT('DROP TABLE ',table_name,';') 
         FROM   information_schema.tables 
         WHERE table_name like '%hello%'$$
DELIMITER ;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

EDIT:

The question has changed asking for a PHP routine to drop tables. In PHP you should run the first SQL to get table names, then delete with a loop those tables:

$tableString = "hello";

$sql = "SELECT table_name FROM information_schema.tables WHERE table_name like '%$tableString%'";

$res = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_array($res)) {
    $tname = $row[0];
    $result = mysqli_query($conn, "DROP TABLE $tname");
    if (!$result) echo "Table $tname deletion unsuccessfully<br> ";  
}

Thanks for answering, I've added an edit to my post. How would I implement this into my query `$sql`? — Matt Hutch, Apr 05 '18 at 08:45

Dropping a table in mySQLi db if contains value in name

1 Answers1