Custom Query Causing upload.php to Malfunction

[EME]

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.

[EME]

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.

[EME]

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.

[EME]

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.

[phill104]

Many thanks for coming back and resolving your thread and apologies for not coming along to assist.

[Αndré]

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.