user580950 user580950 - 1 year ago 56
SQL Question

Mysql Select Query without PHP

I have written following select query which returns the required records.I know how to code this in PHP. I am wondering if it is possible in one single select mysql query

SELECT annual_end_date
FROM Users
WHERE email = ''
AND password = 'password'

On the same table I would also like to check (psuedo code PHP below), return expiry_id=0 records and
is a
Column is less
then current date

If expiry_id='0' THEN
check annual_end_date column
If annual_end_date is less then the current date

If expiry_id is not equal to '0' it should check
subscription table
and compare

If expiry_id!='0'
check pay_end_date column in subscription table
If pay_end_date is less then the current date then show records

Subscription table has just one field
with is a
date column

Answer Source

You can add something like this to your query...

 AND (  ( expiry_id='0' AND annual_end_date < DATE(NOW()) )
     OR ( expiry_id<>'0' AND EXISTS ( SELECT 1
                                        FROM subscription s
                                       WHERE s.user_id = Users.user_id -- ?
                                         AND s.pay_end_date < DATE(NOW())

The specification says to check the "subscription table", but no indication is given about the relationship between subscription and Users. The subquery is based on a guess about the foreign key column in subscription, and the referenced column in Users.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download