CodeIgniter Forums
Mysql insert not working properly - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Mysql insert not working properly (/thread-53775.html)

Pages: 1 2


Mysql insert not working properly - El Forum - 08-08-2012

[eluser]ppwalks[/eluser]
I have a joining table to link products to categories, but when I insert the data it is only updationg the first value. I have purposely made a table column error to display what the statement is that is going through. I am trying to do a batch update so it will insert multiple entries. ie. product_id 1 can be with cat_id 4,6, 2:

product_id 1 -> cat_id 5
product_id 1 -> cat_is 3
product_id 1 -> cat_is 2

and so on..

This has been driving me mad today and is the last part of the puzzle..

Please someone enlighten me.

here is the insert (remeber product_id is spelt wrong to show insert)
Code:
INSERT INTO `product_category` (`prduct_id`, `category_id`) VALUES ('102,102', '1,5')

It is only inserting product_id 102 and cat_id 1 but not product_id 102 and cat_id 5, can someone help again?

Thanks


Mysql insert not working properly - El Forum - 08-08-2012

[eluser]vitoco[/eluser]
The query it's wrong :
This :
Code:
INSERT INTO `product_category` (`prduct_id`, `category_id`) VALUES ('102,102', '1,5')

Must be like this :
Code:
INSERT INTO `product_category` (`prduct_id`, `category_id`) VALUES
('102' , '102'),
('1','5')
Each record inside parenthesis ( and joined by comma to the previous one ) , and each value of that record, separated by comma also

Saludos


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]ppwalks[/eluser]
thanks for the reply but has anyone had the experience to add single quotes to an imploded array, I have tried to put the quotes around the output and through the implode procedure but nothing, when it is sent to the database through implode quotes but it leaves backslashes, The first post on here is the values generated by the implode array then sent to the insert. How can i change the output to match what "vitoco" said was wrong with the query string?

Thanks


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]vitoco[/eluser]
Adding quotes to an array when imploding

Code:
$sql  = "INSERT INTO `product_category` (`prduct_id`, `category_id`) VALUES " ;

foreach( $array as $row )
{
    $sql .= "('".implode("','" , $row )."')," ;
}

// REMOVE LAST ","

$sql = trim( $sql , ',' ) ;

Saludos


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]ppwalks[/eluser]
that doesn't work it says:

Message: implode() [function.implode]: Invalid arguments passed


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]ppwalks[/eluser]
Think may have found an easier way to get the values into db, but stumbled across another problem, I have used the batch_update method to insert the arrays but I cannot think of a way to add an identifier to the table. here is the bit of code:

Code:
$data = array(
    $product, $cat_id
  );
      $this->db->insert_batch('product_category', $data);
And this produces:

Unknown column '0' in 'field list'

INSERT INTO `product_category` (`0`, `1`, `2`) VALUES ('297','297','297'), ('1','4','6')

So how can I change the table selection, this is seriously driving me mad now!

thanks in advance....


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]vitoco[/eluser]
$this->db->insert_batch() from user_guide

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


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]ppwalks[/eluser]
i have read it about 50 times, it is the way the arrays are formatted that are the problem, so was asking for help with it.


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]vitoco[/eluser]
note that the indexes in the array are the name of the columns in the db, that's the key


Mysql insert not working properly - El Forum - 08-09-2012

[eluser]ppwalks[/eluser]
I know how do I change them or format them to be inserted? That really is the original question!