Transforming Raw Data From Simple Arrays to SQL Statements

As developers, we often find ourselves throwing around a few scripts as we come up with some ideas. Along the way, our rough ideas develop into something we want to expand on and make reusable. To store our sample data, we use some sorts of data structures to keep things simple. One of the data structures commonly used to hold related data is an array.

When the application grows to a reasonably large scale, it's hard to avoid using some sort of a database to store your data in. Here we'll go through how to transform what you have in your simple arrays to SQL statements for use in say MySQL/MariaDB.

1. Prepare your data for transition

Often, your variable names may not be what you plan to use long term. If that's the case for you, device meaningful names for your tables in an array, else, just use the names that match the variable names you already have.

        

$tablenames = [ "social_media_sites", "sharing_links" ];

Match the table names above in some sort of sample data from which you will derive the structure of your tables. This could be passed into the function (we write this later) as raw data but it just plays nice to have it blocked out upfront here.

        

$tablecontents = [ $social_media_sites, $sharing_links ];

2. Write your main data transformer (function)

Since we are dealing with arrays and SQL that needs some kind of stopping point in its statement execution, let's first make a little helper function for finding out whether we are on the last item during iteration.

        

// Helper function to check for last element in an array for proper SQL statement termination. function last($element, $array) { return $element == array_values(array_slice($array, -1))[0]; }

The real deal!

From the two pieces of information we have up there on tables (names and content), our function signature needs to have parameters that at least accommodate the two. Let's call our function 'construct_sql' with 2 arguments (arrays of names and contents).

        

// SQL generation function function construct_sql(array $tablenames, array $items) { /** Function body goes here */ }

Within the function, we need a loop to go through the data and appropriate tables. Let's go with a for loop.

        

for ($i = 0; $i < count($items); $i++) { // Loop body }

Inside our loop, we need to do 3 critical things to drive our SQL creation, i.e. determine table fields, drop any existing table, create the table.

        

// table fields $fields = $items[$i][0]; /* Just the first item has all the necessary info */ // creating the table $csql = "DROP TABLE IF EXISTS `". $tablenames[$i] . "`;"; $csql .= "CREATE TABLE IF NOT EXISTS `". $tablenames[$i] . "` (";

Next, we incrementally build out the table structure following our chosen SQL dialect (MySQL in our case here) based on the fields we extracted from above. A for each loop comes in hand here.

        

foreach ($fields as $k => $v) { $csql .= "  `" . $k . "` "; // rest of the structure... }

Finally, insert the data into the tables accordingly. Notice how our little helper function, last(), came in handy.

        

// inserting data $csql .= "INSERT INTO `$tablenames[$i]` ("; foreach ($fields as $k => $v) { $csql .= "`" . $k . "`"; $csql .= !last($v, $fields) ? ", " : ""; } $csql .= ") VALUES "; // table values foreach ($items[$i] as $p) { $csql .= '("' . join('", "', $p); $csql .= '")'; $csql .= last($p, $items[$i]) ? ";" : ","; }


Final code

To make our life easy, I prepared the final code and put it into a gist on Github. Get it from there; ready to test and use. Arrays to SQL statements