CODEWITHSUNDEEP
phpmysql
d_unknown
d_unknown

Reputation: 875

how to search date in any format in mysql

I have a search form with a dropdown selection which enables user to search by a specific column.Here's my code:

<form action="<?php echo site_url('admin/home/search');?>" method="post" accept-charset="utf-8"> 
    <div class="pull-left" style="margin-left:150px" >  
        <select class="form-control pull-left" name="searchby" id="searchby" >
            <option class="label">- Search by -</option>
            <option value="name">Name</option>
            <option value="address">Address</option>
            <option value="date">Date</option>
        </select> 
    </div>    
    <div class="pull-right" style="margin-left:10px">
        <div class="input-group col-sm-12">
            <input type="text" class="form-control" placeholder="Search " name="key_key" id="key_key" required /> 
            <div class="input-group-btn">
                <button class="btn btn-md" type="submit"><span class="fa fa-search"></span></button>
            </div>
        </div>
    </div> 
</form>

If I search by date and I input March it doesn't give any result. Here's my MySQL statement:

$sql = "SELECT *,date_format(schedule,'%M %e, %Y') as datesched,date_format(schedule,'%h:%i:%s %p') as timesched FROM `event` WHERE schedule LIKE '%".$search_term."%'";

    $query = $this->db->query($sql);

    if($query->num_rows() > 0){
        foreach($query->result() as $row){
            $data[] = $row;
        }
        return $data;
    }else{
        return 0;
    }

the datatype of schedule column is timestamp.

Upvotes: 1

Views: 3581

Answers (3)

Ankit Singh
Ankit Singh

Reputation: 54

  1. String to Date format with str_to_date()
  2. SELECT STR_TO_DATE('12/15/2008', '%m/%d/%Y');
  3. same as your query with these changes

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

You can't just use a string to search on a datetime field.
To do so, you must know in what format does the search string is framed.

If search string is in YYYY-MM-DD format then it matches with database date format. Then instead of like operator you can directly use as below: 

WHERE date_format(schedule, '%Y-%m-%d') LIKE '%".$search_term."%'"

You have to fix the date input format for search string.

If JS and CSS framework you are using supports a CSS class to apply for a text field use it. You better opt for a onChange event for select options and change the datetime type CSS class for the search string input element. That will resolve your issue.

Refer to:

Upvotes: 3

darioguarascio
darioguarascio

Reputation: 1087

Using LIKE in this case is wrong, because you can use ranges, like BETWEEN.

You have to firstly generate the rage you are looking for, let's say March 2014, then have:

$from = '2014-03-01 00:00:00';
$to   = '2014-03-31 23:59:59';

and use them in your query:

$query = "SELECT 
    *,date_format(schedule,'%M %e, %Y') as datesched,
    date_format(schedule,'%h:%i:%s %p') as timesched 
FROM `event` 
WHERE schedule BETWEEN '".$from."' AND '".$to."'";

you can generate a date starting from a string in multiple ways, take a look at http://php.net/manual/en/function.date-parse.php

I personally use http://php.net/manual/en/datetime.createfromformat.php

Upvotes: 0

Related Questions