CODEWITHSUNDEEP
mysqlcodeigniterinsert
Lucabro
Lucabro

Reputation: 525

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

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

Upvotes: 2

Views: 25914

Answers (2)

Filippo oretti
Filippo oretti

Reputation: 49817

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

Upvotes: 6

user2334807
user2334807

Reputation:

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'),";
}

Upvotes: 0

Related Questions