Re: Re: SQL syntax

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