There may be another way to do this out there, but when I needed it, I couldn’t find an easy free way to do it, so I made one.
You first need to create your new tables in your new MySQL DB and make sure you have allow_url_fopen on in htaccess or in your PHP config. I use PDO so you may have to modify the code if you don’t.
Step 1: The CF file
I made one of these for each table I wanted to move. Map the jSON key to the new column names in your new DB.
<cfquery datasource="yourDSN" name="qName"> select * from table order by id </cfquery> [ <cfoutput query="qName"> { "id":"#URLEncodedFormat(id)#", "col1":"#URLEncodedFormat(col1)#", "col2":"#URLEncodedFormat(col2)#" } <cfif currentrow neq recordcount>,</cfif> </cfoutput> ]
Step 2: The PHP file
You can run this in your browser using https://yourserver/import.php?records=yourCFfilenameonyourremoteserver
function get_web_page($url) { $options = array( 'http' => array( 'user_agent' => 'spider', // who am i 'max_redirects' => 30, // stop after 10 redirects 'timeout' => 220, // timeout on response ) ); $context = stream_context_create( $options ); $page = @file_get_contents( $url, false, $context ); $result = array( ); if ( $page != false ) $result['content'] = $page; else if ( !isset( $http_response_header ) ) return null; // Bad url, timeout // Save the header $result['header'] = $http_response_header; // Get the *last* HTTP status code $nLines = count( $http_response_header ); for ( $i = $nLines-1; $i >= 0; $i-- ) { $line = $http_response_header[$i]; if ( strncasecmp( "HTTP", $line, 4 ) == 0 ) { $response = explode( ' ', $line ); $result['http_code'] = $response[1]; break; } } return $result; } error_reporting(E_ALL); ini_set("display_errors", 1); ini_set('memory_limit', '128M'); require_once('db.php'); $records = $_GET['records']; $inserts = get_web_page("https://pathtoyourserverwithcfandsql/".$records.".cfm"); print_r($inserts); if ($inserts['http_code'] == 200) { $inserts = $inserts['content']; } if (json_decode($inserts,true) == true) { $insertsR = json_decode($inserts,true); foreach ($insertsR as $items) { $cols = array(); $vals = array(); foreach ($items as $key => $value) { $cols[] = $key; $vals[] = urldecode($value); } $colsI = implode(',',$cols); $questions = ''; $valsC = count($vals); for ($i=1; $i <= $valsC; $i++) { $questions .= ($i < $valsC) ? '?,' : '?'; } print_r($cols); print_r($vals); echo "insert ignore into $records (".implode(',',$cols).") values ($questions)"; print_r($vals); $statement = $db->prepare("insert ignore into $records (".implode(',',$cols).") values ($questions)"); $statement->execute($vals); } } else { echo 'no bueno'; }
Be the first to leave a comment. Don’t be shy.