RTRIM in a sql statement in wordpress

I'm trying to check a filename in a sql statement, that I'm using in PHP, in a wordpress template I'm building.

Bascially I'm trying to use LTRIM to only bring back images that have _a on the end.

eg I have a bunch of images in a series of folders

• clientone_a.jpg
• clienttwo_b.jpg
• ...etc...

this is what Im using at the moment but it doesnt work;

$query = "
    SELECT F.field_value, P.alttext, P.filename, P.description开发者_开发知识库, p.galleryid, 
        G.path 
    FROM wp_nggcf_field_values F, 
        wp_ngg_pictures P, 
        wp_ngg_gallery G 
    WHERE F.fid=1 
        AND (F.field_value='".$data."') 
        AND F.pid = P.PID 
        AND P.galleryid = G.gid 
        AND LTRIM(P.filename,'_') = '_a.jpg'
";

$caseStudiesResults = $wpdb->get_results($query);

It's just the last LTRIM(P.filename,'_') = '_a.jpg' that is the problem, without that it bring back all the images, and I'm after bringing back the _a one only.

Any ideas on where I'm going wrong ?

Thanks

The problem is that you're using the wrong function. LTRIM() simply removes leading spaces. What you're looking for is SUBSTRING_INDEX()

AND SUBSTRING_INDEX(P.filename, '_', -1) = 'a.jpg'

The -1 tells it to return everything to the right of the final delimiter (-2 would be everything to the right of the 2nd to last delimiter, 1 would be everything to the left of the first, etc)...

Edit: As far as testing it, you can test string functions by doing this:

SELECT SUBSTRING_INDEX('test_a.jpg', '_', -1) = 'a.jpg'

Run that, and see if it works (it did for me). By putting static values, you can try it out without needing to run the whole big query...

alternative to substring_index

select substring('clientone_a.jpg', -6)='_a.jpg';