display countdown for policy

[andydeans]

hi,

i have designed my clients site, and currently you can login and add a client, i then have a few tables which are policies that are sold to the client.

i have created a foreign key between the policy table and the clients table.

what i want to do is display the the date it is up for renewal on a php page, but display it a week before, a month before and 3 months before.

could anyone help me with the php/sql code i would use to get this data displayed?

i assume i would get the date from the server and then do a count or something?

many thanks

[Jerail]

There's probably more than one way of going about this, but what I would do is just take the current time, add (1 week, 1 month, 3 months) to it, and search the database, with the calculated date as one of the conditions.

[andydeans]

There's probably more than one way of going about this, but what I would do is just take the current time, add (1 week, 1 month, 3 months) to it, and search the database, with the calculated date as one of the conditions.

thanks for your help, could you help me with the correct code i would need? php/sql to do this and display on web page?

each user will have their own clients to have count downs for.

thanks again

[Jerail]

Well, it's hard to say without knowing exactly what's in your database, but I'm assuming one of rows has a date stamp in it?
Recreate the date stamp whenever someone logs in, and add the time to it (i.e. 1 week, 1 month, or 3 months). This would work best with using the date() function in combination with the mktime() function.
Go to http://php.net/manual/en/function.mktime.php to see some examples of how it's used.

Following that, you could search through the date row of your database for that date.
Do you want to show it only for one day, for a certain amount of time, or all the time until the policy is renewed?
You may have to create start and end dates, and search for all dates that fall within that range. Or you might just define a start date, and search for all policies that are past that date.

[andydeans]

Well, it's hard to say without knowing exactly what's in your database, but I'm assuming one of rows has a date stamp in it?
Recreate the date stamp whenever someone logs in, and add the time to it (i.e. 1 week, 1 month, or 3 months). This would work best with using the date() function in combination with the mktime() function.
Go to http://php.net/manual/en/function.mktime.php to see some examples of how it's used.

Following that, you could search through the date row of your database for that date.
Do you want to show it only for one day, for a certain amount of time, or all the time until the policy is renewed?
You may have to create start and end dates, and search for all dates that fall within that range. Or you might just define a start date, and search for all policies that are past that date.

hi thanks for the reply, i would want it to stay shown until the policy was renewed if possible, i may have designed column wrong in mysql database.

i originally had the date field for "startdate,renewdate" however when using a date picker jquery script it inserted it into database as 0000-00-00 so i changed it varchar and it accepted the correct date picker value into database.

can you still do this method you hae mentioned with it being varchar? it will always be in uk date format as i changed the code of jquery datepicker to dd/mm/yyyy so could it still be searched?

as i say, would like it to show until renewed and to display it a week before it was due.

many thanks again

[diafol]

If you've got a bad db design, why not fix that, rather than try to create workarounds?
date as 0000-00-00 should be fine if the datatype is set as 'date'. Why varchar?

Look up the mysql manual for the INTERVAL keyword - that should give you an idea of how to search for hits.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[andydeans]

If you've got a bad db design, why not fix that, rather than try to create workarounds?
date as 0000-00-00 should be fine if the datatype is set as 'date'. Why varchar?

Look up the mysql manual for the INTERVAL keyword - that should give you an idea of how to search for hits.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

thanks for that, it inserts it as uk date dd/mm/yyyy so when it appears in database it shows as 0000-00-00 so i changed to varchar to get around this. can you help resolve it inserting to uk date then i can do countdown?

thanks

[Jerail]

According to the MySQL reference, all dates must appear in the year-month-day order. If you want to display the dates in a different order, then you'll have to process it with PHP to re-arrange it. Otherwise you lose the advantage of having a date data type.
If you continue to use varchar, you're just making more work for yourself. You'll have to parse the date with PHP in order to calculate it.
You could use the explode() function for this. For example:

<?php
$parsed_date = explode('-', $original_date);
print_r($parsed_date);
/*
Outputs something like:
array {
    [0] => 31
    [1] => 12
    [2] => 2010
}
Where 0=day, 1=month and 2=year. From there you could evaluate your caculations based on the current date.
So maybe: */
$now = date("d")*date("m");
$year = date("o");
$then = $parsed_date[0]*$parsed_date[1];
if($parsed_date[2] == $year) {
    if(($then-$now) <= $three_monts) {
        // do something
    }
}
?>

Just to be clear, this snippet is in no way meant to be functional. It's just an illustration. You'd have to come up with some algorithm for calculating the difference between the current date and the expiry date.

[andydeans]

hi jerail thanks so much for your help so far, i have changed it back to date type in db.

the issue i was having was i was using a datepicker that only displayed as yyyy-mm-dd but i have changed this now to dd-mm-yyyy which is working for inserting into the database.

i have this javascript running to choose the date:

<script type="text/javascript">

$(function() {
        $('#userDate').datepicker({dateFormat: 'dd-mm-yy',
                altField: '#dbDate', altFormat: 'yy-mm-dd'});
});
</script>



<input type="text" id="userDate">
<input type="hidden" id="dbDate" name="dbDate">

only issue is on the details page after insertion it displays it from the db in yyyy-mm-dd format as that is how it inserts.

to display it back in dd-mm-yyyy would i use something like this in my page:

<?php

$userdate = date("d.m.y"); 

?> 

will that display properly?

also after this on the home page like i say it would be policy due this week, then day then go into an overdue column but i want to display if from the db, but grab the clients name so it would display the policy and clients details to call?

again thank you for your help and ardav thanks i appreciate it was a workaround with varchar but now the javascript is working properly so will insert now.

thanks

[Jerail]

$userdate = date("d.m.y"); will just show the today's date. You need to do something like this:

<?php
$SQLDate = "1999-05-28";
$date_array = explode("-", $SQLDate);
$userdate = date("d.m.y", mktime(0, 0, 0, $date_array[1], $date_array[2], $date_array[0]));
echo $userdate;
?>

Would output: 28.05.99 As for calculating whether its due this week, or overdue, etc., that would be just be a mathematic function of the policy's expiration date and the current date. Then you could assign the item a varying attribute depending on its status.

[andydeans]

thanks again jerail. i tried my line and it displayed todays date like you said.

looking at your code that i can see how you have done it, but how do i get the value from the database and not the date you have keyed in at start?

so at moment i have a recordset on page, this is my variable from the table that displays the date of birth:

$row_WADAclients

how can i use this with your code or how can i use your code to display the dateofbirth from database rather than a declared variable at the beginning?

cheers again

[diafol]

For swapping date formats, you could use the mysql STR_TO_DATE function:

assume $date is something like "23/11/2010"

"INSERT INTO table SET field = STR_TO_DATE('$date', '%d/%m/%Y')"

This takes a UK-based format (%d/%m/%Y) and converts it into an unix-formatted date (output into field = 2010-11-23).

[andydeans]

thanks ardav, i have it inserting into the database now properly in yyyy-mm-dd but just want to display any dates on my page from the database in uk format. could you help please?

many thanks

For swapping date formats, you could use the mysql STR_TO_DATE function:

assume $date is something like "23/11/2010"

"INSERT INTO table SET field = STR_TO_DATE('$date', '%d/%m/%Y')"

This takes a UK-based format (%d/%m/%Y) and converts it into an unix-formatted date (output into field = 2010-11-23).

[Jerail]

thanks again jerail. i tried my line and it displayed todays date like you said.

looking at your code that i can see how you have done it, but how do i get the value from the database and not the date you have keyed in at start?

so at moment i have a recordset on page, this is my variable from the table that displays the date of birth:

$row_WADAclients

how can i use this with your code or how can i use your code to display the dateofbirth from database rather than a declared variable at the beginning?

cheers again

Instead of doing $SQLDate = "1999-05-28"; you could just do $SQLDate = $row_WADAclients['DateOfBirth'];

[diafol]

The opposite of STR_TO_DATE is DATE_FORMAT:

"SELECT DATE_FORMAT(field, '%d/%m/%Y') AS alias FROM table"

Download the MySQL manual: http://dev.mysql.com/doc/

It may save you some headaches.

[andydeans]

thanks to you all for helping you have all been great.

jerail i went with what you gave me, only 2 lines of code and it did exactly what it needed to do.

thanks mate

only i now have to try writing a query for the policies.

the policies are held in more than one table so for say car insurance that is in its own table and home insurance in another.

how would i query that and it needs to be unique to that usersid who has logged in.

so i have a relationship between userstbl and clientstbl and clientstbl to the products which hold the policies.

when i set this up originally i used mysql workbench however having the constraints in for on update and on delete it errored in my app. so i took the constraints out and i now have the issue that when i delete a clients it does not delete the policies associated.

anyway that is another issue but if you have any tips for the countdown i would appreciate it and i have to say this forum is best i have been on. responses are brilliant and so quick.

cheers to you all!!!

[andydeans]

this is what i ended up with:

<?php
$SQLDate = $row_WADAclients['DateOfBirth'];
$date_array = explode("-", $SQLDate);$row_WADAclients['DateOfBirth'] = date("d.m.y", mktime(0, 0, 0, $date_array[1], $date_array[2], $date_array[0]));
?>

[andydeans]

one thing though it defaults to 01.01.70

and also can you change it to 01.01.1970?

many thanks

[Jerail]

If you use a capital "Y" it will show the year in 4-digit format.
See http://ca2.php.net/manual/en/function.date.php for all the possible arguments to the date() function. Use that format the date in any way you like.
The mktime() returns a raw date type resource, and that can be formatted however you like by combining it with date().

[andydeans]

If you use a capital "Y" it will show the year in 4-digit format.
See http://ca2.php.net/manual/en/function.date.php for all the possible arguments to the date() function. Use that format the date in any way you like.
The mktime() returns a raw date type resource, and that can be formatted however you like by combining it with date().

Hi jerai,

thanks for getting back to me. i looked through the php manual, i have done a few times but i am missing something i think as i cannot seem to understand this properly.

if i take out the mktime in the code you gave me will that take away the default display 01/01/1970? or what do i need to put in?

i am ok at seeing what the start of the code is doing but this :

mktime(0, 0, 0, $date_array[1], $date_array[2], $date_array[0]))

not too sure and wondering what i need to change to get that to not have the default 01/01/1970?

thank you again jerail

[andydeans]

if i insert something into the database that is before 01/01/1970 it display ok but will that code you gave me have any problems in future doing this?

as it is date of birth it could be before 1970.

as i say it does seem to work but i noticed a blank insert that displayed it as 01-01-1970.

any thoughts jerail?

thank you

[andydeans]

thanks for your help jerail,

just wondering if you could help me with the countdown function?

would greatly appreciate any help.

cheers

[andydeans]

hi jerail,

could you or someone help please?

your code you gave me to display the time in uk date works, but only for my first result.

i have a repeating table display the recordset and my first entry displays in uk time fine but when you add anothter to that entry first goes to the second row in the table and displays in yyyy-mm-dd with first though in uk date format?

<?php
$SQLDate = $row_rslife['StartDate'];
$date_array = explode("-", $SQLDate);$row_rslife['StartDate'] = date("d/m/Y", mktime(0, 0, 0, $date_array[1], $date_array[2], $date_array[0]));
?>

<td><span class="tabletext"><?php echo $row_rslife['StartDate']; ?></span></td>

can anyone help?

thanks again for your help

[diafol]

This is why I made a reply. Check out the SQL transforms.

Countfown = js function (see js timeout)