Conditional where statements?

I currently have the following in the Where SQL section which lists records if the current date is less than or equal to the expiration date.

CURDATE()<=expires

However, there are a few records that have no expiration date because the person is a life member. The word "Life" is in a category called item_name. If I use the following code the search function no longer works (though all the records still display).

CURDATE()<=expires OR item_name = 'Life'

If I remove the item_name portion of the query, the search works fine (which I have set to search on first name, last name and item name). I have also tried using expires = "" but have the same results.

Can ChronoConnectivity support conditional where statements and if so, can you direct me to a post (I tried a search but did not come up with anything) or direct me as to how I would write it?

Thanks.

Hi chriso0258,

You are building a MySQL query here (or at lease the WHERE clause part of one). I'd just open up PHPMyAdmin and test the query there to see what errors show up. You'll need to use

SELECT FROM `#__table_name` WHERE . . .  ;

The code you have looks like good MySQL to me though you might find you need to reverse the order of the sub-clauses to read

`item_name` = 'Life' OR CURDATE() <= {expires} 

That way the date test won't be run if the 'Life' one is OK.

Bob

Hi chriso,

I used conditional statements before and they worked with the search, the FAQs page here is using AND, but I suggest that you enable the debug of the connection, run a search and show me the SQL portion of the debug data.

Regards,
Max

I made a quick test, you get all records because the WHERE is not inside clauses, change your query from:

`item_name` = 'Life' OR CURDATE() <= {expires}

To

(`item_name` = 'Life' OR CURDATE() <= {expires})

That should work fine, I may add this to the core later, but should make sure first that it will not cause problems in other scenarios🙂

Thanks for the help. I'll have to do some additional troubleshooting. I tried your code and it didn't work so I thought I would just try a query on the item_name = 'Life' and nothing pulled up. So, I must have something wrong elsewhere. When I get the query for item_name correct, I'm sure it will work with the other.

Many thanks.

Thanks for the help. I'll have to do some additional troubleshooting. I tried your code and it didn't work so I thought I would just try a query on the item_name = 'Life' and nothing pulled up. So, I must have something wrong elsewhere. When I get the query for item_name correct, I'm sure it will work with the other.

Many thanks.

Turns out I had an extra space before the word Life in my database. 😶

Max's code worked fine then 😀 with minor change:

(item_name = 'Life' OR CURDATE() <= expires)