PHP Snippets

PHP scripts for converting InnoDB to MyISAM database engine

Danreb › January 27th, 2012
Tags:

This PHP CODE snippets will change your database engine from  InnoDB to MyISAM, this is useful when you need to save time manually changing all the tables from InnoDB to MyISAM, for example when a CMS populates a database with tables that use InnoDB but you cannot run on your web host because InnoDB is disabled and MyISAM is your remaining options.

Commonly on a shared hosting environments, web host disabled InnoDB because it requires more disk space and eats more Server resources and Memory than MyISAM. To use this scripts you need to change the variables:

$dbServer // Database server, commonly use value is localhost

$dbUser // Database User, the one with priveleges to your database

$dbPass // Password of the Database User

$dbName // The database you want to convert from InnoDB to MyISAM

Code

<?php
// display error is On for us to be notified if there's something wrong
ini_set('display_errors', 'On');
error_reporting(E_ALL);

// Config, change this variables
$dbServer = "localhost";
$dbUser = "root";
$dbPass = " ";
$dbName = "testdb";

// Set a connection for our database
$link = mysql_connect($dbServer, $dbUser, $dbPass)
or die("unable to connect to msql server: " . mysql_error());

// Select our database
mysql_select_db($dbName, $link)
or die("unable to select database 'db': " . mysql_error());

$result = mysql_query("show tables");
if (!$result) {
  die('query failed: ');
}

// Use while loop to convert all tables to MyISAM
while ($row = mysql_fetch_array($result)){
// Command Reference: ALTER TABLE tableName ENGINE=MyISAM
mysql_query("ALTER TABLE ".$row[0]." ENGINE=MyISAM; ");
}

?>