mysql table settings

[samowns]

hello guyz am newbie in mysql php..i want if any user register. in my admin area there is reminder of fees .like id 1 simran join date 7-10-2015 after 30 days in my admin area reminds simran's fees is pending..any one plz help me ...thnxx in advance

 

here is table name users

Edited November 12, 2015 by samowns

[Psycho]

Not enough information to help:

 

Are these one-time fees? If not, you will need to track when fees are paid separately from the joined date. In fact, it should be stored in a separate table so you can maintain a history. Even if the fees are a one-time event, there's nothing in your current table to know if fees have been paid or not. And, unless fees are required to be paid in full at one time, you would still want a separate table to track them. Only if fees are paid once (non renewed) and in full at once, you will need a separate table. And, even if it is all at once and non-recurring, you will need an additional field in the existing table to track when they are paid. So, some database changes will be needed.

 

Then, how are you wanting these reminders to occur? Do you just want a reminder through the UI - either the user sees it when logging in, a management page, etc. Or, do you want a proactive notification such as an email? The former just requires a DB query to get the requisite data and display the appropriate content. But, the latter would require a scheduled even to check for payments that are older than a specified period and initiate an email or emails.

[samowns]

sir i use tigger for updating fees...i jus want in my admiin area to flash those users with there pending fees. suposse id 1 simran..join date 7-10-2015

after 7-11-205 auto reminder in admin area 'id 1 simran fees is pending'

[Psycho]

You didn't answer my questions, so why should I take the time to answer yours? I don't see how the joined date can be used to know if a user has paid their fees (even though the field doesn't even state that it is a joined date). That date only tells you, presumably, when they joined. So, what data are you storing to know when a user has paid the fees? Are you reducing the fees field when they make payment? That's a poor way to do it since you would have no history of exactly when they were paid and how much they paid.

 

But, making a HUGE assumption that the fees field is the outstanding fees (which is a terrible process), this query would get you the list of users who have an outstanding fee and their joined date was > 30 days ago.

 

SELECT id, fname, name, fee, `date`
 
FROM users
 
WHERE fees > 0
 
  AND date < CURDATE() - INTERVAL 30 DAY

 

Or, if you want ALL the users to display in a list and show specific notices next to the ones who's fee is pending, you could either add that logic to the output processing OR you could add a dynamic field to the query result.

 

SELECT *,
       (`date` < CURDATE() - INTERVAL 30 DAY and fees > 0) as fee_due
 
FROM `users`

 

But, if you are doing as I suspect, it is a poor implementation.

[samowns]

i got ur point sir...wot should i do ..u i need one table more? name fees history...plz help i got your point sir..

[samowns]

tnx bro i can implement ur code <3

[Psycho]

i got ur point sir...wot should i do ..u i need one table more? name fees history...plz help i got your point sir..

 

I can't give you any advice as you still have not answered my previous questions.

 

Are the fees a one-time event or will there be recurring fees: monthly, yearly, etc?

Are the fees to be paid in their entirety when paid or can they be paid partially?

[samowns]

sir fees based on monthly charges....

[samowns]

query runs f9 but sir instead of 1 n 0. i want if 1 fees pending .if 0 no fees pending

Edited November 13, 2015 by samowns