Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

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


Comment on Re: sql queries updation improper
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://626836]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (8)
As of 2014-07-22 11:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (110 votes), past polls