I am working on a contract management application and have created 2 database tables - one holds information on contracts and the other holds information on contract amendments. I want to create a chronological timetable of each contract which will take all relevant dates from a contract and its amendments and sort them chronologically.
e.g. a contract has an effective date and termination date. However, this contract has two amendments, each of which renew the effective date and change the termination date. In order to display the life cycle of the contract
I need to thus display the information like this:
original_effective_date -----> amendment1_effective_date -----> original_termination_date ------> amendment2_effective_date -----> amendment1_termination_date ------> amendment2_termination_date
There are other dates important like the notice deadline for automatically renewing contracts, but this is the most basic example I can think of.
My problem is with the amendments table. Whereas I can take information about the original contract from the contracts table without any problem, I don't necessarily know how many amendments are in the amendments table (I've linked the amendments to the original contract using a amendment_table.original_contract_id field which means that all amendments to the same contract have the same value for the field original_contract_id).
I'm not sure how to best get the data I need from the amendments table. For each amendment there is going to be an effective date and a termination date. I've tried using mysql_fetch_object and using arrays but that doesn't seem to be working
(e.g.
$pick_dates = "SELECT * FROM {$db_tablename}_amendments WHERE amendment_original_contract_cmapp_index = '{$amendment_original_contract_cmapp_index_wert}'";
$exec_pick_dates = mysql_query($pick_dates) or die (mysql_error());
$how_many = mysql_num_rows($exec_pick_dates);
$i=1;
while($i <= $how_many)
{
$choose_amendment = "SELECT * FROM {$db_tablename}_amendments WHERE amendment_original_contract_cmapp_index = '{$amendment_original_contract_cmapp_index_wert}' AND amendment_number = '{$i}'";
$exec_choose = mysql_query($choose_amendment) or die (mysql_error());
while($unique_recordset=mysql_fetch_object($exec_choose))
{
$retrieve_effective_date[$i] = $unique_recordset -> amendment_effective_date;
$retrieve_termination_date[$i] = $unique_recordset -> amendment_termination_date;
$retrieve_survival_period[$i] = $unique_recordset -> amendment_survival_period;
}
What I'd like to know is if there's an easier way of going about this
- i.e. of somehow getting all the dates together in an array and then sorting the array.
