http://qs321.pair.com?node_id=626720

valavanp has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks, I have the following code to insert into mysql db. i am posting just the code which i have problem.
@color=(Blue, Silver); $newpid=8000; foreach $color(@color){ $color=~s/^\s*//g; my $incolor_qry="insert into colorimage_info(pid,color) values + ('$newpid','$color')"; my $incolor_qy=$db->prepare($incolor_qry); $incolor_qy->execute; }
After inserting this i need to update the rows which i inserted by the following code:
@color_image=(a1.jpg, b1.jpg); foreach $color_image(@color_image){ $color_image=~s/^\s*//g; my $upcolor_qry="update colorimage_info set color_image='$colo +r_image.JPG' where id='$newpid'; my $upcolor_qy=$db->prepare($upcolor_qry); $upcolor_qy->execute; }
The results which i am getting is
pid cid color color_image 8000 1 Blue a1.jpg 8000 2 Silver a1.jpg
The color_image column is not updated properly. I am missing some thing here. Can anyone tell me where i am wrong.cid column is autocremented and not null.

Replies are listed 'Best First'.
Re: sql queries updation improper
by derby (Abbot) on Jul 15, 2007 at 15:30 UTC

    Uhh ... yeah ... you're missing something. Read that second sql statement out loud while looking at your table. Do you see it? update table set field = value where id = key. Looks like the table is updated correctly to me. Still don't see it? Okay, here's a hint: where's cid come into play?

    -derby
      The OP said that cid is autoincremented. With MySQL you don't have to handle an autoincremented column.

        Autoincrementing happens on the insert, not the update. There's not enough info in the OP but cid is either the primary key or part of a compound key and it needs to be part of the update sql. (sigh ... I need to remove *master of subtlety* from my CV).

        -derby
        This is where DBI]'s last_insert_id comes in handy ... oh, thats only in DBD::Pg but you can always select the highest id immediately after inserting a record.

        just another cpan module author
Re: sql queries updation improper
by kyle (Abbot) on Jul 16, 2007 at 02:21 UTC

    According to the table you show, every record has the same value for pid. As such, when you UPDATE ... WHERE pid=8000, every record is changed. Your program will set every color_image to some value, then set them all to some other value, etc. At the end of the loop, they'll all have the last value set.

    What you need to do is write the WHERE clause so that each UPDATE affects only one record (or at least only the records you want to affect).

Re: sql queries updation improper
by perllove (Beadle) on Jul 15, 2007 at 15:28 UTC
    Is the set statement be like "set color" instead of set color_image?

    I mean I think you are updating a column which is not existing in the DB

Re: sql queries updation improper
by graff (Chancellor) on Jul 16, 2007 at 13:03 UTC
    You did not show us how the "cid" field is being set, and based on what you have shown, it's not clear why you need to do inserts and then updates on the same table. Why not assemble all the information first, and then just do inserts?

    And at that point, if you are using a typical RDBMS like mysql or postgres or oracle, why not put the full-row insertion data into a plain tab-delimited file, and use the appropriate loading tool (mysqlimport or a "LOAD DATA INFILE" statement or sqlload or whatever). If/when you have many thousands of records to insert, this will go significantly faster.

    But if you want to stick with DBI insert (and update?) statements like you've shown, you should at least learn about placeholders:

    ... my $insert_clr_sql = "insert into colorimage_info (pid,color) values ( +?,?)"; my $insert_clr_sth = $db->prepare( $insert_clr_sql ); for my $color ( @colors ) { $insert_clr_sth->execute( $npid, $color ); } ...

    update: Sorry, I just noticed your remark about how the "cid" field is an auto-increment (presumably "primary key") field. There are ways to get back the value of such a field from DBI after doing an insert (look for "last_insert_id" in the DBI man page), and you can then use that in later updates. Otherwise, you'll need to keep track of the relationship between "colors" ("Blue", "Silver") and "images" ("a1.jpg", "a2.jpg"), so that your update statement looks like:

    update colorimage_info set color_image=? where pid=? and color=?