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.

Is there a maximum of two dimensions or is an infinite amount of children possible? — thpl
– thpl, Commented Jan 10, 2014 at 23:05
Have you considered json_encode? It's not as compact as it could be but may serve your purposes. — Andy Gee
– Andy Gee, Commented Jan 10, 2014 at 23:13

thpl · Accepted Answer · 2014-01-10 23:38:25Z

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
                        (
                        )

                )

        )

)

I'll test this. If it works for me, I'll mark as best answer. Thanks, Thomas.

Niels B. · Accepted Answer · 2014-01-10 23:26:15Z

0

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.

edited Jan 10, 2014 at 23:26

answered Jan 10, 2014 at 23:17

Niels B.

6,3704 gold badges30 silver badges45 bronze badges

7 Comments

thpl Over a year ago

how do you know OP is trying to map form input into the database?

Mike S. Over a year ago

Thanks @Niels. This is a back-end php webservice for a mobile app. There won't be any access to URL's. And, as luck would have it, I think I found my answer right after I posted my request. How to insert array keys as columns

Mike S. Over a year ago

@ThomasDavidPlat You are corrrect. This is not form data.

Niels B. Over a year ago

@Thomas David Plat - my bad, you are right. It was subconciously assumed on my part.

Mike S. Over a year ago

Of course, in my zeal to over-engineer, the simplest solution is often overlooked. For cols and vals: $columns = array_keys($tracks); $values = array_values($tracks); For prepared statement: $query = "INSERT INTO $table (" . implode(", ", $columns) . ") VALUES ('" . implode("', '", $values) . "')"; I output this to console and got the correct query. Cheers!

|

Collectives™ on Stack Overflow

php - reduce multidimensional array to query string

2 Answers 2

1 Comment

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related