+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Difficult DB insert question (/showthread.php?tid=3537)
Difficult DB insert question - El Forum - 10-09-2007
[eluser]Référencement Google[/eluser]
Hi,
I have a problem doing an insert.
The query:
Code:
INSERT INTO nip_word_join (word_ID, profile_ID_word)
VALUES ('100', '90'),
('105', '91'),
('108', '92'),
('110', '93'),
('100', '94'),
('106', '95'),
('100', '96'),
('118', '97'),
('102', '98')Is it possible to make that query with active record?
Another question, the datas are coming from an array, and list is long, how should I do, with a foreach or something like that to make the insert? Can somebody post a smal exemple of code?
Difficult DB insert question - El Forum - 10-09-2007
[eluser]danoph[/eluser]
foreach ($vals as $id => $val) {
$this->db->insert('table', array('word_id' => $val[0], 'profile_ID_word' => $val[1]));
}
Difficult DB insert question - El Forum - 10-09-2007
[eluser]Référencement Google[/eluser]
mmmmm.... that make a lot of successive insert query while I will have almost 200 fields to insert in the DB, I don't think it is the solution. Thanks anyway for the answer danoph.
Difficult DB insert question - El Forum - 10-09-2007
[eluser]danoph[/eluser]
there were only around 10 in that list, what about a transaction?? where are the numbers from??
Difficult DB insert question - El Forum - 10-09-2007
[eluser]Référencement Google[/eluser]
I got problem with transactions, impossible to make it work, I posted another thread about it.
Finally I made like this:
Code:
$sql = "INSERT INTO nip_word_join (word_ID, profile_ID_word)
VALUES ('".$insert['word']['sex']."', '".$insert_ID."'),
('".$insert['profile']['experience_ID']."', '".$insert_ID."')";
if ($insert['sexual_practices'] != array())
{
foreach($insert['sexual_practices'] as $value)
{
$sql .= ",('".$value."', '".$insert_ID."')";
}
}
if ($this->db->query($sql))
{
return TRUE;
}This actually work, but now I've got another problem with a similar query:
Code:
$sql = "INSERT INTO nip_word_join (word_ID, profile_ID_word)
VALUES "; // <======= HERE IS MY PROBLEM
if ($insert['places'] != array())
{
foreach($insert['places'] as $value)
{
$sql .= "('".$value."', '".$insert_ID."'),"; // <======= AND HERE TOO
}
}
if ($this->db->query($sql))
{
return TRUE;
}As you see in the code, I flag 2 places, because I need to make a dynamic contruct of the query, but I get problem because on the last result, there should not be the "," and still do not find how to do that. Some help ?
Difficult DB insert question - El Forum - 10-09-2007
[eluser]danoph[/eluser]
i do a lot of queries like that, and i only add the comma if the item is not the last item in the array. like:
Code:
foreach ($vals as $id => $val) {
$query .= 'part of a query';
if ($id != count($vals) - 1) $query .= ',';
}Difficult DB insert question - El Forum - 10-09-2007
[eluser]Référencement Google[/eluser]
Ho! yeah, that's what I was looking for, great!
Thank you Danoph
Difficult DB insert question - El Forum - 10-09-2007
[eluser]Rick Jolly[/eluser]
Maybe it's not a big deal for 200 records, but for best performance it's best to avoid conditionals and function calls within loops if possible. Something like this might be better:
Code:
$arr = $insert['places'];
$count = count($arr);
if ($count > 0)
{
$sql .= "('".$arr[0]."', '".$insert_ID."')";
for ($i = 1; $i < $count; $i++)
{
$sql .= ",('".$arr[$count]."', '".$insert_ID."')"; // note the comma placement (no trailing comma)
}
}Difficult DB insert question - El Forum - 10-09-2007
[eluser]Référencement Google[/eluser]
Yeah rick, that's again better, thanks. I came it working also with array_shift:
Code:
$sql = "INSERT INTO nip_word_join (word_ID, profile_ID_word)";
$first = array_shift($insert['places']);
$sql .= "VALUES ('".$first."', '".$insert_ID."')";
foreach($insert['places'] as $value)
{
$sql .= ",('".$value."', '".$insert_ID."')";
}
if ($this->db->query($sql))
{
return TRUE;
}