wordpress - Unique Scenario - get attachment ID by URL

Attachments are stored in the wp_posts and wp_postmeta tables.

wp_posts.guid seems to contain the original filename at its upload location

wp_postmeta (where meta_key="_wp_attachment_metadata") contains a serialized ( http://php.net/manual/en/function.serialize.php ) PHP array, which contains the resized file names among other things:

a:6:{s:5:"width";s:4:"1000";s:6:"height";s:3:"750";s:14:"hwstring_small";s:23:"height='96' width='128'";s:4:"file";s:35:"2010/12/IMG_2543-e1291981569982.jpg";s:5:"sizes";a:3:{s:9:"thumbnail";a:3:{s:4:"file";s:33:"IMG_2543-e1291981569982-90x67.jpg";s:5:"width";s:2:"90";s:6:"height";s:2:"67";}s:6:"medium";a:3:{s:4:"file";s:35:"IMG_2543-e1291981569982-180x135.jpg";s:5:"width";s:3:"180";s:6:"height";s:3:"135";}s:5:"large";a:3:{s:4:"file";s:35:"IMG_2543-e1291981569982-500x375.jpg";s:5:"width";s:3:"500";s:6:"height";s:3:"375";}}s:10:"image_meta";a:10:{s:8:"aperture";s:1:"0";s:6:"credit";s:0:"";s:6:"camera";s:0:"";s:7:"caption";s:0:"";s:17:"created_timestamp";s:1:"0";s:9:"copyright";s:0:"";s:12:"focal_length";s:1:"0";s:3:"iso";s:1:"0";s:13:"shutter_speed";s:1:"0";s:5:"title";s:0:"";}}

The only way WordPress/you could lookup post_id by the resized file name would be the following:

• Prefiltering potential matches using MySQL LIKE (will be sloooow):

SELECT     wp_posts.ID, wp_postmeta.meta_valueFROM       wp_postsINNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id                      AND wp_postmeta.meta_key = '_wp_attachment_metadata'                      AND wp_postmeta.meta_value LIKE '%"IMG_2345-100x100.jpg"%'

• Verify/narrow search results in PHP by unserializing (with http://codex.wordpress.org/Function_Reference/maybe_unserialize ?) each meta_value, and manually check if it really matches the filename.

Part II.

function thumbnail_url_to_id( $file_url ){  global $wpdb;  $filename = basename( $file_url );  $rows = $wpdb->get_results( $wpdb->prepare("  SELECT     wp_posts.ID, wp_postmeta.meta_value  FROM       wp_posts  INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id                        AND wp_postmeta.meta_key = '_wp_attachment_metadata'                        AND wp_postmeta.meta_value LIKE %s  ",'%"'.like_escape($filename).'"%'  ));  foreach( $rows as $row ){    $row -> meta_value = maybe_unserialize( $row -> meta_value );    //tr( $row );    var_dump( $row );  }}$files = explode("\n",'TEST_123-90x67.jpgTEST_123-90x671.jpgTEST_123-180x134.jpgTEST_123-180x1341-90x67.jpgTEST_123-180x1341.jpgTEST_123-500x373.jpgTEST_123-500x3731-90x67.jpgTEST_123-500x3731-180x134.jpgTEST_123-500x3731.jpgTEST_123.jpgTEST_1231-90x67.jpgTEST_1231-180x134.jpgTEST_1231-500x373.jpgTEST_1231.jpg');$upload_base = 'http://cc/wordpress/wp-content/uploads/2012/06/';foreach( $files as $filename ){  thumbnail_url_to_id( $upload_base.$filename );}

Test results: http://jsfiddle.net/PcjKA/