CODEWITHSUNDEEP
phpmysqlwordpress
Shawn
Shawn

Reputation: 1675

get data using a lower or upper case using mysql like query?

I want to search for data from the database using MySQL like query

here is my query:

global $wpdb;

if($_GET['search']){
    $search = $_GET['search'];
}
else{
    $search = $_GET['searchtop'];
}

$search_details = $wpdb->get_results(
   $wpdb->prepare(
        "SELECT * FROM 
               tbl_brand,
               tbl_generic,
               tbl_therapeutic_area,
               tbl_drug 
         WHERE 
               tbl_brand.generic_id = tbl_generic.generic_id 
         AND 
               tbl_brand.therapeutic_area_id = 
               tbl_therapeutic_area.therapeutic_area_id 
         AND 
               tbl_brand.brand_id = tbl_drug.brand_id 
         AND 
               tbl_brand.brand_name LIKE '%$search%'",""
        ),ARRAY_A
    );

My data is like DUET {upper case letter} in database I search using lower case duet It returns no results found But when I search Duet its found.

See results when I search using duet

enter image description here

See results when I search using Duet

enter image description here

And If I use this SQL query manually in phpmyadmin its result found.

So that reason I want to get data using a lower or upper case!

Upvotes: 3

Views: 2445

Answers (2)

Shawn
Shawn

Reputation: 1675

I am using properly prepare query like:

 $wpdb->prepare(
        "SELECT * FROM tbl_brand,tbl_generic,tbl_therapeutic_area,tbl_drug 
        WHERE tbl_brand.generic_id = tbl_generic.generic_id 
        AND tbl_brand.therapeutic_area_id = tbl_therapeutic_area.therapeutic_area_id AND tbl_brand.brand_id = tbl_drug.brand_id AND LOWER(tbl_brand.brand_name) LIKE %s",'%' . $wpdb->esc_like($search) . '%',""
    ),ARRAY_A

And Its works.

Upvotes: 3

Dhananjay Yadav
Dhananjay Yadav

Reputation: 273

Convert your search string in lower case

$search=strtolower($search);

Then use LOWER() function to convert your field value in lower case and query like this :

AND LOWER(tbl_brand.brand_name) LIKE '%$search%'

Upvotes: -1

Related Questions