Coppermine SQL query

[equi]

Apologies if a bit off topic, I have two queries for which i would appreciate any help

Issue 1:
I have a table with say 10 fields, one is called filename, another title. the title field is empty, the filenames fully populated. what i require is for the filename to be copied into the title field.

my thoughts of 2 possible solutions, but requiring a query.....1. copy field filename to new field called filename_copy, then rename to title and delete existing title field. 2.have a looping query to pull in each row by turn, copying filename into title field?


Issue 2:

I want to add multiple keywords to a fields but at different times.....eg using SET i can add 2 keywords to each item WHERE filepath = 'x', but i dont want it to overwrite whats already in the field entry, so i can select some rows and add 3 keywords, then at a later time select 50% of them and add say an addition 3 more keywords


Hope this is explained OK

Many thanx in advance

[kegobeer]

1:
UPDATE
tblname
SET colname=colname2

http://dev.mysql.com/doc/mysql/en/UPDATE.html

2:  Huh?  I don't understand why you are worried about different times.

UPDATE
tblname
SET colname = colname + ' keyword'
WHERE filepath='x'

[equi]

kegobeer,


wrt no. 1 ...will that preserve all the details in the filename field but duplicate the same file name in the title field.....also will that command loop for all records in db?

wrt different times. i just meant if on a new query i rerun a set command it always overwrites what is currently in the cell, presumably thats where the + comes in ... does that preserve the existing data in the cell then add additional data as well


thanx

[FireMotion]

assuming you know how to work out the MySQL stuff, just do a:

Code Select Expand

"SELECT pid, filename FROM cpg_pictures"
then with the result set, update the titles:
(with each row do:)

Code Select Expand


$pid = $row['pid'];
$filename = $row['filename'];
UPDATE cpg_pictures SET title = '$filename' WHERE pid = '$pid'

You should do the same with issue 2: first retrieve the table, then update it:

Code Select Expand

$keywords = $row['keywords'];
$newkeywords = 'your new keywords';
$pid = $row['pid'];
UPDATE cpg_pictures SET keywords = '$keywords,$newkeywords' WHERE pid = '$pid'
Something like this...

[kegobeer]

UPDATE
tblname
SET title=filename

This updates the entire table.  All title fields will have whatever value is in the filename fields.

Yes, using colname = colname + ' keyword' simply adds ' keyword' to whatever is in the colname field.

Please visit the MySQL link I provided.  You can read about all MySQL commands there.

@Firemotion: Looping isn't required in the first case, unless you use a where command which isn't necessary because he wants all title fields to have the values from the filename field.  It's also not necessary in the second case if he wants to add the same keyword to all items in the table using the WHERE filepath='x' clause.

[equi]

issue 1 ; solved   THANX VERY MUCH

issue2; using your syntax keg, overwrites data in cell with 0?