wordpress - Unique Scenario - get attachment ID by URL

Question

I have encountered a quit unique scenario where I have an IMAGE url but not the ID of an unattached attachment (I know it sounds strange - but in wordpress terminology it is correct :-) ).

I am engaging the wordpress attachment upload form in a plugin, where a user can upload an image . he then clicks the "insert into post" button which populates the $options field. (standard procedure)

I then need the ID of that image. The thing is that most of the functions of wordpress need an ID to work.

I can retrieve the ID of that image with a code like this :

$image_src = $options['upload_image'] ; // the options field in the plugin that holds the image URL


    $postid_img  = $wpdb->get_var(
        "SELECT ID FROM $wpdb->posts 
         WHERE guid = '$image_src' 
         AND post_type='attachment' LIMIT 1");

Now $postid_img is in fact the ID .

So where is the problem ?

The problem is that this code works perfectly only if the chosen image size is the FULL image size . whenever the URL has intermediate image sizes, (e.g. - the image name , and hence the URL is with a surfixed size like ImageName -123x4500.jpg) - the above function will return 0.

Most of the wordpress attachment related functions (like get_intermediate_size() , or get_attachment_url() or many others ) need the ID as a parameter. but I do not have the ID at this stage of the function.

I even tried a little unknown function called url_to_postid( $url ) - which also fails.

I know that theoretically I could remove all the other sizes from the form, but it is a "hack" - not a solution - especially when we are inside a plugin that might need to co-exist with other plugins .

So how can I get the ID (of the "FULL" image) , based on a "custom" image URL with an Unknown ID , which is also unattached (not to post, page) at that moment ?

(Note that all the solutions which involve a post object or a custom query (which is not direct sql) will probably fail because at this point - the image is not attached to nothing - even if uploaded .)

EDIT I

(Note 2 - all string manipulations on the URL like trimming/regex etc, will be too dangerous to engage . if I will rely on the resolution part (e.g. 300x300) - I can never know what it will be , 4digitx4digit, 3x4 etc.. if I rely on the "-" character , there is no guarentee it will not appear in the image name itself.)

EDIT II UPDATE I -

I found a Hack to "resolve" this problem. I write "resolve" becasue it will work in MY SPECIFIC CASE , but not in all cases . (thanks to kovshenin on #wordpress IRC who pinted me in the right direction)

I changed the jQuery code of that updates the input field from :

window.send_to_editor = function(html) {
    imgurl = jQuery('img',html).attr('src');
    jQuery('#upload_image').val(imgurl);
    tb_remove();
    }

to :

window.send_to_editor = function(html) {
       var attachment_id = 0;
       var classes = jQuery('img',html).attr('class').match(/wp\-image\-([0-9]+)/); // regex to "hijack" the class name (which is the ID)
       if ( classes[1] )
        attachment_id = classes[1];
            imgurl = jQuery('img',html).attr('src');
            jQuery('#upload_image').val(attachment_id); // assign att id.
           tb_remove();
};

What this code does is actually take a CLASS name from the upload form (which has the ID in it ) and populate the input field with the right ID instead of the URL.

This is "hackish" at best - and not a solution for all situations. But in my specific scenario - it works .

Now - the interesting fact is that if this CLASS name in the form contains the ID - there MUST be a way to get it . it exists there - therefor something (a hook / filter / variable ) must be there for me to grab - but what and where - I do not know , and still did not find out .

The solution that @biziclop has suggested seems is the a right one - but for some reason it does not work on my code . Maybe some small thing must be changed there . (see update II)

Someone suggested to use backup_sizes instead of _wp_attachment_metadata with the same approach - but still nada .

If anyone has the answers - please post . I can not believe there is no way to get it (especially when it EXISTS on the form code like class-postID :-)

UPDATE II -

@biziclop solution is working great . I missed out the simple fact that my tables do not have the default renaming - hence the NULL array return when using his code "as-is". (that and the stupid idea of trying to progress without the DEBUG mode on :-) )

SO thanks again !

All the above (and below) solutions would work.

One thing I am still curious about is the above mentioned classes in the upload form. If they are generated with the right ID (class-ID) there must be a way to get them with a simple hook/filter .. but I guess that would be for a later time .

Answer 1

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_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 '%"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.jpg
TEST_123-90x671.jpg
TEST_123-180x134.jpg
TEST_123-180x1341-90x67.jpg
TEST_123-180x1341.jpg
TEST_123-500x373.jpg
TEST_123-500x3731-90x67.jpg
TEST_123-500x3731-180x134.jpg
TEST_123-500x3731.jpg
TEST_123.jpg
TEST_1231-90x67.jpg
TEST_1231-180x134.jpg
TEST_1231-500x373.jpg
TEST_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/