Query more than one table for due date

[andydeans]

hi,

i had a post in php re display a countdown for policy, it got side tracked to inserting the date properly into mysql database. that is now resolved.

what i have is a database setup like this, and if anyone could tell me if this is wrong design before i go any further i would appreciate as i dont want to come across issues later on.

DB:

Users Table
UserID
Username
Password

Client Table
ClientID
FirstName
LastName
DOB
Clients_UserID FK to User Table

Insurance Table
PolicyNumber
Provider
DueDate
DatePaid
Archived
Clients_CliendID FK to Clients Table

Mortgage Table
Account Number
Lender
ReviewDate
DatePaid
Archived
Clients_clientID FK to Clients Table

I want to have on my tasks page a list of all clients due date that week to current date display and not sure how to query the database to do this?

Also want to have on the other side of the page a list of all clients with their due date from all products that are over their due date and remain there until dealt with.

I have an archived column in the tables which is set to 1 when added to show as current but 0 when they archive the product so i dont want the archived products to show.

I am new to MYSQL so hope someone can help, i have read around a bit and looked at : http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub

I create a Recordset on my page using Dreamweaver CS3 PHP/MYSQL.

Current Attempt display from Mortgage Table when the due date = today but do not know how to take it to my next step:

SELECT clients.*, mortgage.*
FROM (clients INNER JOIN mortgage ON mortgage.clients_ClientID=clients.ClientID)
WHERE ReviewDate=CURRENT_DATE()

Many thanks

[rch1231]

Hello,

I think what you are looking for is a way to calculate today plus or minus X number of days. If so this should help:

If you have a reference date and want to calculate another date from it that differs by a known interval, the problem generally can be solved by basic date arithmetic using DATE_ADD( ) and DATE_SUB( ).

The following is an example of calculating new dates from a date based on intervals:

mysql> SET @d = '2003-08-06';
mysql> SELECT @d AS 'start date',
-> DATE_ADD(@d,INTERVAL 7 DAY) AS '1 week',
-> DATE_ADD(@d,INTERVAL 1 MONTH) AS '1 month',
-> DATE_ADD(@d,INTERVAL 3 MONTH) AS '3 months',
-> DATE_ADD(@d,INTERVAL 6 MONTH) AS '6 months';
+------------+------------+------------+------------+------------+
| start date | 1 week | 1 month | 3 months | 6 months |
+------------+------------+------------+------------+------------+
| 2003-08-06 | 2003-08-13 | 2003-09-06 | 2003-11-06 | 2004-02-06 |
+------------+------------+------------+------------+------------+

I think there is more to what you want to do just increment the date but I think this will head you in the right direction. If yo can be a little more specific on the result we may have some oter ideas.

[andydeans]

thanks for your response.

how would i fit this into my current attempt?

the idea is that i want to display all the due dates that week from my tables and all the ones past their due dates display for that table.

thank you again

[rch1231]

thanks for your response.

how would i fit this into my current attempt?

the idea is that i want to display all the due dates that week from my tables and all the ones past their due dates display for that table.

thank you again

OK. I am making a couple of assumptions:
1) Your running this on Monday for the upcoming week.
2) You want all records from Insurance table and Mortgage table where the Due date is from Monday through the next Sunday. And you want the info from the clients table joined to the Insurance and Mortgage tables output.

I see two main queries:
One each for Insurance and Mortgage that are joined to clients. One to get this weeks due and the unpaid for each of the tables.
The Insurance table query could look something like this:

SELECT 
`Insurance Table`.`PolicyNumber`
`Insurance Table`.`Provider`
`Insurance Table`.`DueDate`
`Insurance Table`.`DatePaid`
`Insurance Table`.`Archived`
`Client Table`.`ClientID`
`Client Table`.`FirstName`
`Client Table`.`LastName`
`Client Table`.`DOB`
FROM `Insurance Table` INNER JOIN `Clients Table` ON `Insurance Table`.`Clients_clientID` = `Clients Table`.`Clients_clientID`
WHERE (`Insurance Table`.`DueDate` >= CURRENT_DATE()
AND `Insurance Table`.`DueDate` <= (DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY))
) OR (
`Insurance Table`.`DueDate` <= CURRENT_DATE()
AND 
(`Insurance Table`.`DatePaid` is null 
OR `Insurance Table`.`DatePaid`= '') )

Does that make sense?

[andydeans]

OK. I am making a couple of assumptions:
1) Your running this on Monday for the upcoming week.
2) You want all records from Insurance table and Mortgage table where the Due date is from Monday through the next Sunday. And you want the info from the clients table joined to the Insurance and Mortgage tables output.

I see two main queries:
One each for Insurance and Mortgage that are joined to clients. One to get this weeks due and the unpaid for each of the tables.
The Insurance table query could look something like this:

SELECT 
`Insurance Table`.`PolicyNumber`
`Insurance Table`.`Provider`
`Insurance Table`.`DueDate`
`Insurance Table`.`DatePaid`
`Insurance Table`.`Archived`
`Client Table`.`ClientID`
`Client Table`.`FirstName`
`Client Table`.`LastName`
`Client Table`.`DOB`
FROM `Insurance Table` INNER JOIN `Clients Table` ON `Insurance Table`.`Clients_clientID` = `Clients Table`.`Clients_clientID`
WHERE (`Insurance Table`.`DueDate` >= CURRENT_DATE()
AND `Insurance Table`.`DueDate` <= (DATE_ADD(CURRENT_DATE(),INTERVAL 7 DAY))
) OR (
`Insurance Table`.`DueDate` <= CURRENT_DATE()
AND 
(`Insurance Table`.`DatePaid` is null 
OR `Insurance Table`.`DatePaid`= '') )

Does that make sense?

Thanks this does make sense and i will give it a go and post back. thanks for your help, on thing though i am not getting me head around is:

I setup my MYSQL db with InnoDB and foreign Keys between the tables, however I end up using Inner Join in my queries in Dreamweaver for my recordsets and that seems to be the advice always to use Joins, why do i need to use joins if i have a FK?

Sorry if that seems dumb question.

thanks again

[andydeans]

thansk so much for this, this has helped me alot.

i take it for each product i should just change the table name? should it be in the one query?

many thanks