PHP loop to alter multiple mysql tables

Question

I know that is impossible to alter multiple tables at once, so I'm building my own PHP script that will do the job for me.

Here, I got all the tables that I want to alter:

<?php

include('conf/conn.php');

$result = mysqli_query($conn, "SHOW TABLES FROM queue LIKE '%room_%'");

while($table = mysqli_fetch_array($result)) {
    echo($table[0] . "<br>");
}

?>

Now, I want to create a loop that executes a MySQL query for each table and echo a status every time each query is executed. Something like this:

<?php

foreach ($table[0] as $tableToAlter) {
    $result = mysqli_query('ALTER TABLE $tableToAlter AUTO_INCREMENT = 1001');
    if (!$result) {
        echo 'Alter failded.';
    } else {
        echo 'Alter successful'
    }
}

?>

As you can see, I'm a newbie in PHP and I am stuck in this last part. I don't know if I need to make an array to string conversion before the loop. If someone can please tell me how to make this works in the right way I will really appreciate. Thanks.

Instead of echoing `$table[0]` put it into an array (like `$tablesToAlter[] = $table[0]`) and then in the second part loop like `foreach ($tablesToAlert as $tableToAlter)`. Of course `$tablesToAlter` needs to be declared beforehand (`$tablesToAlter = []`). — Federico klez Culloca, Oct 08 '18 at 15:35
@FedericoklezCulloca or skip the assignment all together and use `$table[0]` within the query, executing the query within the `while` loop instead of the `foreach`. — Alexander Holman, Oct 08 '18 at 15:36
Possible duplicate of [What is the difference between single-quoted and double-quoted strings in PHP?](https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php) — Nigel Ren, Oct 08 '18 at 15:44
As your alter table is in single quotes - `$tableToAlter` will not be substituted in. — Nigel Ren, Oct 08 '18 at 15:45

score 1 · Accepted Answer · answered Oct 08 '18 at 15:53

As suggested in the comments, you could call your query within the while loop using the variable $table[0].

<?php

include('conf/conn.php');

$result = mysqli_query($conn, "SHOW TABLES FROM queue LIKE '%room_%'");

while($table = mysqli_fetch_array($result)) {
    echo("Altering {$table[0]}<br>");
    if (!mysqli_query("ALTER TABLE {$table[0]} AUTO_INCREMENT = 1001");) {
        echo 'Alter failded.';
    } else {
        echo 'Alter successful'
    }
    echo "<br>";
}

?>

As a side note, seeing as we are injecting unknown input into our query I would suggest in general using prepared statements, an example of which can be found here: How can I prevent SQL injection in PHP? (top answer)

PHP loop to alter multiple mysql tables

1 Answers1