I have an interface to insert captions for photos...

in example I'm working on there are 21 photos in 'images' folder...

so after I test-insert all the captions I get:
+------+-------+---------+---------------------+
| blog | photo | caption | whenadded |
+------+-------+---------+---------------------+
| 1 | 1 | one | 2007-08-22 17:35:35 |
| 1 | 2 | two | 2007-08-22 17:35:35 |
| 1 | 3 | three | 2007-08-22 17:35:35 |
| 1 | 4 | four | 2007-08-22 17:35:35 |

etc...

then to insert the actual captions I have to do update (otherwise it
inserts photo again..) I have (java code..):

for (int i=1; i < iCaptionsL; i++) {
qUpdate = "update pb set caption='" + sCaptions[i] + "', photo=" + i +
", blog='" + sBlogPg + "'";
}

but with this command result I get 21 rows like this..

+------+-------+---------+---------------------+
| blog | photo | caption | whenadded |
+------+-------+---------+---------------------+
| 1 | 21 | | 2007-08-22 17:38:14 |
| 1 | 21 | | 2007-08-22 17:38:14 |
| 1 | 21 | | 2007-08-22 17:38:14 |
| 1 | 21 | | 2007-08-22 17:38:14 |
| 1 | 21 | | 2007-08-22 17:38:14 |

etc...


if I just run a command in command-line client, for example:

update pb set caption='nine', photo=9, blog='1';

it gets ignored..


I need to set this up so I can easily edit captions... so far this does
not look very easy....

thank you very much...

maya wrote:
> I have an interface to insert captions for photos...
>
> in example I'm working on there are 21 photos in 'images' folder...
>
> so after I test-insert all the captions I get:
> +------+-------+---------+---------------------+
> | blog | photo | caption | whenadded |
> +------+-------+---------+---------------------+
> | 1 | 1 | one | 2007-08-22 17:35:35 |
> | 1 | 2 | two | 2007-08-22 17:35:35 |
> | 1 | 3 | three | 2007-08-22 17:35:35 |
> | 1 | 4 | four | 2007-08-22 17:35:35 |
>
> etc...
>
> then to insert the actual captions I have to do update (otherwise it
> inserts photo again..) I have (java code..):
>
> for (int i=1; i < iCaptionsL; i++) {
> qUpdate = "update pb set caption='" + sCaptions[i] + "', photo=" + i
> + ", blog='" + sBlogPg + "'";
> }
>
> but with this command result I get 21 rows like this..
>
> +------+-------+---------+---------------------+
> | blog | photo | caption | whenadded |
> +------+-------+---------+---------------------+
> | 1 | 21 | | 2007-08-22 17:38:14 |
> | 1 | 21 | | 2007-08-22 17:38:14 |
> | 1 | 21 | | 2007-08-22 17:38:14 |
> | 1 | 21 | | 2007-08-22 17:38:14 |
> | 1 | 21 | | 2007-08-22 17:38:14 |
>
> etc...
>
>
> if I just run a command in command-line client, for example:
>
> update pb set caption='nine', photo=9, blog='1';
>
> it gets ignored..
>
>
> I need to set this up so I can easily edit captions... so far this does
> not look very easy....
>
> thank you very much...
>

You need a WHERE clause to select which row(s) to update. Otherwise it
updates the entire table with the same information.

If you want different data on different rows, you need separate UPDATE
statements (unless you can relate the updated data to other data in the
same row).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Hi Maya,

maya wrote:
> update pb set caption='nine', photo=9, blog='1';

IMHO this should be

update pb set caption='nine' WHERE photo=9, blog='1';


The WHERE clause specifies for which row the caption column needs to be set.

The statement, as you wrote, updates all rows in the table with the
values you specify (so caption = nine, photo = 9 and blog = 1). This is
also the reason why your table gets blog = 1 and photo = 21 if you run
your Java code. I bet iCaptionsL = 22 in your Java code. So 21 is
basically the last update it performs and sets all rows accordingly.

Hope this helps.

Kind regards,
Richard

Richard Willemsen wrote:
> Hi Maya,
>
> maya wrote:
>> update pb set caption='nine', photo=9, blog='1';
>
> IMHO this should be
>
> update pb set caption='nine' WHERE photo=9, blog='1';
>
>
> The WHERE clause specifies for which row the caption column needs to be
> set.
>
> The statement, as you wrote, updates all rows in the table with the
> values you specify (so caption = nine, photo = 9 and blog = 1). This is
> also the reason why your table gets blog = 1 and photo = 21 if you run
> your Java code. I bet iCaptionsL = 22 in your Java code. So 21 is
> basically the last update it performs and sets all rows accordingly.
>
> Hope this helps.
>
> Kind regards,
> Richard

thank you very much.. I had been told by someone here that you can omit
"WHERE".. oh well.. now have prob that when I do initial insert like
this:

qInsert = "insert into pb set caption='" + sCaptions[i] + "', photo=" +
i + ", blog='" + sBlogPg + "'";

stuff gets inserted twice, so I get:

+------+-------+---------+---------------------+
| blog | photo | caption | whenadded |
+------+-------+---------+---------------------+
| 1 | 1 | | 2007-08-23 10:40:32 |
| 1 | 1 | | 2007-08-23 10:40:32 |
| 1 | 2 | | 2007-08-23 10:40:32 |
| 1 | 2 | | 2007-08-23 10:40:32 |
| 1 | 3 | | 2007-08-23 10:40:32 |

etc..

why would this be???

so I can't test my update-stmt until I solve this.....

thank you very much..

maya wrote:
> Richard Willemsen wrote:
>> Hi Maya,
>>
>> maya wrote:
>>> update pb set caption='nine', photo=9, blog='1';
>>
>> IMHO this should be
>>
>> update pb set caption='nine' WHERE photo=9, blog='1';
>>
>>
>> The WHERE clause specifies for which row the caption column needs to
>> be set.
>>
>> The statement, as you wrote, updates all rows in the table with the
>> values you specify (so caption = nine, photo = 9 and blog = 1). This
>> is also the reason why your table gets blog = 1 and photo = 21 if you
>> run your Java code. I bet iCaptionsL = 22 in your Java code. So 21 is
>> basically the last update it performs and sets all rows accordingly.
>>
>> Hope this helps.
>>
>> Kind regards,
>> Richard
>
> thank you very much.. I had been told by someone here that you can omit
> "WHERE".. oh well.. now have prob that when I do initial insert like
> this:
>
> qInsert = "insert into pb set caption='" + sCaptions[i] + "', photo=" +
> i + ", blog='" + sBlogPg + "'";
>
> stuff gets inserted twice, so I get:
>
> +------+-------+---------+---------------------+
> | blog | photo | caption | whenadded |
> +------+-------+---------+---------------------+
> | 1 | 1 | | 2007-08-23 10:40:32 |
> | 1 | 1 | | 2007-08-23 10:40:32 |
> | 1 | 2 | | 2007-08-23 10:40:32 |
> | 1 | 2 | | 2007-08-23 10:40:32 |
> | 1 | 3 | | 2007-08-23 10:40:32 |
>
> etc..
>
> why would this be???
>
> so I can't test my update-stmt until I solve this.....
>
> thank you very much..
>

You don't use WHERE on an INSERT statement, but you do on most UPDATE
statements. Big difference.

And yes, it gets inserted twice because you told it to do so.

INSERT adds a new row. UPDATE changes the existing data. Two
statements, two purposes.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> maya wrote:
>> Richard Willemsen wrote:
>>> Hi Maya,
>>>
>>> maya wrote:
>>>> update pb set caption='nine', photo=9, blog='1';
>>>
>>> IMHO this should be
>>>
>>> update pb set caption='nine' WHERE photo=9, blog='1';
>>>
>>>
>>> The WHERE clause specifies for which row the caption column needs to
>>> be set.
>>>
>>> The statement, as you wrote, updates all rows in the table with the
>>> values you specify (so caption = nine, photo = 9 and blog = 1). This
>>> is also the reason why your table gets blog = 1 and photo = 21 if you
>>> run your Java code. I bet iCaptionsL = 22 in your Java code. So 21 is
>>> basically the last update it performs and sets all rows accordingly.
>>>
>>> Hope this helps.
>>>
>>> Kind regards,
>>> Richard
>>
>> thank you very much.. I had been told by someone here that you can
>> omit "WHERE".. oh well.. now have prob that when I do initial
>> insert like this:
>>
>> qInsert = "insert into pb set caption='" + sCaptions[i] + "', photo="
>> + i + ", blog='" + sBlogPg + "'";
>>
>> stuff gets inserted twice, so I get:
>>
>> +------+-------+---------+---------------------+
>> | blog | photo | caption | whenadded |
>> +------+-------+---------+---------------------+
>> | 1 | 1 | | 2007-08-23 10:40:32 |
>> | 1 | 1 | | 2007-08-23 10:40:32 |
>> | 1 | 2 | | 2007-08-23 10:40:32 |
>> | 1 | 2 | | 2007-08-23 10:40:32 |
>> | 1 | 3 | | 2007-08-23 10:40:32 |
>>
>> etc..
>>
>> why would this be???
>>
>> so I can't test my update-stmt until I solve this.....
>>
>> thank you very much..
>>
>
> You don't use WHERE on an INSERT statement, but you do on most UPDATE
> statements. Big difference.
>
> And yes, it gets inserted twice because you told it to do so.
>
> INSERT adds a new row. UPDATE changes the existing data. Two
> statements, two purposes.
>

ok, but when I just INSERT stuff gets inserted twice, before I do
update.....

thank you...

maya wrote:
> Jerry Stuckle wrote:
>> maya wrote:
>>> Richard Willemsen wrote:
>>>> Hi Maya,
>>>>
>>>> maya wrote:
>>>>> update pb set caption='nine', photo=9, blog='1';
>>>>
>>>> IMHO this should be
>>>>
>>>> update pb set caption='nine' WHERE photo=9, blog='1';
>>>>
>>>>
>>>> The WHERE clause specifies for which row the caption column needs to
>>>> be set.
>>>>
>>>> The statement, as you wrote, updates all rows in the table with the
>>>> values you specify (so caption = nine, photo = 9 and blog = 1). This
>>>> is also the reason why your table gets blog = 1 and photo = 21 if
>>>> you run your Java code. I bet iCaptionsL = 22 in your Java code. So
>>>> 21 is basically the last update it performs and sets all rows
>>>> accordingly.
>>>>
>>>> Hope this helps.
>>>>
>>>> Kind regards,
>>>> Richard
>>>
>>> thank you very much.. I had been told by someone here that you can
>>> omit "WHERE".. oh well.. now have prob that when I do initial
>>> insert like this:
>>>
>>> qInsert = "insert into pb set caption='" + sCaptions[i] + "', photo="
>>> + i + ", blog='" + sBlogPg + "'";
>>>
>>> stuff gets inserted twice, so I get:
>>>
>>> +------+-------+---------+---------------------+
>>> | blog | photo | caption | whenadded |
>>> +------+-------+---------+---------------------+
>>> | 1 | 1 | | 2007-08-23 10:40:32 |
>>> | 1 | 1 | | 2007-08-23 10:40:32 |
>>> | 1 | 2 | | 2007-08-23 10:40:32 |
>>> | 1 | 2 | | 2007-08-23 10:40:32 |
>>> | 1 | 3 | | 2007-08-23 10:40:32 |
>>>
>>> etc..
>>>
>>> why would this be???
>>>
>>> so I can't test my update-stmt until I solve this.....
>>>
>>> thank you very much..
>>>
>>
>> You don't use WHERE on an INSERT statement, but you do on most UPDATE
>> statements. Big difference.
>>
>> And yes, it gets inserted twice because you told it to do so.
>>
>> INSERT adds a new row. UPDATE changes the existing data. Two
>> statements, two purposes.
>>
>
> ok, but when I just INSERT stuff gets inserted twice, before I do
> update.....
>
> thank you...
>

One INSERT statement inserts one item. Execute it a second time and it
inserts another item.

So either you alreaydy have a row with that data and you're inserting a
second row, or you're executing the INSERT twice.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Hey Maya

Can you copy/past that section of source. It's probably an issue with
your Java code and not the insert statement.

BTW, if you correctly "design" the model (tables) you would not even be
allowed to insert the rows twice since the primary key would prevent
that. In that case an exception would be thrown. In this case the
primary key would include the blog and photo column.

Kind regards,
Richard


maya wrote:
> Richard Willemsen wrote:
>> Hi Maya,
>>
>> maya wrote:
>>> update pb set caption='nine', photo=9, blog='1';
>>
>> IMHO this should be
>>
>> update pb set caption='nine' WHERE photo=9, blog='1';
>>
>>
>> The WHERE clause specifies for which row the caption column needs to
>> be set.
>>
>> The statement, as you wrote, updates all rows in the table with the
>> values you specify (so caption = nine, photo = 9 and blog = 1). This
>> is also the reason why your table gets blog = 1 and photo = 21 if you
>> run your Java code. I bet iCaptionsL = 22 in your Java code. So 21 is
>> basically the last update it performs and sets all rows accordingly.
>>
>> Hope this helps.
>>
>> Kind regards,
>> Richard
>
> thank you very much.. I had been told by someone here that you can omit
> "WHERE".. oh well.. now have prob that when I do initial insert like
> this:
>
> qInsert = "insert into pb set caption='" + sCaptions[i] + "', photo=" +
> i + ", blog='" + sBlogPg + "'";
>
> stuff gets inserted twice, so I get:
>
> +------+-------+---------+---------------------+
> | blog | photo | caption | whenadded |
> +------+-------+---------+---------------------+
> | 1 | 1 | | 2007-08-23 10:40:32 |
> | 1 | 1 | | 2007-08-23 10:40:32 |
> | 1 | 2 | | 2007-08-23 10:40:32 |
> | 1 | 2 | | 2007-08-23 10:40:32 |
> | 1 | 3 | | 2007-08-23 10:40:32 |
>
> etc..
>
> why would this be???
>
> so I can't test my update-stmt until I solve this.....
>
> thank you very much..
>

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.. 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..)

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...

thank you very much...





Richard Willemsen wrote:
> Hey Maya
>
> Can you copy/past that section of source. It's probably an issue with
> your Java code and not the insert statement.
>
> BTW, if you correctly "design" the model (tables) you would not even be
> allowed to insert the rows twice since the primary key would prevent
> that. In that case an exception would be thrown. In this case the
> primary key would include the blog and photo column.
>
> Kind regards,
> Richard
>
>
> maya wrote:
>> Richard Willemsen wrote:
>>> Hi Maya,
>>>
>>> maya wrote:
>>>> update pb set caption='nine', photo=9, blog='1';
>>>
>>> IMHO this should be
>>>
>>> update pb set caption='nine' WHERE photo=9, blog='1';
>>>
>>>
>>> The WHERE clause specifies for which row the caption column needs to
>>> be set.
>>>
>>> The statement, as you wrote, updates all rows in the table with the
>>> values you specify (so caption = nine, photo = 9 and blog = 1). This
>>> is also the reason why your table gets blog = 1 and photo = 21 if you
>>> run your Java code. I bet iCaptionsL = 22 in your Java code. So 21 is
>>> basically the last update it performs and sets all rows accordingly.
>>>
>>> Hope this helps.
>>>
>>> Kind regards,
>>> Richard
>>
>> thank you very much.. I had been told by someone here that you can
>> omit "WHERE".. oh well.. now have prob that when I do initial
>> insert like this:
>>
>> qInsert = "insert into pb set caption='" + sCaptions[i] + "', photo="
>> + i + ", blog='" + sBlogPg + "'";
>>
>> stuff gets inserted twice, so I get:
>>
>> +------+-------+---------+---------------------+
>> | blog | photo | caption | whenadded |
>> +------+-------+---------+---------------------+
>> | 1 | 1 | | 2007-08-23 10:40:32 |
>> | 1 | 1 | | 2007-08-23 10:40:32 |
>> | 1 | 2 | | 2007-08-23 10:40:32 |
>> | 1 | 2 | | 2007-08-23 10:40:32 |
>> | 1 | 3 | | 2007-08-23 10:40:32 |
>>
>> etc..
>>
>> why would this be???
>>
>> so I can't test my update-stmt until I solve this.....
>>
>> thank you very much..
>>

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