SQL does not take all the passed variables from php

Question

I have these sql statements in a php function and I have passed all the needed variables. I have 2 insert statements in the query. They are inserting data into the same table, the first insert is for main service, and the second one if for an add-on service in my website.

SQL somehow takes the value of $userid and $item_label and the first insert works just fine. The second insert won't work unless I changed $userid and $item_label into static values, like '123','122'. (so basically, if I leave them as :userid, :item_label, SQL won't insert the values into the table)

But other overlapping variables like $filename, both insert statement used do not cause any of the problems.

And $userid is not the targeted table's index or anything. So, please help. and here is the code

function add_writing_editing_to_table($userid, $item_label, $basic_writing_service_id, $basic_writing_price, $editing_service_subcategory, $editing_service_id, $editing_serivce_price, $filename, $new_filename){

      global $db;
      $query = "INSERT INTO op_cart
                (service_category, userid, item_label, service_subcategory, service_id, is_addon, unit, total_price, filename, new_filename, checkout)
        VALUES
    ('writing', :userid, :item_label, 'basic_writing', :basic_writing_service_id, '0', '1', :basic_writing_price, :filename, :new_filename, '0'),
    ('editing', :userid, :item_label,  :editing_service_subcategory, :editing_service_id, '1', '1', :editing_serivce_price, :filename, :new_filename, '0');";
      $statement = $db->prepare($query);
      $statement->bindValue(':userid', $userid);
      $statement->bindValue(':item_label', $item_label);
      $statement->bindValue(':basic_writing_service_id', $basic_writing_service_id);
      $statement->bindValue(':basic_writing_price', $basic_writing_price);
      $statement->bindValue(':filename', $filename);
      $statement->bindValue(':new_filename', $new_filename);          
      $statement->bindValue(':editing_service_subcategory', $editing_service_subcategory);
      $statement->bindValue(':editing_service_id', $editing_service_id);
      $statement->bindValue(':editing_serivce_price', $editing_serivce_price);
      $statement->execute();
      $statement->closeCursor();

}

possible duplicate of [php PDO insert batch multiple rows with placeholders](http://stackoverflow.com/questions/15069962/php-pdo-insert-batch-multiple-rows-with-placeholders) — Kevin, Oct 01 '14 at 00:33
well, multiple row insert can be achieved if userid and item_label are not passed as variables into the second row of values....so, I just want to know why sql acts like that and how to fix it — Bubble, Oct 01 '14 at 01:17
I somehow managed to work around changing error exceptions and passed values as strings (well, lots of string functions), to trick sql to think that those two variables are static values...arr..it worked..but..it is really silly=.= — Bubble, Oct 01 '14 at 03:20

score 1 · Answer 1 · answered Oct 01 '14 at 01:25

1

http://php.net/manual/en/pdo.prepare.php

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

You need to rename seconds userid and item_label

answered Oct 01 '14 at 01:25

sectus

15,605
5
55
97

Thanks. I actually did. And it did not work. But the problem does not happen to $filename and $new_fileneame, even when I used them twice....Is it bizarre? – Bubble Oct 01 '14 at 01:29
and if I put these two insert into 2 different functions and call them together. SQL only insert whatever I call the first and does not insert the second one. – Bubble Oct 01 '14 at 01:32
@Bubble , It looks like second query raise constrain error. Try to setup your PDO to raise exceptions on error. – sectus Oct 01 '14 at 01:47
Thank you so much. I will definitely try that. – Bubble Oct 01 '14 at 01:50

SQL does not take all the passed variables from php

1 Answers1