Batch Upload and order

[osh]

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.

[Nibbler]

If you have a recent version of MySQL, use this query:

Code Select Expand


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.

[osh]

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.

[Nibbler]

You need at least 4.1 for the query to work. It should be fairly easy to simulate the subquery in php.

[osh]

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?

[osh]

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