a tab for a specific action

[Sten]

Hello,

I have an application where we have to call subscribers two months before the end of validity.

So I need a tab which leads to the list of subscribers that have their $date_expiration within 2 months.
I use
__sql__ = "select * from usagers, badges where badges.id_usager='$id' WHERE TO_DAYS(NOW()) - TO_DAYS(badges.date_expiration) <= 60 AND temporaire='0'"

Could you help me ?

thank you

Jean

[shannah]

Hi Jean,
There are a few ways to achieve what you want to do.Ê Before I get into that, I have some questions:
1. The SQL you quote above probably doesn't do what you want.Ê You are joining usagers and badges together but not specifying how they are related.Ê My guess is that you mean it to be something like this:
"select * from usagers u inner join badges b onÊ b.id_usager=u.id WHERE
TO_DAYS(NOW()) - TO_DAYS(b.date_expiration) <= 60 AND
u.temporaire='0'"
2. What are the usagers table and badges tables for?Ê Does the usagers table hold the subscribers; and the badges table hold the specific subscription info (e.g. expiration date, etc..)?
3. What is the nature of the relationship between the usagers table and the badges table?Ê e.g can a usager be related to more than one badge?
I think one of the best ways to achieve what you want it create a calculated field on the usagers table for expiration date.Ê Then you can essentially do the query you want directly against the usagers table.
e.g. Assuming there can be only one badge per usager, you could add the following to the beginning of the fields.ini file for the usagers table:
__sql__ = "select u.*, b.date_expiration from usagers u left join badges b on u.id=b.id_usager"
Important:Ê We use a left join because we need every row from the usagers table to be returned here.
Now even though the date_expiration field won't show up on the find form, you CAN do queries based on date_expiration using URL conventions.
e.g. http://yourdomain.com/yourapp/index.php?-table=usagers&date_expiration=>2006-07-01
Now you can create a link somewhere in your application to this result set matching against any expiration date.
e.g.
$link = DATAFACE_SITE_HREF.'?-action=list&-table=usagers&date_expiration='.urlencode('>'. date('Y-m-d', time()-(60*24*60*60)));
ÊÊÊÊ // Note 60 days * 24 hours * 60 minutes * 60 seconds
Alternatively you could actually create a custom action that essentially does the same thing...
Note that if there could be more than one badge per usagager then you'll need to alter the __sql__ statement in the fields.ini file so that you are only dealing with the most recent expiration date.
e.g.
__sql__ = "select u.*, b.last_exp_date from usagers u left join ( select id_usager, max(date_expiration) as last_exp_date from badges group by id_usager) as b on u.id=b.id_usager"
This is a pretty big and powerful area that deserves some more documentation, but hopefully this is enough to get you started on it.
If you'd rather do the custom action method, I can do some examples of that as well.
-Steve

[Sten]

Thank you Steve for this complete answer,
Sorry I realized I put the wrong sql query, I should have verified. I keep you informed.
Kind regards
Jean