Date Format (PHP/MySQL)

[dashifen]

Greetings,

This board is great.  I've been working to connect flash to my PHP/MySQL site for a while and now I can!  I was never aware of the sendAndLoad() functionality of the LoadVars class until I visited here earlier.  

So, here's where my problem is coming from.  I'm creating an online calendar program for myself.  It'll have tasks and events.  Tasks have a title, description, due date, and priority.  So far, the title, description, and priority are working fine.  However, on 7 out of the 9 tests that I made so far, the due date was "lost."  It shows on the screen as 0000-00-00 00:00:00 which is the default vaule for the table.  However, the two dates that worked are 2001-01-20 01:00:00 and 2002-09-20 04:00:00.  The odd part is, that I didn't enter any time into the field, just a date.  Plus, it never seems to work for dates in 2004.  

Here is the code that is executed when you click the save task button:

on(click) {
   new_task = new LoadVars();  
   new_task.onLoad = function() {
      if(this.msg == "saved") _root.status_msg.text = "Task Saved."
      else _root.status_msg.text = "Error!!"
   }
   
   new_task.title = _parent.i_title.text;
   new_task.due_on = _parent.i_due_on.text;
   new_task.priority = _parent.i_priority.data[_parent.i_priority.selectedIndex];
   new_task.description = _parent.i_description.text;

   _parent.i_title.text = "";
   _parent.i_due_on.text = "";
   _parent.i_priority.selectedIndex = 0;
   _parent.i_description.text = "";
   
   new_task.sendAndLoad("http://www.dashifen.com/calendar/proNewTask.php", new_task, "POST");
}

And here is the code that is at proNewTask.php:

<?
include("functions.php");
include("../db_vars.php");

$title = mysql_escape_string($_POST['title']);
$due_on = $_POST['due_on'];
$priority = $_POST['priority'];
$description = mysql_escape_string($_POST['description']);

$success = runQuery("INSERT INTO cal_data (title, body, start, priority) VALUES ('$title', '$description', '$due_on', $priority)");
if($success) echo "msg=saved&";
else echo "msg=error&";
?>

__________________

the runQuery() function is a self-defined function that uses mysql_connect, mysql_select_db, and mysql_query to execute the sql statement passed to it as the parameter.  It has been thouroughly tested and works.  The "start" field in the database is used for tasks to represent their due_date.  It saved space since I'm using the same table for tasks and events.

So:  can anyone figure out with the due_on value isn't always saved?  I'm working off the assumption that it's because I've improperly formatted the date in the input field, but I'm not sure which format to use so that the MySQL database will like it.

[dashifen]

In case it helps.  Here's a dump of the cal_data table, where the information is inserted:

CREATE TABLE `cal_data` (
 `item_id` int(10) unsigned NOT NULL auto_increment,
 `priority` tinyint(2) NOT NULL default '1',
 `title` varchar(255) NOT NULL default '',
 `body` blob,
 `start` datetime default NULL,
 `end` datetime default NULL,
 `type` tinyint(1) default NULL,
 PRIMARY KEY  (`item_id`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;

[Jorge Solis]

Hi David, welcome to the boards !

How are you inserting the start and end columns?

Jorge

[dashifen]

On the flash side, they're simply input text fields.  I had hoped to be able to enter something like 2/26/2004 for the due dates and perhaps 2/26/2004 6:30 PM when a time is required.

On the PHP side, I'm sending them to the database exactly as they come out of flash (I thought) but something seems to be getting weird in the conversion from string to datetime.

[Jorge Solis]

From MySQL manual

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.

Insert the expression you're using directly trough phpMyAdmin, check if it works, then return to Flash.

Jorge

[dashifen]

That looks like that's going to do it.  I've been adding the infomration as MM-DD-YYYY HH:MM:SS rather than YYYY-MM-DD etc.  Thanks for your help.  I'm used to working with MS SQL Server and not MySQL which works of of the American common format for dates rather than UNIX formats, I guess.