phpmyadmin Question posted in
The official documentation can be found here.

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

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

in lower case: duet results

See results when I search using Duet

in Duet: results

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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%'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search