Entering Dates into mysql database using sql INSERT

Question

A better suggestion on how to approach this then what I have done so far would be appreciated. Right now the dates have to be entered in the correct format year-month-day. Is there a way to automate this task more, with drop downs, just not sure how you would combine the information from the drop downs to submit to one field with my sql INSERT INTO command. This is what it looks like for a quick visual site

Here is an example of my code below.

$date = $_POST['date'];
$title = $_POST['title'];
$entry = $_POST['entry'];





if(($date !="") && ($title !="") && ($entry !="")) {

$sql = "INSERT INTO news (date,title,entry)VALUES(
\"$date\",
\"$title\",
\"$entry\"
)";

$results = mysql_query($sql)or die(mysql_error());

}
?>







<form name="formS" method="post" action="insert.php">
<p>
Date:<input type="text" name="date" id="date" />

</p>

<p>
Title<input type="text" name="title" id="title" />
</p>
<p>
Entry:<textarea name ="entry"  ></textarea>
</p>

<p>
Submit:<input type="submit" name="submit" id="submit" />
</p>


</form>

Not related to the question, but I think you should read http://en.wikipedia.org/wiki/SQL_injection :) — Lukáš Lalinský, Oct 10 '09 at 08:27
Not much to fear from SQL injection with an insert, since you can't run multiple queries in one statement by default (I think it's PHP that locks this out), BUT... +1 for bringing it up, as it is an important overall consideration :) — hlpiii, Oct 10 '09 at 22:19

score 3 · Accepted Answer · answered Oct 10 '09 at 05:38

One free-form date input field can be fine, depending on your needs. This will give people a lot of flexibility in how they enter a date. For instance, this will work if someone enters "Yesterday" or "7 days ago".

$date = date("Y-m-d", strtotime($_POST['date']));

Demo:

$date_expressions = array(
    "yesterday",
    "tomorrow",
    "7 days ago",
    "next year",
    "last month",
    "10 jan 08"
);

date_default_timezone_set("America/Los_Angeles");

echo "<pre>";
foreach ($date_expressions as $date_expression) {
    $date = date("Y-m-d", strtotime($date_expression));
    echo "{$date_expression} = {$date}\n";
}
echo "</pre>";

Output (executed on 10-9-2009):

yesterday = 2009-10-08
tomorrow = 2009-10-10
7 days ago = 2009-10-02
next year = 2010-10-09
last month = 2009-09-09
10 jan 08 = 2008-01-10

This is great! how would I go about reversing this for example fetching the row 'date' from my table and having it output "tomorrow today 7 days ago" etc... with a echo statement? — Cool Guy Yo, Oct 11 '09 at 20:28
Check this out: http://stackoverflow.com/questions/11/how-do-i-calculate-relative-time — hlpiii, Oct 12 '09 at 00:52

score 0 · Answer 2 · answered Oct 10 '09 at 05:00

0

You can simply accept the date as multiple drop down boxes and then combine them before inputting it in the database

$date = $_POST['dateyear']."-".$_POST['datemonth']."-".$_POST['dateday'];

answered Oct 10 '09 at 05:00

Alec Smart

94,115
39
120
184

Also, make sure you sanitize/filter the input. – Alec Smart Oct 10 '09 at 05:01
Thanks this was exactly what I was trying to figure out. What does sanatize/filter mean? – Cool Guy Yo Oct 10 '09 at 21:31

score 0 · Answer 3 · answered Oct 10 '09 at 08:28

0

If you have multiple inputs for the date, you can combine it like this:

$date = sprintf('%04d-%02d-%02d', $_POST['date_year'], $_POST['date_month'], $_POST['date_day']);

answered Oct 10 '09 at 08:28

Lukáš Lalinský

40,587
6
104
126

Entering Dates into mysql database using sql INSERT

3 Answers3