[SOLVED] Inserting Time

[cheechm]

Hi,

Can you recomend how I would go inserting a date into a MySQL database from a form? What type of format would use? Can you point me toward a tutorial or anything?

 

Thanks

[Yesideez]

If you set a datatype as INT(11) unsigned you can use php's time() and insert that value. You've use date("format",time()) to dusplay it.

 

If you use the datatype "datetime" you can use mysql's NOW() to insert the time but to display it using PHP you'd use something like this:

 

date("format",strtotime(field))

 

format=PHP date formatting

field=your mysql data field

[per1os]

Do you want to be able to view the date via a phpMyAdmin or is it only going to be viewed from like a php script.

 

If a php script I suggest storing as a UNIX timestamp ( www.php.net/time() ) as a INT(11) in MySQL.

 

If you have a specific format, either a date/time column or varchar would work.

[cheechm]

Well I would like the format to be:

DD/MM/YYYY  and HH/MM

 

Well I want the user to be able to define a time in a form which is then added to the MySQL database.

So say the user entered 20/11/2007, 12:30, then I want that to be saved to the database.

 

The viewing will only be from PHP.

[Yesideez]

date('d-m-Y H:m',time())

[per1os]

Store it as a unix timestamp, and use www.php.net/strtotime  to convert it to the unix timestamp (INT(11) for the column definition)

[cheechm]

So how would I instert it from a form into a MySQL database? Could you give me example code please? Thanks

[cheechm]

Any example code?

[chigley]

How is the date inputted in the form?

[cheechm]

There will be a drop down box for year. Then there will be input text boxed for day and month.

(DD/MM/YYYY). For the time, there will be a drop down list comprimising of a few times.

[chocopi]

The best way to store a date in sql is YYYY-MM-DD

[master82]

or just add unix_timestamp() to the insert / update query if you want the current time it was inserted / updated

[cheechm]

The best way to store a date in sql is YYYY-MM-DD

 

But could you display that as DD/MM/YYYY in the PHP file?

 

Can anyone post some example code? (I can do the form, I just needd the adding to the MySQL info)

[chocopi]

you could do something like

 

<?php
// $date would be the sql select statement
$date = 'YYYY-MM-DD';
$date = explode('-',$date);
$year = $date[0];
$month = $date[1];
$day = $date[2];

echo "$day/$month/$year";
?>

 

Hope it helps

 

~ Chocopi

[chocopi]

oh sorry you could use:

 

<?php
// $date would be the sql select statement
$date = 'YYYY-MM-DD';
list ($year,$month,$day) = explode('-',$date);

echo "$day/$month/$year";
?>

 

I forgot about list() 

[cheechm]

Ok that helps a lot. How would I instert that into the database if the table is called Results and the column is called Date (What type of column would I use?)

[chocopi]

Well this is what i would use:

 

<?php

// Insert into db YYYY-MM-DD so you can use other date functions later on
$date = date('Ymd');

$query = "INSERT INTO Results (Date)
VALUES('$date')";

mysql_query($query) or die(mysql_error());

?>

 

The to get this back to DD-MM-YYYY use the code i gave earlier:

 

<?php
$query = mysql_query("SELECT Date FROM Results"); // You will most likely need a WHERE statements
$row = mysql_fetch($query) or die(mysql_error());
$date = $row['Date'];

list ($year,$month,$day) = explode('-',$date);

echo "$day/$month/$year";
?>

 

I think that should work but its untested 

 

~ Chocopi

[chocopi]

Oh my bad, should be this: (i forgot to add array onto mysql_fetch  )

 

Code:

 

<?php

// Insert into db YYYY-MM-DD so you can use other date functions later on
$date = date('Ymd');

$query = "INSERT INTO Results (Date)
VALUES('$date')";

mysql_query($query) or die(mysql_error());

?>

 

 

The to get this back to DD-MM-YYYY use the code i gave earlier:

 

 

<?php
$query = mysql_query("SELECT Date FROM Results"); // You will most likely need a WHERE statements
$row = mysql_fetch_array($query) or die(mysql_error());
$date = $row['Date'];

list ($year,$month,$day) = explode('-',$date);
$date = "$day/$month/$year";

echo "$date";
?>

 

This should work this time,

 

~ Chocopi

[cheechm]

OK that is a great hel. So I made a form, but can't seem to make the date and time work:

 

<form action="addevent.php" method="post">
Event Name: <input type="text" name="eventname" /><br />
Yeargroup: <input type="text" name="year" /><br />
Team: <br /><textarea rows="3" cols="30" name="team" /></textarea><br />
Date (DD/MM/YYYY): 
<input type="text" name="day"  onfocus="this.value=''" value="Day"/>
<input type="text" name="month"  onfocus="this.value=''" value="Month"/>
<select name="year">
<option value="2007">2007</option>
<option value="2008">2008</option>
</select>
<select name="time">
<option value="12:30">12:30</option>
<option value="games">During games period</option>
<option value="4:00">4:00</option>
</select>


<input type="submit" value="Add Event"/>
</form>

 

How would I instert that into the database.

[chocopi]

on addevent.php you need to have the insert:

 

<?php

$date = date('Ymd');

$query = "INSERT INTO Results (Date)
VALUES('$date')";

mysql_query($query) or die(mysql_error());

?>

 

if addevent.php is the code you showed then put:

 

<?php

// Your form code

if($_POST)
{

$date = date('Ymd');

$query = "INSERT INTO Results (Date)
VALUES('$date')";

mysql_query($query) or die(mysql_error());

}

?>

 

Hope that helps,

 

~ Chocopi

[cheechm]

<form action="addevent.php" method="post">
Event Name: <input type="text" name="eventname" /><br />
Yeargroup: <input type="text" name="year" /><br />
Team: <br /><textarea rows="3" cols="30" name="team" /></textarea><br />
Date (DD/MM/YYYY): 
<input type="text" name="day"  onfocus="this.value=''" value="Day"/>
<input type="text" name="month"  onfocus="this.value=''" value="Month"/>
<select name="year">
<option value="2007">2007</option>
<option value="2008">2008</option>
</select>
<select name="time">
<option value="12:30">12:30</option>
<option value="games">During games period</option>
<option value="4:00">4:00</option>
</select>


<input type="submit" value="Add Event"/>
</form>

That is in event.php

 

And I will put this:

<?php

$date = date('Ymd');

$query = "INSERT INTO Results (Date)
VALUES('$date')";

mysql_query($query) or die(mysql_error());

?>

in addevent.php

 

What format should the date be. I have it set to date time and whenever I use the form it gives me the same thing:

09 00:00:00/06/2007 /

[chocopi]

in your table ?

[cheechm]

That is what it looks like in the PHP.

 

2007-06-09 00:00:00 <== that is in the table.

 

 

Date column is set as datetime. Is that right?

Also, I think it is something to do with my form?

 

No matter what I enter, it always gives this:

2007-06-09 00:00:00.

 

Could you check my form?

 

Thanks

[chocopi]

Change it from DateTime to Date, as that is what the 00:00:00 are for. Unless you want the time in there as well

[cheechm]

Yeh I want time there as well.