Skip to content

Instantly share code, notes, and snippets.

@nvg58 nvg58/ymltosql.php
Last active Aug 29, 2015

Embed
What would you like to do?
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
You can’t perform that action at this time.