Search for attachment ID with URL (Case Sensitive)

Question

I am trying to import products images and have their URLs in the sheet. Now there are 2 images with the same name but one have uppercase letters and other have lower case letters. For example, the image name is ABC.jpg and other image name is abc.jpg. When I run my query it is returning both the records.

Here is my Query:

  $wpdb->get_results(
        $wpdb->prepare(
        "SELECT ID as post_id FROM $wpdb->posts WHERE post_type='attachment' AND guid='%s'", $product_img 
  ));

I tried by using the collation but that is also returning me same results.

How do I get this fixed?

Answer 1

You can use the BINARY keyword to make a comparison case sensitive.

$wpdb->get_results(
        $wpdb->prepare(
        "SELECT ID as post_id FROM $wpdb->posts WHERE post_type='attachment' AND BINARY guid='%s'", $product_img 
  ));

Note that this may affect indexes on the column involved.

Example showing that it works:

mysql> select 'ABC.jpg'='abc.jpg';
+---------------------+
| 'ABC.jpg'='abc.jpg' |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select BINARY 'ABC.jpg'='abc.jpg';
+----------------------------+
| BINARY 'ABC.jpg'='abc.jpg' |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.01 sec)

mysql> 

The other option is to change the collation of the column involved to make it case sensitive. See this answer.

Case-sensitivity documentation is here.