YAML to MySQL (yaml2sql) Script in PHP

23 Feb

I often find myself having to prototype code and create database tables that I restructure often. I needed a tool to quickly generate MySQL database table structures without my having to manually write the CREATE TABLE statements myself.

YAML is an excellent starting point to write up these database table prototypes, and then there is just the question of converting it into MySQL tables. Luckily, an excellent discussion forum on converting JSON to MySQL helped with this.

Note that you will need to have the PHP-Yaml library installed to get this to work.

Note that table output is optimized for CakePHP naming conventions but you can edit as necessary.

<?php
// Modified from http://www.daniweb.com/web-development/php/threads/381669

// Edit these two variables below
$tableName = "employees";
$fileName = "eg-yaml.txt";

$f = file_get_contents($fileName);
$arr = explode('---',$f);  
$global_arr = array(); // Contains each decoded json (TABLE ROW)
$global_keys = array(); // Contains columns for SQL

if(!function_exists('yaml_parse')) 
	die('Your host does not support yaml');

for($i=0; $i<count($arr); $i++)
{
    $decoded = yaml_parse ($arr[$i]);
    if (!is_array($decoded)) continue;
    $global_arr[] = $decoded;
    foreach ($decoded as $key=>$value)
            $global_keys[$key] = "$value";
}

// CREATE SQL TABLE
$query = "CREATE TABLE IF NOT EXISTS `$tableName` (
  \t`id` int(11) unsigned NOT NULL auto_increment,\n";
    foreach($global_keys as $key => $val)
  {
        $query2 = "`$key` varchar(100) NOT NULL,";
        if (((int)$val > 0) || (!(strpos($key, "_id") === false)))
                $query2 = "`$key` INT NOT NULL,";
        if (!(strpos($key, "time") === false))
                $query2 = "`$key` DATETIME NOT NULL,";
        if (!(strpos($key, "date") === false))
                $query2 = "`$key` DATE NOT NULL,";
        if ((!(strpos($key, "description") === false)) ||
                (!(strpos($key, "notes") === false)) ||
                (!(strpos($key, "comments") === false)))
                $query2 = "`$key` TEXT NOT NULL,";
        $query .= "\t$query2\n";
  }


$query .= "\t`created` DATETIME NOT NULL,
        `modified` DATETIME NOT NULL, 
        PRIMARY KEY  (`id`)
)\n";
echo ($query);

// iterate $global_arr
for($i=0; $i<count($global_arr); $i++) // this is faster than foreach
{
    if (!(is_array($global_arr[$i]))) continue;
    foreach($global_arr[$i] as $key => $value){
	    $sql[] = (is_numeric($value)) ? "`$key` = $value" : "`$key` = '" . mysql_real_escape_string($value) . "'";
    }
    $sqlclause = implode(",",$sql);
    echo("INSERT INTO `$tableName` SET $sqlclause;\n");

} // for i
?>

>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: