1

I have a CSV file from which I extract the headers, which are actual fields in my MySQL tables using PHP's explode() function. I don't know how many fields there will be in each CSV file, but I need to insert data dynamically in those fields.

For example:

array(
    (int) 0 => 'admission_no',
    (int) 1 => 'first_name',
    (int) 2 => 'middle_name',
    (int) 3 => 'last_name',
    (int) 4 => 'gender',
    (int) 5 => 'date_of_birth',
    (int) 6 => 'join_date',
    (int) 7 => 'form_id',
    (int) 8 => 'stream_id'
)

The insert statement like INSERT INTO tbl_name (...) VALUES (...). I want the column names dynamically loaded from the array above, since I am not sure how many they will be. Can someone point me in the right direction?

Oldskool
  • 34,211
  • 7
  • 53
  • 66
peter
  • 31
  • 1
  • 3
  • 1
    Um... are you dynamically creating the table too? If you don't know what columns, how do you know they will or will not exist in a table? If you do know all the columns of the table simply list them and leave Null/empty those values which are not defined in the file. – xQbert Dec 26 '14 at 19:04
  • i know what columns exist in the table and the csv file headers will have only existing columns.thing is i intend the function to be app-wide and used to insert to different tables.what i dont want is to hard code the columns in the sql statement since the columns will be different for each table – peter Dec 26 '14 at 19:23
  • So, you need to implode the array into a comma separated string that you dynamically insert into the select statement? http://stackoverflow.com/questions/9895450/how-to-transform-array-to-comma-separated-words-string – xQbert Dec 26 '14 at 19:26
  • yeah something like that – peter Dec 26 '14 at 19:32
  • let me try the implode method and see what happens.thanks for the insight – peter Dec 26 '14 at 19:35
  • No problem. The only thing that I don't like about this approach is the SQL is going to be dynamic which means the data coming in needs to be strongly sanitized before updating the system with it. – xQbert Dec 26 '14 at 19:36
  • yeah i'll deal with that – peter Dec 26 '14 at 19:39
  • And this is in a CakePHP app? (Since you used the `cakephp` tag). You can always dynamically set the fields to CakePHP's `save()` method, just make sure the data array contains the data in the right order. – Oldskool Dec 26 '14 at 20:26
  • More info and we can write it for you – Rymn Dec 27 '14 at 19:13
  • ok.above is the first array.suppose another time the method is called from a controller whose model has only 3 fields and i need to load them using $this->Model->query("INSERT INTO tbl_name($fields) VALUES()"); – peter Dec 27 '14 at 20:48

1 Answers1

0

This may meet your requirements. try this....

      $query = "INSERT INTO `mytable` ( ".
      mysql_real_escape_string(implode(array_keys(' , ', $explodedColumnName))).
      ") VALUES ( '".
      mysql_real_escape_string(implode("' , '", $userData)).
      "' )";
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103