[thelist] Convert string to date
Jackson Yee
jyee at vt.edu
Wed Jul 10 10:12:00 CDT 2002
[comments inline]
----- Original Message -----
From: "~kristina" <kristina at kfx-design.co.uk>
To: <thelist at lists.evolt.org>
Sent: Wednesday, July 10, 2002 03:09
Subject: [thelist] Convert string to date
> Dear list,
>
> I have a small db using mysql & php.
>
> I'm trying to convert a string that is entered by a 'user' via select
> options into a date in the future. With the idea that when the expirydate
> == today it will delete itself.
>
> The part I need help with is converting the string to a date.
>
> the date is in this format:
>
> <tr>
> <td>Delete record on:</td>
> <td>
> <select name="deleteday" size="1">
> <option> </option>
> <option>01</option>
> <option>02</option>
> <option>03</option>
> <option>04</option>
> <option>05</option>
> <option>06</option>
> <option>07</option>
> <option>08</option>
> <option>09</option>
> <option>10</option>
> <option>11</option>
> <option>12</option>
> <option>13</option>
> <option>14</option>
> <option>15</option>
> <option>16</option>
> <option>17</option>
> <option>18</option>
> <option>19</option>
> <option>20</option>
> <option>21</option>
> <option>22</option>
> <option>23</option>
> <option>24</option>
> <option>25</option>
> <option>26</option>
> <option>27</option>
> <option>28</option>
> <option>29</option>
> <option>30</option>
> <option>31</option>
> </select>/
> <select name="deletemonth" size="1">
> <option> </option>
> <option>01</option>
> <option>02</option>
> <option>03</option>
> <option>04</option>
> <option>05</option>
> <option>06</option>
> <option>07</option>
> <option>08</option>
> <option>09</option>
> <option>10</option>
> <option>11</option>
> <option>12</option>
> </select>/
> <select name="deleteyear" size="1">
> <option> </option>
> <option>2002</option>
> <option>2003</option>
> <option>2004</option>
> <option>2005</option>
> </select>
> </td>
> </tr>
Ouch. [g] I'd suggest using PHP to make your life a bit when outputting
selects. Instead of writing every single option out, you can use
<select name="deleteday" size="1">
<?php
for ($i = 1; $i <= 31; $i++)
{
echo '<option>', $i, '</option>
'; // extra whitespace for source viewing of the outputted
page
}
?>
</select>
Another nice user interface tidbit is that if the form submits to the same
page, you can place a selected attribute test so that the user doesn't have to
reselect the date again. You can do this by changing the echo statement above
to
$PostedDay = (int) $_POST['Day'];
for ($i = 1; $i <= 31; $i++)
{
echo '<option ';
if ($i == $PostedDay)
{
echo 'selected="true"';
}
echo '>', $i, '</option>
'; // extra whitespace for source viewing of the outputted
page
}
You can see this code at work in my updates page at
http://216.12.34.199/updates.php
If you set the date on the left side to something and then submit the form,
the date will be set to the values when the page refreshes. It's a nice
timesaver when you're going through several dates at once and don't need to
set the year and month for every single one.
> How is
> deleteday
> deletemonth
> deleteyear
> converted to a proper date
>
> is it as simples as
> $deleteday = d
> $deletemonth = m
> $deleteyear = Y
>
> which are all = $expirydate
When your form is posted, you will receive the variables in $_POST or $_GET
(depending on which method you use for the form element) in string form. You
can then use the strtotime() method to get a date stamp. MySQL can parse the
date automatically for you if you place it in yyyy-mm-dd format, so you could
simply concatenate the strings together like
$expirydate = (int) $_POST['deleteyear']
. '-' . (int) $_POST['deletemonth']
. '-' . (int) $_POST['deleteyear'];
$SQLQuery = "UPDATE TableName SET ExpiryDate='$expirydate' WHERE ID=$ID";
The (int) cast is to make sure that someone doesn't try to inject any nasty
SQL code into your query statement.
> So far I have:
>
> //convert string to date
> function makedate($expirydate){
> $deleteday = date("d");
> $deletemonth = date("m");
> $deleteyear = date("Y");
> }
>
> But it isn't doing anything.......!!
For functions which reference global variables, make sure that you insert a
line which says
global $deleteday, $deletemonth, $deleteyear;
at the top of the function, otherwise PHP will think that you're setting only
local variables, rather than changing the global variables. In other words,
whatever you assign to $deleteday in makedate() will stay in makedate() and
not affect $deleteday outside of the function.
> as then I think that all I need to do is add a query that tests to see if
> $expirydate == to today if so delete:
>
> $query = "DELETE * FROM tableName WHERE $expirydate=now()";
I would probably change it to "WHERE $expirydate < now()" just to be on the
safe side. The query as you have it right now will only work if you run it
every single day, and while that seems easy enough to do, you'd be surprised
what technical problems can come up in life.
> When I created the table I did this (which I'm not sure is correct
> either)
>
> CREATE TABLE tableName (
> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> timestamp DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
> expirydate DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
> deleteday TEXT NOT NULL,
> deletemonth TEXT NOT NULL,
> deleteyear TEXT NOT NULL,
> [rest of table info]
> );
First of all, are you really going to need the time of the day if you're only
going to give the user the option of date? If not, then you can use the DATE
type rather than the DATETIME type, and make your table a bit more efficient.
Secondly, deleteday, deletemonth, and deleteyear seem rather redundant if you
already have expirydate within the table. Don't they contain the same
information?
Good luck on the project. If you need any example code for dealing with dates
with PHP in forms, feel free to give me an e-mail off-list. There's no sense
in reinventing the wheel when someone else has already done so for you.
Regards,
Jackson Yee
jyee at vt.edu
http://www.jacksonyee.com/
More information about the thelist
mailing list