Adrian Adrian - 1 month ago 8
MySQL Question

MYSQL Using AND and OR on selecting data from database

i would like to select * data from this table just to display everything according to my filter selected.

My Table:

tbl_reservation

res_id | trans_code | status | order_type | type_of_order | date_ordered
------ | -----------|----------|--------------|-------------------|-------------
1 | 111-111 |Delivered | For Delivery | Online Transaction|2016-10-09
------ | -----------|----------|--------------|-------------------|-------------
2 | 111-112 |Delivered | For Pick-up | Online Transaction|2016-10-09
------ | -----------|----------|--------------|-------------------|-------------
3 | 111-113 |Cancelled | For Delivery | Online Transaction|2016-10-09
------ | -----------|----------|--------------|-------------------|-------------
4 | 111-114 | | For Purchase | Online Transaction|2016-10-09
------ | -----------|----------|--------------|-------------------|-------------
5 | 111-115 | | For Pick-up | Walkin Transaction|2016-10-09
------ | -----------|----------|--------------|-------------------|-------------
6 | 111-116 | | For Purchase | Walkin Transaction|2016-10-09
------ | -----------|----------|--------------|-------------------|-------------
7 | 111-117 |Delivered | For Pick-up | Walin Transaction |2016-10-09
------ | -----------|----------|--------------|-------------------|-------------
8 | 111-118 |Delivered | For Delivery | Online Transaction|2016-10-08
------ | -----------|----------|--------------|-------------------|-------------
9 | 111-119 |Delivered | For Pick-up | Online Transaction|2016-10-08
------ | -----------|----------|--------------|-------------------|-------------
10 | 111-110 |Cancelled | For Delivery | Online Transaction|2016-10-08
------ | -----------|----------|--------------|-------------------|-------------
11 | 111-100 | | For Purchase | Online Transaction|2016-10-08
------ | -----------|----------|--------------|-------------------|-------------
12 | 111-101 | | For Pick-up | Walkin Transaction|2016-10-08
------ | -----------|----------|--------------|-------------------|-------------
13 | 111-102 | | For Purchase | Walkin Transaction|2016-10-08
------ | -----------|----------|--------------|-------------------|-------------
14 | 111-103 |Delivered | For Pick-up | Walin Transaction |2016-10-08


My Form:

<form method="post" action="">
<select name="filter">
<option value="Today">Filter Data for today</option>
<option value="Yesterday">Filter Data for yesterday</option>
</select>
<input type="submit" value="Filter"/>
</form>


MY PHP CODE TOGETHER WITH MY SAMPLE QUERY ON GETTING FILTER

<?php
//inclue db connection

if(isset($_POST["filter"])){
$get_filter = $_POST["filter"];
if($filter == "Today"){
$sql = mysql_query("SELECT * FROM tbl_reservation WHERE status = 'Delivered' OR status = 'Cancelled' OR status = '' AND DATE(date_ordered) = DATE(now()) ");
}elseif($filter == "Yesterday"){
$sql = mysql_query("SELECT * FROM tbl_reservation WHERE status = 'Delivered' OR status = 'Cancelled' OR status = '' AND DATE(date_ordered) = DATE(now())-1 ");
}


//THEN DISPLAY ALL IN VARIABLE USING WHILE LOOP AS mysql_fetch_array
//Display data here
//..
//..
//..
?>


Date Order = 2016-10-09 [For today]

Date order = 2016-10-08 [For yesterday]

There is no error.

When I choose filtering for today..it displays all data together with the data for yesterday

But when I choose for yesterday.. it display all data together with the data for today

What i was hoping is to correct the data query. Can someone help? thank you.

Answer

When you subtract 1 from a date, it does not have the effect you intend: MySql then converts the date to a number and subtracts 1 from that. This will not give the expected result on the first of the month.

So use date_add or date_sub instead of -1, for example:

date_sub(date(now()), interval 1 day)

Secondly, the logical operator and has precedence over or, so when you write this:

WHERE status = 'Delivered' 
   OR status = 'Cancelled' 
   OR status = '' 
  AND date(date_ordered) = date(now())

It is evaluated as:

WHERE status = 'Delivered' 
   OR status = 'Cancelled' 
   OR (status = '' AND date(date_ordered) = date(now()))

... which means the date condition is not playing any role when that status is Delivered or Cancelled.

You can solve this by adding parentheses around the OR part, or you can benefit from the IN operator:

WHERE status IN ('Delivered', 'Cancelled', '')
  AND date(date_ordered) = date(now()))

.. and for the second SQL statement:

WHERE status IN ('Delivered', 'Cancelled', '')
  AND date(date_ordered) = date_sub(date(now()), interval 1 day)