I found this in my collection of old random files. Fortunately this one was called normalize.php, rather than the numerous asdf7.php or so files I have. Anyway, at the time I had a huge database that was one big table and every field needed to be normalized into its own table. This was for a belongs_to/ has_many. (Although I guess since its in php, you’d refer to them as belongsTo/ hasMany. Anyway, I’m sure there are hundreds of better ways to do this, but this one is mine, written long ago and resurrected because I needed it.

$DATABASE = "db_name";
$TABLE = "big_table_name";

function exec_query($sql) {
	//echo $sql . "\n\n";
	$result = mysql_query($sql);
	if (!$result) { echo "Could not successfully run query ($sql) from DB: "
							. mysql_error(); exit; }
	return $result;
}

/* connect to database */
$link = mysql_connect('localhost', 'root', '');
if (!$link) { die('Could not connect: ' . mysql_error()); }

/* connect to db */
$db_selected = mysql_select_db($DATABASE, $link);
if (!$db_selected) { die ('Can\'t use db : ' . mysql_error()); }

/* Get fields and create table for each field name */
$result = exec_query("show fields from $TABLE");

while ($row = mysql_fetch_assoc($result)) {
	$drop_result = exec_query("DROP TABLE if exists $row[Field]");

	$create_table_query = "CREATE TABLE $row[Field]
				(id int(11) not null auto_increment
				, title varchar(256) not null
				, PRIMARY KEY(id))";
	$create_result = exec_query($create_table_query);

	// add foreign keys to main table
	$alter_result = exec_query("ALTER TABLE $TABLE
				ADD COLUMN $row[Field]_id INT(11)");

	// get all distinct values
	$distinct_values_result = exec_query("SELECT DISTINCT $row[Field]
					FROM $TABLE ORDER BY $row[Field]");

	while ($distinct_values_row = mysql_fetch_row($distinct_values_result)) {
		// add all data to newly created tables
		$insert_data_result = exec_query("INSERT INTO $row[Field]
						SET title = '$distinct_values_row[0]'");
 		$inserted_id = mysql_insert_id();

		// now update main table to reference newly created indices
		$update_big_table = "UPDATE $TABLE SET $row[Field]_id = '$inserted_id'
					WHERE $row[Field] = '$distinct_values_row[0]'";
		$update_big_table_result = exec_query($update_big_table);

		// then we can delete manually the old columns.
	}
}

Related posts:

1. Coding Horror: A Visual Explanation of SQL Joins
2. 7 reasons I switched back to PHP after 2 years on Rails – O'Reilly Ruby
3. Stupid Stupid Stupid Environmentalist
4. Field Notes Brand
5. Last Facebook Ruby Code – Grids

Leave Comment