Alter Every Table In A Database - How?

[JBLDW]

How would I go about altering every table in a MySQL database?

I have read several posts on this subject, regarding altering a table for foreign character sets and the like, or altering specific tables, but can find nothing for what I am trying to do, which is to use a script to alter every table in the database from MyISAM to InnoDB.

I currently have the script shown below, but cannot get it to generate any output (or error messages in the server logs, either) so, as far as I can tell, it is not working:

<?php 
include '/var/wetsocks.php';

mysql_pconnect("$s", "$u", "$p") or die(mysql_error());
mysql_select_db("$d") or die(mysql_error());

$sql = "SHOW TABLES FROM $d";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_array($result)) {

   foreach ($row as $key => $table) {   
    mysql_query("ALTER TABLE `$table` ENGINE = InnoDB");
    echo $key . " => " . $table . " successfully altered...<br />";
   }
  }

} else {
  echo "Cannot etablish connection.";
}
?>

I did wonder if I needed to change

$sql = "SHOW TABLES FROM $d";

to

$sql = "SHOW TABLES LIKE '%' FROM $d";

...but that does not appear to work, either.

Can anyone help with this, please? - I realise that I can change each table manually, but it would make far more sense if I could figure out some way of making this work through a script.

I have also tried amending the code as follows (I think that the syntax may not be correct for the above):

<?php 
include '/var/wetsocks.php';

mysql_pconnect("$s", "$u", "$p") or die(mysql_error());
mysql_select_db("$d") or die(mysql_error());

$sql = "SHOW TABLES FROM $d";
$result = mysql_query($sql);

if {

while ($row = mysql_fetch_array($result)) {

	foreach ($row as $key => $table) {   

		mysql_query("ALTER TABLE `$table` ENGINE = InnoDB");
		echo $key . " => " . $table . " successfully altered...<br />";

	} // End of foreach statement.

} // End of while statement.

} else (!$result) {

	echo "DB Error, could not list tables\n";
	echo 'MySQL Error: ' . mysql_error();
	exit;

} // End of if statement.

?>

...but that does not work, either...

[JBLDW]

I believe that I have now fixed this. - I used the code shown below, and our site is still working, only could someone here please check this for us? - I am not sure how to verify whether the alteration has been successfully implemented or not, but can confirm that all our database files now have a .frm file extension.

<?php 
include '/var/wetsocks.php';

mysql_pconnect("$s", "$u", "$p") or die(mysql_error());
mysql_select_db("$d") or die(mysql_error());

$sql = "SHOW TABLES FROM $d LIKE '%'";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {

	foreach ($row as $key => $table) {   

		mysql_query("ALTER TABLE `$table` ENGINE = InnoDB");
		echo $key . " => " . $table . " successfully altered...<br />";

	} // End of foreach statement.

} // End of while statement.

if (!$result) {

	echo "DB Error, could not list tables\n";
	echo 'MySQL Error: ' . mysql_error();
	exit;

} // End of if statement.

?>

For anyone else wanting to use this script for something you will also need your very own wetsocks.php.

The recipe is simple. - Just create a file, give it a .php file extension, and then enter something like the following (using your own database name, username, password, etc.):

<?php

$d = 'smouldering_footwear';
$u = 'cheesey';
$p = 'trainers';
$s = 'localhost';

?>