1

Decoded from a JSON object I would like to insert a associative array to MySQL via PHP.

I found several examples already like https://www.daniweb.com/web-development/php/threads/381669/json-to-mysql-with-php that handle one-time imports like that - also with unknown keys.

But in my case I need to do inserts (from this associative array) again and again to the same table (without droping and recreating this table first) and there may appear new keys in the array anytime which also need to be reflected as new columns (all defined as "varchar(255)") in the MySQL table.

So basically before doing the insert the existent columns need to be checked and supplemented with the new columns if needed.

I searched a while now, but didn't find anyone who did the same before and just can't believe that I'm the first one who needs this. :-)

Does anyone know an example regarding that coincidentally?

Thanks in advance!

ab-tools
  • 528
  • 5
  • 18
  • 2
    You can first check if the column exists via this method: http://stackoverflow.com/a/5943905/2136840. You can also use `SHOW COLUMNS`, although that is MySQL-specific so not portable. For creating the columns, the db user that the php script uses will need the `ALTER` privilege, and then you would create the column using `ALTER TABLE`. I'm not sure what your site/script is used for, but that would entail giving a lot of access to that user, which may not be desirable. – ChicagoRedSox Apr 18 '15 at 18:49
  • Why couldn't you alternatively use an `id|key|value` table, which properly represents such variant structured data sets? (That's just negligibly more work accessing it then). Or wouldn't it fit better to store extraneous/rare/non-standard fields just on a JSON blob alongside a standarized column scheme? (Also easy to recombine in application logic). – mario Apr 18 '15 at 18:56

2 Answers2

3

There should be an information_schema database that contains the table names, field names and the ordinal position of each field in that table.

  1. You query this database as follows:

     $sql = "select column_name, data_type from information_schema.columns
         where table_name = '$table_name' ORDER BY ordinal_position";

    Note: I also query the data_type because it can be useful in these operations.

  2. Save this information to an array. (This is your master)

  3. Iterate through your associative array (outer-loop) while iterating through each element in your master (inner-loop). Any keys in your outer that are not found in your inner are added to the table.

  4. Add any new columns to table (there are plenty of example sql statements for adding columns).

  5. Requery #1 to get a new master.

  6. Next, iterate through your master (outer-loop) while iterating through your original associative array (inner-loop). When there's a field name match add the field name and value to a new array (you could also use two strings: $fields and $values).

  7. The new array (or strings) will now have all the fields/values in the right order, and you can use this to build your insert SQL statement.

    INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
mario
  • 144,265
  • 20
  • 237
  • 291
Rex
  • 357
  • 3
  • 5
0

To Insert data from Associative Array to MySQl Please follow the below steps.

Step 1: Remove the Elements those are not the table columns.Example from your POST array please remove the Submit Buttons Value.

unset($_POST['submit']);

If you want to add any other column's values to table you may add the same to the array.Like if you want to save client IP address,Then use like 

$_POST['customer_ip'] = $_SERVER['REMOTE_ADDR'];

Step 2: $query = "INSERT INTO table_name SET ";

foreach($_POST as $column => $column_value) {
    $query .= $column." = '".$column_value."'";
}

$query contents the desired SQL Query to execute.

mysqli_query($query);
Pravat Kumar Sahoo
  • 313
  • 2
  • 9
  • Thanks for your comment, but unfortunately this does not answer my question regarding handling unknown columns while inserting new data to the same table. – ab-tools Apr 18 '15 at 19:08