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.