How to use opposite of BETWEEN in a MySQL query?

Question

I have a working query which used "BETWEEN" to find results within a date range, what i need is just opposite result or not between the given date range. Here is the query below.

SELECT loanac.id, loanac.name, loanac.lacc, loanac.phone, 
SUM(loantrans.in) as totalin, SUM(loantrans.out) as totalout 

FROM loanac, loantrans 
WHERE (loanac.lacc=loantrans.account) 
AND (`loantrans`.`date` BETWEEN CAST('$range' AS DATE) AND CAST('$date' AS DATE))

GROUP BY loanac.lacc
HAVING SUM(IFNULL(`loantrans`.`out`,0)) > SUM(IFNULL(`loantrans`.`in`,0))

Can anyone help me finding opposite result or result of not between the date range having the same conditions?

Details Screenshots here: https://www.dropbox.com/sh/lgmop3zxqaf9mjy/AAAWVWjZXd8da9KDDOqz2ANla?dl=0

Answer 1

The only thing I can think of is that, somehow, the expressions that define the date interval are returning a time section (those strtotime() look like the culprits). This answer deals with two options (option 2 is the good one) when the date includes a time part.

In your specific case, I think this would be the best approach to solve the issue:

SELECT loanac.id, loanac.name, loanac.lacc, loanac.phone
     , SUM(loantrans.in) as totalin, SUM(loantrans.out) as totalout 
FROM loanac
     INNER JOIN loantrans on loanac.lacc = loantrans.account
-- Instead of an implicit join in the WHERE clause, use an explicit INNER JOIN
WHERE date(loantrans.date) < date('$range')  
  AND date(loantrans.date) > date('$date')
GROUP BY loanac.lacc
HAVING SUM(IFNULL(`loantrans`.`out`,0)) > SUM(IFNULL(`loantrans`.`in`,0))

Notice that the date() function "removes" the time section of the value.

---

One more thing: Your code may be vulnerable to SQL Injection attacks. Please take a look here for a (humorous) example on what it is, and tips on how to deal with it.

Answer 2

Given that your $date and $range are simply "now" and "6 days ago", there is NO reason to be generating the dates in PHP. Why not simply

WHERE datefield BETWEEN (curdate() - INTERVAL 6 DAY) AND curdate()
a.k.a.
WHERE datefield BETWEEN '2014-09-19' AND '2014-09-25'
a.k.a
WHERE (datefield >= '2014-09-19') AND (datefield <= '2014-09-25')

for which the opposite (NOT) would be

WHERE (datefield > curdate()) OR (datefield < (curdate() - INTERVAL 6 DAY))
a.k.a
WHERE (datefield > '2014-09-25') OR (datefield < '2014-09-19')