CODEWITHSUNDEEP
phpopencart
user3343374
user3343374

Reputation: 19

cannot insert array into table mysql php

Hi I am trying to insert 2 arrays in a database table and getting the following:

Warning: Invalid argument supplied for foreach() in 
C:\xampp\htdocs\sport\admin\model\competition\newsletter.php on line 9

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\sport\admin\model\competition\newsletter.php on line 12

Notice: Array to string conversion in C:\xampp\htdocs\sport\admin\model\competition\newsletter.php on line 18

Notice: Array to string conversion in C:\xampp\htdocs\sport\admin\model\competition\newsletter.php on line 18Notice: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''customer_transaction'(customer_id,amount) VALUES'Array,Array'' at line 1

Error No: 1064
INSERT INTO 'customer_transaction'(customer_id,amount) VALUES'Array,Array' in C:\xampp\htdocs\sport\system\database\mysql.php on line 49

How can I fix all of that. Here is the code: This is opencart module file

$sql = "SELECT DISTINCT competition_rate, customer_id FROM " . DB_PREFIX . "customer  WHERE competition_rate NOT LIKE 0";
    $query = $this->db->query($sql);
$rates = array();
$customer_ids = array();
foreach($query->row['competition_rate'] as $result){
    $rates[] = $result * $data['name'];
}
foreach($query->row['customer_id'] as $result2){
    $customer_ids[] = $result2;
}

$sums = $rates;
$ids = $customer_ids;
$this->db->query("INSERT INTO 'customer_transaction'(customer_id,amount) VALUES'".$ids.",".$sums."'");
    }

Upvotes: 0

Views: 1872

Answers (4)

Abed Hawa
Abed Hawa

Reputation: 1362

I build the query while fetching the result then concatinate the pieces to form the final insert query:

$arr = array();
foreach($query->rows as $result){
   $arr []= '('. implode(', ', array($result['customer_id'], $result['competition_rate'] * $data['name'])) . ')';
}
$this->db->query("INSERT INTO 'customer_transaction'(customer_id,amount) VALUES " . implode(', ', $arr));

Upvotes: 0

Krish R
Krish R

Reputation: 22711

Try this, Your table name need not to be wrapped inside the ' and values() () brackets missed.

 $this->db->query("INSERT INTO `customer_transaction` (customer_id,amount)                               
   VALUES ('$ids','$sums') ");
        ..^                    ^...

instead of 

 $this->db->query("INSERT INTO 'customer_transaction'(customer_id,amount) VALUES'".$ids.",".$sums."'");

Upvotes: 0

Fabio
Fabio

Reputation: 23480

I would rather loop in the $row and then make one INSERT query at any iteration

$query = $this->db->query($sql);

foreach($query->row as $result){
   $rates = $result['competition_rate'] * $data['name'];
   $ids = $result['customer_id'];

   $this->db->query("INSERT INTO customer_transaction (customer_id,amount) VALUES ('".$ids.",".$rates."')");
}

Note that you surrounded table name with quotes but actually you shouldn't, if you want you can use backticks (`)

Upvotes: 1

Artur
Artur

Reputation: 384

You cannot concatenate string with arrays. $ids and $sums must be a strings.

Upvotes: 0

Related Questions