codeigniter INSERT query is correct and executed but no insert is performed

Question

relevant code:

$sql = "INSERT INTO rel_attivita_corsi(id_attivita,id_corso) VALUES";
foreach($elements as $element) {
    $sql .= "('".$element."','1'),";
}
$sql = substr($sql,0,-1);

if (!$this->db->query($sql)) {
    echo "FALSE";
}
else {
    echo "TRUE";
}
echo $this->db->last_query();

The table structure:

rel_attivita_corsi
-----------------------------
ID            (int)   primary
id_attivita   (int)
id_corso      (int)

I'm using codeigniter, as you can see, the last raw return the right query, but the table in the db remain empty... running the query returned in phpmyadmin everything works correctly.

Any ideas?

UPDATE 1 - Using active records:

$dati = array();

foreach($elements as $element){
    $dati[] = array('id_attivita' => $element, 'id_corso' => 1);
}

if (!$this->db->insert_batch("rel_attivita_corsi",$dati)) {
    echo "FALSE";
}
else {
    echo "TRUE";
}

echo $this->db->last_query();

no success, last query printed correctly but no insert happen

UPDATE 2 - Using active records and no foreach:

$this->db->insert_batch("rel_attivita_corsi",array(array('id_attivita' => 7,'id_corso' => 1),array('id_attivita' => 9,'id_corso' => 1)));

no success....

i've substituted fake value with real now, and the $elements array is:

Array
(
    [0] => 7
    [1] => 9
)

UPDATE 3 Problem solved... there was another query after the code that deleted every record insert into the table, so the table was always empty

If you heve doubt on the flow of your code try use profiling

Can you show what $sql contains? Side question: are you escaping/sanitizing $elements at some point? — Pekka
– Pekka, Commented May 20, 2013 at 10:13
Why not use codeigniters active record class to build your sql statement? It would look a lot cleaner, and also sanitize your values before inserting them (which you are currently not doing). ellislab.com/codeigniter/user-guide/database/active_record.html — Jeemusu
– Jeemusu, Commented May 20, 2013 at 10:17
Can you update you question with print_r($element);, so that we can tell you if you are doing it right!!! — Elavarasan Muthuvalavan - Lee
– Elavarasan Muthuvalavan - Lee, Commented May 20, 2013 at 10:28

Filippo oretti · Accepted Answer · 2013-05-20 10:29:28Z

i think you are looping wrong:

$sql .= "('".$element."','1'),";

this produces (value,1), (value, 1) , (value, 1)

while you need

(value,value,value)

ma che te lo dico a fare poi :P

so your query is:

INSERT INTO table(ID,id_activity,id_cont) VALUES (somenthin,1), (somenthing,1) etc ..

and you need instead

INSERT INTO table(ID,id_activity,id_cont) VALUES (value,value,value) ;

you can try this:

$sql = "INSERT INTO table(id_activity,id_cont) VALUES (".implode(',',$elements)."); ";

if (!$this->db->query($sql)) {
    echo "FALSE";
}
else {
    echo "TRUE";
}
echo $this->db->last_query();

IN CASE you are trying making an INSERT BATCH check the Codeigniter documentation it says:

 $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')

user2334807 · Accepted Answer · 2013-05-20 10:18:44Z

0

replace the code :

$sql = "INSERT INTO table(id_activity,id_cont) VALUES";
foreach($elements as $element) {
    $sql .= "('".$element."','1'),";
}

With the code:

$sql = "INSERT INTO table(ID,id_activity,id_cont) VALUES";
foreach($elements as $element) {
    $sql .= " ('','".$element."','1'),";
}

answered May 20, 2013 at 10:18

user2334807

Collectives™ on Stack Overflow

codeigniter INSERT query is correct and executed but no insert is performed

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related