Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: Coppermine SQL query  (Read 2657 times)

0 Members and 1 Guest are viewing this topic.

equi

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24
Coppermine SQL query
« on: December 21, 2004, 10:17:20 pm »

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
Logged

kegobeer

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 4637
  • Beer - it does a body good!
    • The Kazebeer Family Website
Re: Coppermine SQL query
« Reply #1 on: December 21, 2004, 10:26:56 pm »

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'
Logged
Do not send me a private message unless I ask for one.  Make your post public so everyone can benefit.

There are no stupid questions
But there are a LOT of inquisitive idiots

equi

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24
Re: Coppermine SQL query
« Reply #2 on: December 21, 2004, 10:41:20 pm »

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
Logged

FireMotion

  • Coppermine newbie
  • Offline Offline
  • Posts: 14
Re: Coppermine SQL query
« Reply #3 on: December 22, 2004, 12:31:46 am »

assuming you know how to work out the MySQL stuff, just do a:
Code: [Select]
"SELECT pid, filename FROM cpg_pictures"then with the result set, update the titles:
(with each row do:)
Code: [Select]
$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]
$keywords = $row['keywords'];
$newkeywords = 'your new keywords';
$pid = $row['pid'];
UPDATE cpg_pictures SET keywords = '$keywords,$newkeywords' WHERE pid = '$pid'
Something like this...
Logged

kegobeer

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 4637
  • Beer - it does a body good!
    • The Kazebeer Family Website
Re: Coppermine SQL query
« Reply #4 on: December 22, 2004, 12:32:29 am »

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.
Logged
Do not send me a private message unless I ask for one.  Make your post public so everyone can benefit.

There are no stupid questions
But there are a LOT of inquisitive idiots

equi

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24
Re: Coppermine SQL query
« Reply #5 on: December 22, 2004, 01:38:06 pm »

issue 1 ; solved   THANX VERY MUCH

issue2; using your syntax keg, overwrites data in cell with 0?
Logged
Pages: [1]   Go Up
 

Page created in 0.018 seconds with 19 queries.