0

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.

Improve this question
2
  • Is there a maximum of two dimensions or is an infinite amount of children possible? 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. Commented Jan 10, 2014 at 23:13

2 Answers 2

Reset to default
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 ( ) ) ) )
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I'll test this. If it works for me, I'll mark as best answer. Thanks, Thomas.
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.

Improve this answer

7 Comments

how do you know OP is trying to map form input into the database?
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
@ThomasDavidPlat You are corrrect. This is not form data.
@Thomas David Plat - my bad, you are right. It was subconciously assumed on my part.
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!
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.