Okay, I'm totally confused -- but I'll tell you what I do. :)
First, in my HTML form, I have dropdowns for day, month and year.
MySQL likes the date in the format YYYY-MM-DD, and it's simply easier to
assemble that in PHP.
I'm going to digress for a second, but bear with me:
I use these dropdowns a lot, so I just wrote a class that I use in most
of my projects that has a lot of common form elements.
I also find it handy to be able to populate the dropdown with a
pre-selected value (ie. for an EDIT screen).
Here's an example of a function that creates a date dropdown:
<?php
/**
* Returns a HTML snippet with a select box for choosing a date between 1
and 31
* @param string strFieldName Name of the field (example: date)
* @param integer intSelectedDate [default: 01]
* @return string
*/
function SelectDate($strFieldName,$intSelectedDate=1) {
$output = '<select name="' . $strFieldName . '">';
for (i=0; $i <= 31; $i++) {
$output =. "<option value='$i'>$i</option>";
}
$output .= "</select>";
return $output;
}
?>
When I'm assembling my page, it's really easy to just call the function,
instead of pasting in 33 lines of HTML each time I want a date dropdown.
Anyway, back to the question at hand:
Let's assume you've got a form with three select boxes, named 'month',
'day', 'year' repsecively.
Once your user submits the form, you'll have the following variables at
your disposal (assuming the form's method parameter is set to POST)
$_POST['day']
$_POST['month']
$_POST['year']
You can then simply assemble the date string for insert:
$date = $_POST['year'] . "-" . $_POST['month'] . "-" . $_POST['day'];
$sql = "INSERT INTO blah (...,,date) VALUES (...,'$date');";
Conversly, when you select the date from the database, there are two
approaches I use.
If it's JUST a date, the default date format is fine for most purposes
(like populating an EDIT form).
The second option is to get the date as a UNIX_TIMESTAMP -- which is
generally my preferred way of getting dates that I need to format for
output.
example:
$sql = "SELECT date FROM blah WHERE foo = '$bar';";
// you use your method of choice for executing the query, and if all
goes well, you get:
$result: = "2005-04-01";
$arrDate = explode($result,'-');
/*
Explode is handy, and splits a string into array elements at a specified
string, in this case, the dash character.
$arrDate would look like:
$arrDate = array(
[0] => '2005'
,[1] => '04'
,[2] => '01');
This is perfect for say, reassigning the selected date to the
SelectDate() funtion, but it's not that simple to format the date like
you want. You could use mktime() to convert this to a unix timestamp
and then use date() to format the timestamp, but in this instance, it
makes more sense to just ask MySQL for the date in UNIX_TIMESTAMP format
to begin with.
*/
This leads us to the next example:
You could select the date as a UNIX_TIMESTAMP (number of seconds from
1/1/1970 12:00:00am), and use the PHP date() method to transform it.
date() is extremely powerful, and allows you to convert a timestamp into
a formatted date of your specification.
http://www.php.net/manual/en/function.date.php
In that instance, you'd do something like:
$sql = "SELECT UNIX_TIMESTAMP(date) AS ts_date FROM blah WHERE foo = 'bar'";
// Execute your SQL statement and get a result successfully
$result = 14976487041; // or some other incomprehensible integer.
$strOutputDate = date("jS F Y",$result);|
echo $strOutputDate;
results in:
1st January 2006
|
So, the bottom line is that if you select the column as UNIX_TIMESTAMP,
it's one additional line of PHP to format the date for output.
-Jeromie
>I can do something along the lines of: <td><?php echo
>$row_Recordset1['day']; ?>/<?php echo $row_Recordset1['month']; ?>/<?
>php echo $row_Recordset1['year']; ?></td>
>which will give me something like 01/01/2006
>
>but what I actually want is 1st January 2006
>
>I thought about perhaps getting the mysql database to read the three
>columns into one string in a fourth column 01012006, then setting
>the output something along the lines of:
>
>mysql_query("select *, DATE_FORMAT(date, '%d/%m/%Y') as editeddate
>from tablename");
>but how do I get it into one string in a fourth column???
>or:
>is there a better way to do it??
>
>I also th0ught about having a hidden field on the input form - so I
>have drop downs for day, month, and year in my input form which then
>add all three to the hidden field ( as a string) which then inputs
>to the mysaql database - and I could use the above DATE_FORMAT to
>convert to UK in the output page.
>Awful stuck here any help would be very much appreciated!
>Thank You
>
>
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
