CODEWITHSUNDEEP
phpmysqldate
mhmd
mhmd

Reputation: 254

Search between 2 dates with different format

I have a table with a column name is db_datetime with type is datetime the date is stored like this in the database

enter image description here

for the search i use a date picker the format of this date picker is like this

09/19/2016 M/d/Y

i want to search between two dates and search on the name with a drop down menu and search in character or number i use this code but the problem is with the date i use STR_TO_DATE but it didn't give me a result all my effort didn't success always give me no result for my search and i have a dates stored for what i search this is the code

if(isset($_POST['submit'])) {
    $search=mysqli_real_escape_string($conn,$_POST['txt_search']);
    $q = array();  
    $sql = "";     
    if(isset($_POST['txt_name']) && !empty($_POST['txt_name'])){  
    $name = mysqli_real_escape_string($conn,$_POST['txt_name']); 
    $q[] = " db_name='".$name."' ";      
    }  
    if(isset($_POST['txt_psd']) && !empty($_POST['txt_psd'])){  
    $psd=  mysqli_real_escape_string($conn,$_POST['txt_psd']); 
     $q[] = "STR_TO_DATE($psd, '%Y-%m-%d') like'%db_datetime%' ";    
    }   
    if(isset($_POST['txt_pdd']) && !empty($_POST['txt_pdd'])){  
    $pdd =  mysqli_real_escape_string($conn,$_POST['txt_pdd']); 
    $q[] = "STR_TO_DATE($pdd, '%Y-%m-%d') like'%db_datetime%' ";      
    }     
    $qq=array();
    if(isset($_POST['txt_search']) && !empty($_POST['txt_search'])){  
    $search =  mysqli_real_escape_string($conn,$_POST['txt_search']); 
    $qq[] = "db_name like '%".$search."%' "; 
    $qq[] = "db_datetime like '%".$search."%' "; 
    $qq[] = "db_user like '%".$search."%' "; 
    } 
    $first = true; 
    $second=true;
    foreach($q as $qu){  
        if($first){  
        $sql .= " where ".$qu;      
        $first = false;  
        }else{  
        $sql .= " and ".$qu;          
        }} 
       foreach($qq as $qu){  
        if($second){  
        $sql .= " where  ".$qu;      
        $second = false;  
        }else{  
        $sql .= " or ".$qu;          
        }}  
    $result=mysqli_query($conn,"SELECT * FROM tbl_login {$sql}")or die(mysqli_error($conn));

Upvotes: 0

Views: 95

Answers (2)

Raunak Gupta
Raunak Gupta

Reputation: 10809

You can covert the user input date into Y-m-d format as supported by MySQL in PHP then pass that date into mysql.

Edited

$userInput = '09/19/2016';
$date = DateTime::createFromFormat('m/d/Y', $userInput);
$newDate = $date->format('Y-m-d'); 
echo $newDate; //output will be : 2016-09-19

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157872

Your idea for the STR_TO_DATE function is wrong. Obviously, it needs the input date format, not output (which is already known). So the following should work

STR_TO_DATE('$psd', '%m/%d/%Y') = db_datetime

note that your idea of using LIKE is wrong as well.

Upvotes: 0

Related Questions