0

I have records of data with many columns. One of the columns being date & time. Check the sanpshot for view. Now I want to run a query to display data from particular date to particular date. I am writing following code in php but it doesn't work and actually query doesn't work. Second condition in code when nothing is coming in POST works but not vice versa.

https://i.stack.imgur.com/YNZPg.png

$d1=date("d/m/Y", strtotime($_POST['frm']));
$d2=date("d/m/Y", strtotime($_POST['to']));
$query;

if(empty($_POST['frm'])){
    if(empty($_POST['to'])){
        $query = mysql_query("select * from ordermng where status = '$xno' ", $link);
   }      
}      
else{
    $query = mysql_query("select * from ordermng where (status = '$xno') between date  = '$d1%' and date = '$d2%' ", $link);
}
iatboy
  • 1,295
  • 1
  • 12
  • 19
Abhishek Singh
  • 197
  • 1
  • 3
  • 17
  • `WHERE date BETWEEN '$d1' AND '$d2'`.... but stop using `%`, this isn't a LIKE clause – Mark Baker Dec 26 '14 at 23:55
  • And (strictly speaking) `date` is a reserved word, so shouldn't really be used as a column name.... though MySQL is rather more forgiving about this than the person who's likely to have to maintain your code – Mark Baker Dec 26 '14 at 23:57
  • Alright. Thanks but how do I convert the date time into the format I want to use in php and then use the query? – Abhishek Singh Dec 26 '14 at 23:58
  • And as you're clearly just learning; stop learning bad practises with the old deprecated MySQL library, and switch to MySQLi or PDO with bind variables – Mark Baker Dec 26 '14 at 23:58
  • The date format for SQL queries is `Y-m-d`, not `d/m/Y` – Mark Baker Dec 26 '14 at 23:59
  • http://bobby-tables.com/ - you describe it really correct, it does not work. Please learn about SQL injection so you understand what *foremost* does not work. Next to that extract the SQL and run it with a different SQL cleint (e.g. the SQL textbox in phpmyadmin) until you get the result you need. Phpmyadmin - contrary to your PHP code - does actually tell you about the errors that are happening. – hakre Dec 27 '14 at 00:06

2 Answers2

0

Here is an EG:

SELECT *
FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

With variables:

SELECT *
FROM `objects`
WHERE (date_field BETWEEN '$d1' AND '$d2')

You should be using mysqli anyway though.

Also you might need to convert the ampm to 24 hour. It isn't hard, just google it.

0

Your Query is wrong Try this

select * from ordermng where  (date  between   '$d1%' and  '$d2%') AND  status = '$xno'
Mendi
  • 31
  • 4