Check if something has completed by a date

I'm planning to have a PHP/MySQL setup. Basically, I was wondering what is the best way to approach this, or a more efficient way of approaching this.

I was thinking of getting t开发者_如何学编程he current month (PHP), comparing it to recent payments via IF statement. Those who haven't paid it will echo the complex numbers who haven't paid their total amount.

I will use a while loop to add up total for an apartment complex, and then compare that to rent_total. If the total_paid is less than the rent_total it will display as not paid.

apt_payments

ID (INT)

resident_name (VARCHAR)

complex_number (SMALLINT)

amount (DECIMAL [10,2])

payment_date (DATETIME)

apt_complex

ID (INT)

complex_number (SMALLINT)

type (VARCHAR)

start_lease (DATE)

end_lease (DATE)

rent_total (DECIMAL [10,2])

vacant (INT)

---

Untested, but:

SELECT cplx.*, amtPaid
FROM apt_complex cplx
LEFT JOIN (
     SELECT sum(amount) as amtPaid, 
            year(payment_date) as payYear, 
            month(payment_date) as payMonth, 
            complex_number 
     FROM apt_payments
     GROUP BY complex_number, year(payment_date), month(payment_date)
) payments ON payYear = [YEAR] AND payMonth = [MONTH] 
            AND payments.complex_number = cplx.complex_number

I think something like that will give you a list of all apartments and the amount they've paid that month. You can loop through the result in PHP and decide to print whatever you want depending on the values amtPaid and rent_total.

You can also put a CASE statement in the query and check for payment there. Then the query can simply return paid or unpaid. [YEAR] and [MONTH] should be replaced by date calls in MySQL or have PHP put the month/year into the query. I didn't want to assume what month you'd be looking at.