Smart way to insert in php

Question

I'm using codeigniter, and I have a large table with a lot of columns that I want to insert large amount of data into. Is there a better approach than writing the name if each column, and then the value that will be inserted in each column. For example, a php function that gets the table columns and put them in an array, and have another array that have the values pointing to each column...

Example with codeigniter:

$data = array('column_name1'=>'value1', 'column_name2' =>'value2'....);
$this->db->insert('table_name', $data);

Regular php

mysql_query("INSERT INTO table_name (column_name1, column_name2) VALUES ("Value1"....)");

Just how many columns are you talking about? It would probably have been easier to just type them out than to type out this question. — user229044, Nov 21 '13 at 13:25
Dynamic prepared statements: http://stackoverflow.com/a/13333344/1322435 — Jonathan Spiller, Nov 21 '13 at 13:47

score 1 · Answer 1 · answered Nov 21 '13 at 14:31

Using your example of data:

$data = array('column_name1'=>'value1', 'column_name2' =>'value2'....);
$this->db->insert('table_name', $data);

You could build your query like this:

private function buildInsertSql($data, $table) {
    $columns = "";  
    $holders = "";  
    foreach ($data as $column => $value) {  
       $columns .= ($columns == "") ? "" : ", ";  
       $columns .= $column;  
       $holders .= ($holders == "") ? "" : ", ";  
       $holders .= ":$column";  
    }  
    $sql = "INSERT INTO $table ($columns) VALUES ($holders)";  
    return $sql; 
}

You should then run it using PDO, since mysql_query is deprecated. You can read up on how PDO binds values to 'holders', etc. Also, don't forget other forms of sanitization, like white-listing your column names etc.

ilpaijin · Answer 2 · 2013-11-21T13:42:57.770

Maybe you're looking for something like this

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name' ,
      'date' => 'My date'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name' ,
      'date' => 'Another date'
   )
);

$this->db->insert_batch('mytable', $data); 

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')

Source: http://ellislab.com/codeigniter/user-guide/database/active_record.html

Or this just could serve as a base to be extended then

------------EDIT AFTER 1st COMMENT-------------

So to get yourtablename columns you can apply something like this with Active Record Class methods in CI

SELECT column_name
FROM information_schema.columns
WHERE table_name='Yourtablename'

This way you can create columns array as you described and proceed with it

Thank you for your reply. However, I'm trying to avoid typing the name of the columns because I have many tables with about 30 to 40 columns — Rachid, Nov 21 '13 at 13:33

score 0 · Accepted Answer · edited Sep 18 '16 at 10:41

Inside model constructor I will instantiate a property and I'll store there my columns names. I'll reuse that array many times so that's why it's in the constructor

$this->table_col = $this->db->list_fields('table_name');

Inside the insert method, with an array like this as parameter

$data = array(
   array('column_name1'=>'value1', 'column_name2' =>'value2'....),
   array('column_name1'=>'value1', 'column_name2' =>'value2'....),
   array('column_name1'=>'value1', 'column_name2' =>'value2'....)
);

I will filter columns removing each invalid field. You can wrap it in a function since you'll use it in other places (update, insert_single, where clauses)

 foreach($data as &$single)
 {
     $invalid_keys = array_diff(array_keys($single), $this->table_col);

     if(!empty($invalid_keys))
     {
        $single = array_diff_key($single,array_flip($invalid_keys));
     }
 }

If I want to be sure that each query went well, I'll use transactions. Otherwise just insert_batch

$this->db->trans_begin();

$this->db->insert_batch('table_name', $data); 

if ($this->db->trans_status() === FALSE)
{
    $this->db->trans_rollback();
}
else
{
    $this->db->trans_commit();
}

score 0 · Answer 4 · answered Nov 22 '13 at 02:57

0

$data = array('column_name1'=>'value1', 'column_name2' =>'value2'....);

foreach($data as $key as $value){
 $sql = "INSERT INTO $table ($key) VALUES ($value)";  
    return $sql; 
}

...I hope i give you idea :)

answered Nov 22 '13 at 02:57

Denmark

538
2
8
26

Smart way to insert in php

4 Answers4