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: Custom Query Causing upload.php to Malfunction  (Read 2431 times)

0 Members and 1 Guest are viewing this topic.

EME

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Posts: 8
Custom Query Causing upload.php to Malfunction
« on: July 18, 2013, 09:21:58 pm »

We are using Coppermine as an archive for old family history items (such as photos and letters from the 1920's).  We are impressed as there are now 1,000's of items and it is very stable.  I cannot link you directly as we are using your password feature.

Each album is named for a year and contains only items from that year.  We are using one of the custom fields (user1) to give each uploaded item a year code so that they can be searched (Example: show all items from year 1922 with Bob Smith in the title or caption).

We need to add a feature that will reference the albums table automatically and add the album name to field user1 in the pictures table on upload of each new item.  Otherwise we will have to rely on users to remember to add it each time, and this will certainly introduce error into our search results.

I wrote the query and got it working properly when executed directly in Mysql on the server (please excuse if this is not pretty, it is pasted from OneNote):

CREATE TABLE tmp_1 (user1 varchar(255), pid int(11));INSERT INTO `famgallery`.`tmp_1` SET `user1` = (SELECT `title` FROM `cpg15x_albums` WHERE `aid` = (SELECT aid FROM cpg15x_pictures WHERE pid=(select max(pid) from cpg15x_pictures)));
UPDATE `famgallery`.`tmp_1` SET `pid` = (SELECT MAX(pid) FROM cpg15x_pictures)
;  UPDATE cpg15x_pictures f, tmp_1 t
SET f.user1 = t.user1
WHERE f.pid = t.pid; DROP TABLE tmp_1;

Then I set about moving it into PHP and this is where the problems started.  First a bunch of syntax errors came up as I converted to the predefined values in your documentation (Example: $CONFIG['TABLE_ALBUMS']).  But I cleared those all out and ended up with this:

cpg_db_query("CREATE TABLE {$CONFIG['TABLE_PREFIX']}tmp_1 (user1 varchar(255), pid int(11));INSERT INTO {$CONFIG['TABLE_PREFIX']}tmp_1` SET `user1` = (SELECT `title` FROM {$CONFIG['TABLE_ALBUMS']} WHERE `aid` = (SELECT aid FROM {$CONFIG['TABLE_PICTURES']} WHERE pid=(select max(pid) from {$CONFIG['TABLE_PICTURES']})));
UPDATE `famgallery`.`{CONFIG['TABLE_PREFIX']}tmp_1` SET `pid` = (SELECT MAX(pid) FROM {$CONFIG['TABLE_PICTURES']})
;  UPDATE {$CONFIG['TABLE_PICTURES']} f, {CONFIG['TABLE_PREFIX']}tmp_1 t
SET f.user1 = t.user1
WHERE f.pid = t.pid; DROP TABLE {CONFIG['TABLE_PREFIX']}tmp_1;");

It is placed at the end of upload.php (included in attached zip file).  I removed the exit command from the end of the previous series of conditional statements, as it is my understanding that nothing after that would be recognized.  It uploads the file but does not execute the custom query, and returns an error (also in the zip file).  I looked in the debug information (contained in zip file).  But it seems to be returning the same empty value over and over before timing out.

I have searched the board with phrases like "upload.php", "cpg_db_query", and "custom query".  But none of the results have been related to my situation.  Any guidance would be appreciated.
Logged

EME

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Posts: 8
Re: Custom Query Causing upload.php to Malfunction
« Reply #1 on: July 19, 2013, 03:46:09 pm »

UPDATE FROM ORIGINAL POSTER:

This morning I came back to the problem again.  Whereas last night Coppermine was giving me a brief message through the GUI now it is throwing a Critical Error with much text.  This is probably just what the doctor ordered as it provides specific clues.

I have attached the Critical Error and will share the solution once it is completed.  If anyone has thoughts on how to solve this problem in the meantime please let me know.
Logged

EME

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Posts: 8
Re: Custom Query Causing upload.php to Malfunction
« Reply #2 on: July 20, 2013, 03:40:52 pm »

UPDATE FROM ORIGINAL POSTER:

So what I've learned is that is that cpg_db_query is a wrapper around mysql_query.  As such it can only run one query at a time.

Breaking the original example in this thread into multiple queries got it working:

cpg_db_query("CREATE TABLE {$CONFIG['TABLE_PREFIX']}tmp_1 (user1 varchar(255), pid int(11))"); cpg_db_query("INSERT INTO `famgallery`.`{$CONFIG['TABLE_PREFIX']}tmp_1` SET `user1` = (SELECT `title` FROM {$CONFIG['TABLE_ALBUMS']} WHERE `aid` = (SELECT aid FROM {$CONFIG['TABLE_PICTURES']} WHERE pid=(select max(pid) from {$CONFIG['TABLE_PICTURES']})))"); cpg_db_query("UPDATE `famgallery`.`{$CONFIG['TABLE_PREFIX']}tmp_1` SET `pid` = (SELECT MAX(pid) FROM {$CONFIG['TABLE_PICTURES']})");  cpg_db_query("UPDATE {$CONFIG['TABLE_PICTURES']} f, {$CONFIG['TABLE_PREFIX']}tmp_1 t SET f.user1 = t.user1 WHERE f.pid = t.pid"); cpg_db_query("DROP TABLE {$CONFIG['TABLE_PREFIX']}tmp_1");

My hope is that someone on the Coppermine Development Team will be able to take a look, though I know it is all volunteer.  I think this is a clean modification.  But if I'm doing something that is going to cause problems with existing functionality I'd appreciate hearing about it.  The final version of upload.php is attached as a text file.
Logged

EME

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Posts: 8
Re: Custom Query Causing upload.php to Malfunction
« Reply #3 on: July 20, 2013, 03:51:34 pm »

ONE FINAL NOTE FROM ORIGINAL POSTER:

Reviewing the critical error was what eventually got this solved.

It seems my hosting provider was to blame for the upload function only sending a brief notification through the GUI at first.  The day that I posted regarding this they were experiencing a problem that slowed system performance down significantly.  I would guess the Coppermine upload feature was timing out before it could get around to generating the critical error.
Logged

phill104

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4886
    • Windsurf.me
Re: Custom Query Causing upload.php to Malfunction
« Reply #4 on: July 20, 2013, 05:07:39 pm »

Many thanks for coming back and resolving your thread and apologies for not coming along to assist.
Logged
It is a mistake to think you can solve any major problems just with potatoes.

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Custom Query Causing upload.php to Malfunction
« Reply #5 on: August 23, 2013, 04:29:57 pm »

If I understood correctly you just want to add the album name to the custom user field for new uploads? If so, there's a much easier solution without creating a temp table and querying the database so often. Please confirm and I'll create the mod for you.
Logged
Pages: [1]   Go Up
 

Page created in 0.017 seconds with 19 queries.