I am trying to to search in my database with an array I get from a drop-down-box, and then after search for that instance I try to insert it into my table. Heres what I have so far.
Controller:
public function insertTable() {
$text = $this->input->post('text');
$value['value'] = $this->input->post('value');
print_r($value);
$data = $this->myModel->insertTo($value,$text);
}
Model:(note table1 has an auto incremented id value which is a foreign key in table2)
public function insertTo($value,$text){
$this->db->insert('table1', array('text' => $text);
$id = $this->db->insert_id();
foreach ($value as $v) {
$query = $this->db->get_where('Table3', array('value' => $v));
$result = $query->result();
foreach ($result as $row) {
$vID = $row->vID;
}
$this->db->insert('Table2', array('ID' => $id, 'vID' => $vID));
}
}
So as you can see I first insert a value to table1 and getting the primary key id
value inside of it, then I have a foreach
loop which loops for every value in the $value
array. I query this in my database save the value and insert. When doing this though I get the following error:
Error Number: 1054
Unknown column 'Array' in 'where clause'
SELECT * FROM (`Table3`) WHERE `value` = Array
Filename: /Applications/MAMP/htdocs/CI/models/myModel.php
Line Number: 24
So my question is where have I gone wrong? How should I query a database with an array of values and then insert it into a database?
An example of this for an instance would be:
$value = 'hello','goodbye','morning';
//lets say when the array value is 0
$query = $this->db->get_where(table3, array('value' => 'hello');
//say this query returns 1
$this->db->insert('Table2', array('ID' => $id, 'vID' => '1');
And I want this to happen for every value in the array, so next time we would search by goodbye
and the id is 2
and that would get inserted in table2
To use an array in where clause you can do like this :
$ids = join(',',$array);
$sql = "SELECT * FROM table WHERE id IN ($ids)";
@Tosx's answer is correct, but to expand on this and provide a Codeigniter Active record implementation:
You could use ActiveRecord chaining to build your query:
$valueArray = array('Frank', 'Todd', 'James');
$this->db->select('vID')->from('table3')->where_in('value', $valueArray);
$query = $this->db->get();
// Produces: SELECT * FROM table3 WHERE value IN ('Frank', 'Todd', 'James')
Alternatively you could use an associative array to search multiple columns:
$array = array('name' => $name, 'title' => $title, 'status' => $status);
$this->db->where($array);
$query = $this->db->get(table3);
// Produces: SELECT * FROM table3 WHERE name = 'Joe' AND title = 'boss' AND status = 'active'
There's great documentation on CI ActiveRecord here: http://ellislab.com/codeigniter/user-guide/database/active_record.html#select