Rachid Rachid - 2 months ago 14
SQL Question

Smart way to insert in php

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"....)");

Answer

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();
}
Comments