Convert YML to MySQL
<?php | |
// Modified from http://sumanrs.wordpress.com/2012/02/23/yaml-to-mysql-yaml2sql-script-in-php | |
// Connect SQL | |
$servername = "localhost"; | |
$username = "root"; | |
$password = "root"; | |
$database = "test"; | |
// Create connection | |
$conn = mysql_connect( $servername, $username, $password ); | |
// Check connection | |
if ( $conn->connect_error ) { | |
die( "Connection failed: " . $conn->connect_error ); | |
} | |
echo "<p>Connected successfully</p>"; | |
// Make `test` the current selected db | |
$db_selected = mysql_select_db( $database, $conn ); | |
if ( !$db_selected ) { | |
die ( "Can\'t use $database : " . mysql_error() ); | |
} | |
// Edit these two variables below | |
$tableName = "table_name"; | |
$fileName = "file_name"; | |
$f = file_get_contents( $fileName ); | |
$arr = explode( '---', $f ); | |
$global_arr = array(); | |
$global_keys = array(); | |
if ( !function_exists( 'yaml_parse' ) ) | |
die( 'Your host does not support yaml' ); | |
$decoded = yaml_parse( $arr[1] ); | |
if ( !is_array( $decoded ) ) continue; | |
foreach ( $decoded as $d ) { | |
$global_arr[] = $d; | |
foreach ( $d as $key=>$value ) { | |
$global_keys[$key] = "$value"; | |
} | |
} | |
// Create SQL table if not exist | |
$query = "CREATE TABLE IF NOT EXISTS `$tableName` (\n"; | |
foreach ( $global_keys as $key => $val ) { | |
if ( $key === 'id' ) $query2 = "`$key` int(11) primary key NOT NULL auto_increment,"; | |
else $query2 = "`$key` varchar(100) CHARACTER SET utf8 NOT NULL,"; | |
$query .= "\t$query2\n"; | |
} | |
$query = substr( $query, 0, -2 ); // remove the last `,` character | |
$query .= ")\n"; | |
$result = mysql_query( $query ); | |
if ( !$result ) die( '<p>Invalid query: ' . mysql_error() . '</p>' ); | |
else echo "<p>Create $tableName if not exist successfully!</p>"; | |
for ( $i=0; $i<count( $global_arr ); $i++ ) // this is faster than foreach | |
{ | |
$sql = array(); | |
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 ); | |
$query1 = "INSERT INTO `$tableName` SET $sqlclause;\n"; | |
$query2 = "UPDATE `$tableName` SET $sqlclause WHERE `id` = $i+1;\n"; | |
$result = mysql_query( $query1 ); | |
if ( !$result ) | |
if ( !mysql_query( $query2 ) ) | |
die( '<p>Invalid query: ' . mysql_error() . '<p>' ); | |
} | |
echo "<p>Push data to $tableName done.</p>"; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment