php search date string

Question

ive got few client list with DOB format yyyy-mm-dd.
how do i search client by their Date of birth, even if i enter either year or month or date first, it will show the result.

at the moment, i need to use format yyyy-mm-dd to search them.

$result = $db->query("SELECT f_name, l_name, dob, email FROM user WHERE dob LIKE'$keyword%' OR f_name LIKE '$keyword%' OR l_name LIKE '$keyword%' LIMIT 100")

if($result) {
  if($db->affected_rows()!= 0){

     // echo the result

  }
}

Not entirely sure what you're asking here. Can you give us a few examples for what you'll be searching for? — Turbotoast
– Turbotoast, Commented Dec 13, 2011 at 12:32
A side-note: usually, affected_rows returns the rows affected by an INSERT, DELETE, REPLACE or UPDATE statement. Not from SELECT, though. — Linus Kleen
– Linus Kleen, Commented Dec 13, 2011 at 12:38
Are these dates stored as YYYY-MM-DD VARCHAR/CHAR fields, or as DATE fields? Also, am I right in thinking that what you're asking is to input the date as a keyword in any arbitrary format, like "DD/MM/YYYY" or "MM/DD/YYYY"? — Wooble
– Wooble, Commented Dec 13, 2011 at 12:43
@Wooble its date field. thats correct. any idea how to search the DOB even if the format is different? — tonoslfx
– tonoslfx, Commented Dec 13, 2011 at 12:45

Michael Berkowski · Accepted Answer · 2011-12-13 12:52:45Z

2

Rather than LIKE, you can use YEAR(), MONTH(), DAY() to compare against. Be sure you've properly escaped $keyword already...

$result = $db->query("
  SELECT f_name, l_name, dob, email
  FROM user 
  WHERE
    // The year, month, or day can be an exact match (note parentheses)
    (YEAR(dob) = '$keyword' OR MONTH(dob) = '$keyword' OR DAY(dob) = '$keyword')
    // Or the full yyyy-mm-dd date can be an exact match
    OR DATE(dob) = '$keyword'
    // OR UK date format
    OR DATE(dob) = STR_TO_DATE('$keyword', '%d/%m/%y')
    // or the rest of your conditions...
    OR f_name LIKE '$keyword%' OR l_name LIKE '$keyword%' LIMIT 100"
);

EDIT: added quotes around integer date components, to avoid syntax errors for when strings rather than numbers are searched.

edited Dec 13, 2011 at 12:52

answered Dec 13, 2011 at 12:33

Michael Berkowski

271k47 gold badges451 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tonoslfx Over a year ago

thanks youre really helpful :). i forgot somthing to ask in the question. is it possible to search DOB with UK format (dd/mm/yyyy) even though the date format in database is (yyyy-mm-dd)? how do i do that :)

Michael Berkowski Over a year ago

@boyee007 Maybe via STR_TO_DATE(). See addition above. I'm not sure if the function call will fail if the input doesn't match the format dd/mm/yy.

Timur · Accepted Answer · 2011-12-13 13:01:55Z

1

In my opinion, it would be better to normalize date in PHP:

$date = strtotime($keyword);
$result = $db->query('
  SELECT f_name, l_name, dob, email
  FROM user 
  WHERE
    f_name LIKE "$keyword%"
    OR l_name LIKE "$keyword%"
    '.($date&&$date!=-1?'OR dob="'.date('Y-m-d',$date).'"':'').'
  LIMIT 100
');

answered Dec 13, 2011 at 13:01

Timur

6,7381 gold badge30 silver badges37 bronze badges

Collectives™ on Stack Overflow

php search date string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related