Re: Re: SQL syntax

Try It And See

Bastien Koert

On 2013-01-15, at 7:43 AM, Karl DeSaulniers <[email protected]> wrote:

> 
> On Jan 15, 2013, at 5:31 AM, David Robley wrote:
> 
>> 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
> 
> 
> Thanks for the suggestion. Your the second to offer the IN as a solution.
> I will try that out. I currently have enough tables and don't want to create a separate table for the status.
> BTW what is TIAS... havent seen that one.. lol
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

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