Jump to content
Paul-D

Using define() in an SQL

Recommended Posts

I replaced

$sqlMonthlySpend = "SELECT * FROM Bank_Data WHERE  (Reason = 1 or Reason = 10 or Reason = 11 or reason = 12  or reason = 15 or reason = 16) AND EntryDate > DATE_ADD( CURDATE() ,INTERVAL -30 DAY) AND EntryDate <= DATE_ADD( CURDATE() ,INTERVAL 0 DAY) ORDER BY EntryDate";
 

with this

$sqlMonthlySpend = "SELECT * FROM Bank_Data WHERE  (Reason = OTHER or Reason = SHOPS or Reason = ONLINE or reason = CASH  or reason = EBAY or reason = ARGOS) AND EntryDate > DATE_ADD( CURDATE() ,INTERVAL -30 DAY) AND EntryDate <= DATE_ADD( CURDATE() ,INTERVAL 0 DAY) ORDER BY EntryDate";
 

But I get a mysql_query error.

Share this post


Link to post
Share on other sites

Literal strings, such as OTHER need to be in single quotes eg 'OTHER'

Rather then a list of separate or conditions it is a lot easier to use IN (...)

Why use DATE_ADD for a 0 interval?

Don't use SELECT star. Specify the columns you need.

Is "reason" an ENUM type column?

SELECT * 
FROM Bank_Data 
WHERE Reason IN ('OTHER', 'SHOPS', 'ONLINE', 'CASH', 'EBAY', 'ARGOS')
AND EntryDate > CURDATE()- INTERVAL 30 DAY 
AND EntryDate <= CURDATE()
ORDER BY EntryDate

 

Share this post


Link to post
Share on other sites

I have just re-read your topic title. Does it imply that you have
 

define('OTHER', 1);
define('SHOPS', 2)
etc;

If that is the case you cannot drop constants into the strings as you can with variables, you need to concatenate. (The quotes would then be unnecessary as the constants will be replaced by numeric expressions)

"... WHERE Reason IN (" . OTHER . "," . SHOPS . "," .ONLINE . ") AND ...."

It's a PITA.

You could define the Reason column as ENUM, then my previous code would work, specifying oll the string values in their numeric order)

reason ENUM('OTHER', 'REASON2', 'REASON3', ... , 'REASON14', 'EBAY', 'ARGOS')

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.