MediaWiki: MySQL to extract path to images

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).

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.

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:
	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
-- where 
	-- SUBSTRING(img_timestamp, 1, 8)>=20110922 
	img_timestamp DESC
  1.  select 
  2.      img_name, 
  3.      img_size, 
  4.      img_user_text, 
  5.      img_timestamp, 
  6.      CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2)) AS img_path, 
  7.      CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2), '/', img_name) AS img_path_with_file 
  8.  from 
  9.      wikimedia_image 
  10.  -- where 
  11.      -- SUBSTRING(img_timestamp, 1, 8)>=20110922 
  12.  ORDER BY 
  13.      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
  1.  img_name    img_size    img_user_text    img_timestamp    img_path    img_path_with_file 
  2.  ==============  ==============  ==============  ==============  ==============  ================== 
  3.  Pic_3.jpg    50242        User1        20111129115343    e/e8        e/e8/Pic_3.jpg 
  4.  Pic_2.jpg    38624        User1        20111129115335    8/87        8/87/Pic_2.jpg 
  5.  Pic_1.jpg    41197        User1        20111129115325    e/eb        e/eb/Pic_1.jpg 
  6.  Ebl07.jpg    321307        User2        20111129101544    6/66        6/66/Ebl07.jpg 
  7.  Ebl06.jpg    270863        User2        20111129101521    7/74        7/74/Ebl06.jpg 
  8.  Ebl05.jpg    463948        User2        20111129101512    b/b5        b/b5/Ebl05.jpg 
  9.  Ebl03.jpg    318017        User2        20111129101502    1/13        1/13/Ebl03.jpg 
Category: MediaWiki :: Article: 406

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.