Current install is 1.4.8 (looking at upgrading to 1.4.9)
When I batch upload, the uploaded images have a "random" image order. It appears that because images are not imported in any specific order, the import order assigns the order in which they are then displayed. It's not what I want, and on small albums, it's an easy fix to just reorder them using the "Sort My Pictures" tool.
But when the imported images number a few hundred, this method is tedious at best. I'm looking for a way to automatically assign the order ID to the same order as the filename sort. I don't know php or SQL very well, otherwise I think this would be a fairly simple task. Even a SQL query that I can apply would do the trick.
I've used "SELECT pid,filename,position FROM `pictures`where `aid`='3' order by filename;" to show me the list in the correct order. Now I just need to update the position field so it's ascending and that should, I think, do what I want.
If you have a recent version of MySQL, use this query:
CREATE TEMPORARY TABLE temp AS SELECT * FROM cpg_pictures WHERE aid = 3;
UPDATE cpg_pictures p SET position = (SELECT COUNT(*) FROM temp WHERE filename < p.filename) WHERE aid = 3;
That will re-order the position by filename for the album you specify.
It looks like I'm running an older version (MySQL version 4.0.27-standard-log)
The query didn't work, so I split it into two. The create temporary table was successful, and the update failed. The failure almost looks like the query is truncated. The first error was "#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) FROM temp WHERE filename < p.filename) WHERE ai " >:(
Or is it something else in the query that 4.0 is having a problem with? ???
ps. I have to access the database via phpMyAdmin, but I don't think that's a problem.
You need at least 4.1 for the query to work. It should be fairly easy to simulate the subquery in php.
Thanks - I'll check with my ISP and see if they can upgrade MySQL.
If they can not, how would I rewrite the query using 4.0?
Getting the ISP to upgrade is not going to happen. ??? >:(
Is there quick php code I can put out there to essentially do this, at least for now? I can drop it on the web site, run it manually and that could update the database.
Thx