Re: SQL syntax

Karl DeSaulniers wrote:

> Hello Everyone,
> Hope your 2013 is treating you well.
> Quick question and apologies if it is a stupid question.
> Is this a viable syntax?
> 
> $sql = "SELECT orderid
> FROM ORDERS_TABLE
> WHERE orderstatus = 'Cancelled' OR (orderstatus = ('New' OR
> 'Denied' OR 'Expired' OR 'Failed' OR 'Pending' OR 'Refunded' OR
> 'Reversed' OR 'Under Review' OR 'Voided') AND orderdate <
> '".mysqli_real_escape_string($yesterday)."')";
> 
> Namely the orderstatus = (a whole bunch of options).
> In my database `orderstatus` field is an enum() list btw.
> Or is there a better way to check multiple options against an enum
> inside your select statement?
> Reason I am doing this is to avoid having to do...
> 
> $sql = "SELECT orderid
> FROM ORDERS_TABLE
> WHERE orderstatus = 'Cancelled' OR (orderstatus = 'New'  AND
> orderdate < '".mysqli_real_escape_string($yesterday)."') OR
> (orderstatus = 'Denied'  AND orderdate <
> '".mysqli_real_escape_string($yesterday)."') OR (orderstatus =
> 'Expired'  AND orderdate <
> '".mysqli_real_escape_string($yesterday)."') ... etc";
> 
> TIA,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com

The best way to check if your SQL query is valid is to feed it to mysql 
which will return an error if the syntax is incorrect :-) So TIAS! Although 
it looks wrong to me.

But you might try the IN operator - something like

OR (orderstatus IN ('New', 'Denied', 'Expired', 'Failed', 'Pending', 
'Refunded', 'Reversed', 'Under Review', 'Voided') AND orderdate < 
'".mysqli_real_escape_string($yesterday)."')

Personally I would have used a lookup table for the order status.
-- 
Cheers
David Robley

A cynic smells flowers and looks for the casket.


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php