Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re: sql queries updation improper

by graff (Chancellor)
on Jul 16, 2007 at 13:03 UTC ( #626836=note: print w/replies, xml ) Need Help??

in reply to sql queries updation improper

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=?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://626836]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (9)
As of 2018-03-19 17:35 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (245 votes). Check out past polls.