updating table..
This is a discussion on updating table.. within the MySQL forums, part of the Database Server Software category; --> thank you very much for your help.. of course primary key helped a lot.... (put primary key for 'photo' ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 11:27 AM
| ||||
| ||||
 Re: updating table.. thank you very much for your help.. of course primary key helped a lot....  (put primary key for 'photo' column, but then realized if I do that I have to do a separate table for each blog, but well, that would be ok..) Richard Willemsen wrote: > Hi Maya, > > First of all, great photos. I wish I could find the time to start > photographing again. I have the stuff (Nikon D70) but not the time.  > > Anyway...  > > maya wrote: >> >> my gosh, you're right, my design is probably not the best.. I don't >> have a lot of MySQL exp.. I know how to do basic stuff, small apps, >> but not major/very complex stuff.. > > No worries, it's not that bad. It's just that if you take a bit more > time in adding constraints (primary keys, foreign keys, check, etc), it > will save you a lot of time in fixing data issues later on. At least try > ALWAYS to create a primary key for each table. If you cant then you need > to redesign your tables. Just ask yourself the question 'how do I > recognize (or identify) each row?'. The answer is your primary key. Then > if all your tables have primary keys, it's not that hard to figure out > the foreign keys (links between the tables). Foreign keys are always > based on the primary key of the referenced table. > > Anyway, as said, don't worry about it too much for now. > >> ok, here's the deal: this is interface where I'm inserting captions: >> >> http://www.francesdelrio.com/photobl...n/captions.jsp >> (pls don't click on any buttons, db has not been set up online yet.. >> I'm still only doing db-stuff locally.. also, links to diff blogs not >> working for now since still developing, but when they work images for >> that particular blog will be displayed on the right..) >> >> it's for this blog: >> www.francesdelrio.com/photoblog/blog1/photos.jsp >> >> after grabbing all captions (incl. ones that are empty strings, since >> not all photos will have captions) I do: >> >> for (int i=1; i < iCaptionsL; i++) { qInsert = "insert into pb >> set caption='" + sCaptions[i] + "', photo=" + i + ", blog='" + sBlogPg >> + "'"; >> stmt.executeUpdate(qInsert); >> } >> >> that's it.. no other insert-stmts in proc_captions.jsp (where form >> submits to..) >> > > Looks ok to me too (although using executeQuery would be more > appropriate in this case). Just something I'm wondering about: > > 1) Is it possible that the code is called twice (or in your source or > the page is called twice) > > 2) Is it possible that those records are already in the table while you > are testing. Meaning, do you delete the records before you hit the > submit button? > > You can try to create the primary key because then you will get an error > when the duplicate records are inserted. This may give you a hint when > it happens. > >> not sure yet how to overall plan/design this, but what I'm planning to >> do is: first time I load this interface for a new blog (page) I have >> just added, I click 'insert' button, so it inserts amount of rows >> corresponding to how many images are in that particular blog (program >> counts how many images are in 'images' folder, I use this no. to >> generate all photo-divs, nav, etc. in the blogs..) >> >> then once all rows are in place I can start doing updates (for each >> row either insert a caption or leave it blank if no caption for that >> photo..) >> >> then do update, which takes me to proc_captions_update.jsp, where I >> have this stmt: >> >> for (int i=1; i < iCaptionsL; i++) { qUpdate = "update pb set >> caption='" + sCaptions[i] + "' WHERE photo=" + i + " and blog='" + >> sBlogPg + "'"; >> stmt.executeUpdate(qUpdate); >> } >> >> not sure if this right approach.. would very much appreciate >> suggestions... >> > > Although it's not a bad approach, I wouldn't do the "fixed" inserts > first. What happens when you want to add an extra photo? So a "add photo > each time" would be a better approach, or at least that's how I would do > it. Keep in mind, I don't know precisely what your "work flow" would be > when maintaining the blog so your approach may be best as well. > > Although this is c.d.mysql, I would like to give you the following Java > tip as well. Instead of generating a SQL statement each time with all > the quotes, you can also use a prepared statement and use parameters. > Have a read here > > http://java.sun.com/docs/books/tutor.../prepared.html > > Especially the section "Supplying Values for PreparedStatement > Parameters" which has two code fragements explaining the difference. > > Most programming languages where you connect to a database has some > similar features. It avoids those awkward quoting and also allows you > re-use the statement even when the parameters change. It could also > improve performance because the database engine may decide to cache the > statements. > >> thank you very much... > > You are welcome. :-) > > Kind regards, > Richard      |
 |
« joining many tables together while searching
|
MI5 Persecution: GLR: David Hepworth (21/Feb/1997) (2435) »
| Thread Tools | |
| Display Modes | |
Linear Mode
|
|
All times are GMT. The time now is 04:04 PM.
