php - reduce multidimensional array to query string

Question

I've been scouring SO this afternoon for a viable solution to my use case. I haven't found it.

Use Case: transform array into a format such that, in a PDO insert statement, the array keys are the columns, and the array values are the SQL values. If this request duplicates others, I apologize. I haven't found one that works for me. In my example the parent array is Array[0], which gets inserted into one table, Array[1] are the children. They get iterated over and inserted into another table with fk_user_id as the foreign key.

Example insert: $sth = $dbh->prepare("INSERT INTO $table ($colData) VALUES ($valData)");

So, columns would be (key1, key2) and VALUES would be (:value1, :value2) and so on. I'm having all sorts of issues with multidimensions and extracting child nodes, etc, etc.

Sample array:

[INFO] Array
(
    [0] => Array
        (
            [last_visit] => 1389393273.19
            [first_visit] => 1389313338.69
            [this_visit] => 1389393265.75
            [latitude] => 37.7858352661
            [longitude] => -122.406417847
            [opted_out] => 0
            [opted_in] => 0
            [user_id] => 1
            [locale] => en
            [device_maker] => unknown
            [device_model] => Simulator
            [os_platform] => iPhone OS
            [os_version] => 7.0.3
            [modified] => 1389393273.19
            [model_type] => tracker
        )
    [1] => Array
        (
            [0] => Array
                (
                    [view_count] => 1
                    [visit_timestamp] => 1389393265.63
                    [page_viewed] => home
                    [page_id] => 320
                    [fk_user_id] => 1
                    [model_type] => page
                )
            [1] => Array
                (
                    [view_count] => 2
                    [visit_timestamp] => 1389393265.64
                    [page_viewed] => contactView
                    [page_id] => 321
                    [fk_user_id] => 1
                    [model_type] => page
                )

        )
)

Solutions I've tried Remove parent - keep children, Remove key from associative array, Iterating over an array, Flatten array and others. I may be so deep in the code that I lost perspective of my data. It's been awhile since I worked this much with PHP, so my skills are in need of brushing up. Your help is greatly appreciated.

Answer 1

I've tied together a possible solution for you that is also considering walking trough multidimensional arrays (unlimited amount of children).

The solution is a function based on recursion and returns an array with the cols (that need to be inserted to the db), the rows and an array containing the children.

You can loop through the output array or statically make use of certain elements of it. Take a look at it.

<?php

$arr = array(
  'key1' => 'val1',
  'key2' => 'val2',
  'key3' => 'val3',
  'key4' => array(
    'subkey1' => 'subval1',
    'subkey2' => 'subval2',    
  )
);

function walkArray($input)
{
    // Define our output array
    $output = array(
      'keys' => '',
      'vals' => '',
      'children' => array(),
    );

    // We're looping trough the input array
    foreach($input AS $key => $value)
    {
      // If the current value is an array we reached the next dimension
      if(is_array($value))
      {
        // So we call walkArray() recursively with our current value
        // and assign the returned array to a new element in our $output's 'children' key 
        $output['children'][] = walkArray($value);
      }
      else
      {
        // We'll concatenate our keys and values...
        $output['keys'] .= $key . ', '; 
        $output['vals'] .= ':' . $value .', ';
      }
    }

    // And get rid of the trailing commas
    $output['keys'] = rtrim($output['keys'], ', ');
    $output['vals'] = rtrim($output['vals'], ', ');   

    // Eventually we return our output array
    return $output; 
}

?>

<pre>
  <?php
    print_r(walkArray($arr));
  ?>
</pre>

The output would be:

Array
(
    [keys] => key1, key2, key3
    [vals] => :val1, :val2, :val3
    [children] => Array
        (
            [0] => Array
                (
                    [keys] => subkey1, subkey2
                    [vals] => :subval1, :subval2
                    [children] => Array
                        (
                        )

                )

        )

)    

Answer 2

Dynamically mapping form input to SQL columns in the way intended here is a terrible idea from a security standpoint. Anyone will be able to dump any data into any column by constructing a matching POST request.

Furthermore, by interpolating form values directly into your SQL string you are vulnerable to SQL injection. Anyone can submit a fake POST request and execute arbitrary SQL that will expose or damage any table in your database.

With that warning being given, what you want is to iterate over the keys in the first record using PHP's foreach

http://www.php.net/manual/en/control-structures.foreach.php

foreach ($main_record as $column_name => $column_value) {
   ...
}

Inside the loop, you will have to build up two strings in each iteration. The first one contains the column names (the comma seperated list after the table name). The second one contains the values.

After that, you will have to get the ID of the last inserted record, PDO has a function for that. Then you'll have to repeat this technique for each of the associated records.

If you want a better solution, consider using a framework like Laravel, which includes an Object-Relational Mapper library called Eloquent ORM. It will make these kind of operations simpler and safer to program.