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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

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.