0

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));
Improve this question
7
  • Re-format the date so it's compatible with the mysql one. I wouldn't be surprised if the date picker didn't have this inbuilt functionality Commented Sep 28, 2016 at 7:10
  • Try something like this: $dte = str_replace('/', '-', $post_data['date']); $dt = new DateTime(); $date = $dt->createFromFormat('d-m-Y', $dte); $date = $date->format('Y-m-d'); I had a similar problem, replace the vars with your's. Commented Sep 28, 2016 at 7:13
  • @James111 how i reformat the date ? Commented Sep 28, 2016 at 7:15
  • @DenisSolakovic not working the input change the date Commented Sep 28, 2016 at 7:20
  • you need to convert the data into that format using strtotime and Date function and use EQUAL TO instead of LIKE for date Commented Sep 28, 2016 at 7:22

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

if(isset($_POST['txt_psd']) && !empty($_POST['txt_psd'])){ $psd= mysqli_real_escape_string($conn,$_POST['txt_psd']); $userInput = $psd; $date = DateTime::createFromFormat('m/d/Y', $userInput); $q[] = "db_datetime=$date"; } i use your code but it give me that Catchable fatal error: Object of class DateTime could not be converted to string
@mhmd: I have updated my code please check it, I have just assign a date into variable and tested it by is_string function which return true. So now you will not get the mentioned error.
that what i have for print the sql where db_datetime=2016-09-19 no result. and this is the code if(isset($_POST['txt_psd']) && !empty($_POST['txt_psd'])){ $psd= mysqli_real_escape_string($conn,$_POST['txt_psd']); $userInput = $psd; $date = DateTime::createFromFormat('m/d/Y', $userInput); $newDate = $date->format('Y-m-d'); echo $newDate; $q[]="db_datetime=$newDate";
Replace the last line with this $q[]="db_datetime='$newDate'"; you have missed single quote.
this what i have where db_datetime='2016-09-19'no result.
|
0

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.

Improve this answer

7 Comments

didn't work i try your solution and this solution STR_TO_DATE('$psd', '%Y/%m/%d') = db_datetime and this solution STR_TO_DATE('$psd', '%Y-%M-%d') = db_datetime
why on the earth would you try a '%Y-%M-%d' format? How it's ever related to your 09/19/2016 date?!
it should be lower "m" by the way.
on the database it look like that
But you you are converting not your database value but input value.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.