What?
In not as many words as others, here's my MySQL query to extract details on images stored in the MediaWiki CMS system (v1.14).

Why?
Prior to a migration and just after another change freeze, I had sent all Wiki articles modified since the last export but then needed to send all images that had also been either added/modified since.

How?
Using MySQL, the following query lists the image name, size, user who uploaded, timestamp and the path. Remember that the paths are determined using the MD5 Hash of the filename:

select 
	img_name,
	img_size,
	img_user_text,
	img_timestamp,
	CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2)) AS img_path,
	CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2), '/', img_name) AS img_path_with_file
from 
	wikimedia_image 
-- where 
	-- SUBSTRING(img_timestamp, 1, 8)>=20110922 
ORDER BY 
	img_timestamp DESC

Note that I've commented out the lines which specify images modified since the 22nd of September 2011. I've left it in there for my own benefit as it's unlikely I'm asked for an entire export.

Should yield something like:

img_name	img_size	img_user_text	img_timestamp	img_path	img_path_with_file
==============  ==============  ==============  ==============  ==============  ==================
Pic_3.jpg	50242		User1		20111129115343	e/e8		e/e8/Pic_3.jpg
Pic_2.jpg	38624		User1		20111129115335	8/87		8/87/Pic_2.jpg
Pic_1.jpg	41197		User1		20111129115325	e/eb		e/eb/Pic_1.jpg
Ebl07.jpg	321307		User2		20111129101544	6/66		6/66/Ebl07.jpg
Ebl06.jpg	270863		User2		20111129101521	7/74		7/74/Ebl06.jpg
Ebl05.jpg	463948		User2		20111129101512	b/b5		b/b5/Ebl05.jpg
Ebl03.jpg	318017		User2		20111129101502	1/13		1/13/Ebl03.jpg

Next >