CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Funereal
Funereal

Reputation: 673

Inserting array of Ids in Codeigniter

I'm trying to insert two rows but the id of every row is in the same array, how can I insert correctly?

Because I tried by this way but only insert me the first id.

$sql = "
    INSERT INTO cars(car, price, createdDate, createdBy) 
    VALUES (".$this->db->escape($ids).",
            ".$this->db->escape($price).",
            NOW(),
            ".$this->session->userdata('admin_id').")";             

    mysql_query($sql);
    echo ($sql);

This is what I get: 

INSERT INTO cars (car, price, createdDate, createdBy) 
VALUES ('217450,217449', '15', NOW(), 150)

In car I want to insert the price, createdDate and createdBy on the two car Ids 217450,217449.

Thank you in advance.

Upvotes: 0

Views: 896

Answers (3)

Parag Tyagi
Parag Tyagi

Reputation: 8960

$ids = "217450, 217449";
$id_explode = explode(",", $ids);

foreach($id_explode as $id)
{
    $sql = "
             INSERT INTO cars(car, price, createdDate, createdBy) 
             VALUES (".$this->db->escape($id).",
             ".$this->db->escape($price).",
             NOW(),
             ".$this->session->userdata('admin_id').")
           ";             

   mysql_query($sql);
   echo ($sql);
}


But I recommend you not to use raw SQL queries as it is vulnerable to SQL injection.
Hence, Use CI's active record:

$ids = "217450, 217449";
$id_explode = explode(",", $ids);

$insert_batch = array();
foreach($id_explode as $id)
{
    $arr = array(
              'car' => $id,
              'price' => $price,
              'createdDate' => NOW(),
              'createdBy' => $this->session->userdata('admin_id'),
             );
    $insert_batch[] = $arr;
}

$this->db->insert_batch('cars', $insert_batch);


Documentation:

https://ellislab.com/codeigniter/user-guide/database/active_record.html

Upvotes: 1

codeGig
codeGig

Reputation: 1064

If you use ids as array like $ids = array('217450','217449');

$sql = "
INSERT INTO cars(car, price, createdDate, createdBy) 
VALUES ";
foreach($ids as $id){

$sql .=(".$this->db->escape($id).",
        ".$this->db->escape($price).",
        NOW(),
        ".$this->session->userdata('admin_id')."),";             
}
mysql_query($sql);
echo ($sql);

Now it will create query

 INSERT INTO cars (car, price, createdDate, createdBy)
VALUES ('217450', '15', NOW(), 150),('217450', '15', NOW(), 150);

Upvotes: 1

Ganesh Jadhav
Ganesh Jadhav

Reputation: 2848

Use PHP function explode:

$ids= explode(',', $ids);
foreach($ids as $id)
{
    $sql = "INSERT INTO cars(car, price, createdDate, createdBy) 
        VALUES (" . $this->db->escape($id) . ", " . $this->db->escape($price)
        . ", NOW(), " . $this->session->userdata('admin_id') . ")";
    mysql_query($sql);
}

Upvotes: 1

Related Questions