[PHP-DB] SQL syntax
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
Re: [PHP-DB] SQL syntax
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus IN ( 'Cancelled', 'New', 'Denied',
'Expired' , 'Failed' , 'Pending' , 'Refunded' , 'Reversed' , 'Under Review'
, 'Voided') AND orderdate '.mysqli_real_escape_string($
yesterday);
Another option would be to use either of these functions
-
Find-in-sethttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set.
This is useful if your data type is SET/ENUM type
-
Fieldhttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
On Tue, Jan 15, 2013 at 2:59 PM, Karl DeSaulniers k...@designdrumm.comwrote:
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).');
regds
amit
The difference between fiction and reality? Fiction has to make sense.
[PHP-DB] 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
Re: [PHP-DB] SQL syntax
On Jan 15, 2013, at 5:25 AM, Amit Tandon wrote:
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus IN ( 'Cancelled', 'New', 'Denied',
'Expired' , 'Failed' , 'Pending' , 'Refunded' , 'Reversed' , 'Under
Review'
, 'Voided') AND orderdate '.mysqli_real_escape_string($
yesterday);
Another option would be to use either of these functions
- Find-in-sethttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
.
This is useful if your data type is SET/ENUM type
- Fieldhttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
On Tue, Jan 15, 2013 at 2:59 PM, Karl DeSaulniers k...@designdrumm.com
wrote:
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).');
regds
amit
The difference between fiction and reality? Fiction has to make
sense.
I am wanting Cancelled to be without a date check, but thanks for the
suggestion.
I will try the IN option.
Thank you.
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
Re: [PHP-DB] 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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: SQL syntax
Try It And See
Bastien Koert
On 2013-01-15, at 7:43 AM, Karl DeSaulniers k...@designdrumm.com 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
Re: [PHP-DB] Re: SQL syntax
True, I guess it never hurts to try. Busy making orders and hadn't
tested just yet.
I just wanted a professional opinion on if the syntax was common to
use, mostly.
Didn't want to get a Jr PHP job and use it and have everyone looking
at me sideways.. :P
Thanks all,
Best,
Karl
On Jan 15, 2013, at 6:47 AM, Bastien wrote:
Try It And See
Bastien Koert
On 2013-01-15, at 7:43 AM, Karl DeSaulniers k...@designdrumm.com
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
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-DB] PDO ?
Doing some conversion - looking for a solution.
Currently I do something like this:
while (list($var1,$var2) = mysql_fetch_array($qrslts))
{
handle the vars
}
Is there something in the PDO functions that emulates this same ability?
Some of my uses of the sql syntax have many more vars and I'm
trying to avoid having to move the fetch-ed fields one at a time to vars
that I wish to use.
I already tried:
list($var1,$var2) = $qrslts-fetch(PDO::FETCH_ASSOC);
this does not populate the vars.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: SQL syntax
Or is there a better way to check multiple options against an enum inside
your select statement?
IN
for example: orderstatus IN ( 'Cancelled', 'New', 'Denied', 'Expired')
However this is not exactly what you are doing. You want different orderdate
$yesterday depending on the orderstatus. A better way might be:
$sql = SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus = 'Cancelled'
OR ( orderstatus IN ('New', 'Denied', 'Expired') AND
orderdate '.mysqli_real_escape_string($yesterday).')
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
Re: [PHP-DB] PDO ?
On 15. 1. 2013 18:25, Jim Giner wrote:
Doing some conversion - looking for a solution.
Currently I do something like this:
while (list($var1,$var2) = mysql_fetch_array($qrslts))
{
handle the vars
}
Is there something in the PDO functions that emulates this same
ability? Some of my uses of the sql syntax have many more vars and I'm
trying to avoid having to move the fetch-ed fields one at a time to
vars that I wish to use.
I already tried:
list($var1,$var2) = $qrslts-fetch(PDO::FETCH_ASSOC);
this does not populate the vars.
Hi,
using PDO::FETCH_NUM instead PDO::FETCH_ASSOC should do the job.
Kapu
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: PDO ?
Never Mind A little careful re-reading of the docs told me what I was doing wrong. I always use PDO::FETCH_ASSOC in my fetch statements. Discovered that I have to have numerical array results in order to utilize the List command. Voila! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
