[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?
[eluser]danoph[/eluser]
foreach ($vals as $id => $val) {
$this->db->insert('table', array('word_id' => $val[0], 'profile_ID_word' => $val[1]));
}
[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.
[eluser]danoph[/eluser]
there were only around 10 in that list, what about a transaction?? where are the numbers from??
[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 ?
[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 .= ',';
}
[eluser]Référencement Google[/eluser]
Ho! yeah, that's what I was looking for, great!
Thank you Danoph
[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)
}
}
[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;
}
|
