conversion of datetime datatype to date and time datatype in php/mysql

I am a newbie. I started php coding few days back. I want to copy a "datetime" datatype in php to fields that are of "date" and "time" datatype.

I have kept the field name datetime_info for the datetime value. It exists in try1 table. date is the name of field for "date" datatype and time is the name for "time" datatype. These two exist in try2 table.

Here is what I have written.

$result = mysql_query("SELECT * FROM try1");

while ($row = mysql_fetch_array($result))
{

    $result_update = mysql_query("INSERT INTO try2 (date, time) VALUES ('".$row_team['datetime_info']."', '".$row_team['datetime_info']."')");
    if (!$result_update)
    die('Error: ' . mysql_errno() . mysql_error());
}

The values stored in "try1" table are:

id  datetime_info
1   2008-10-02 00:00:00
2   2008-10-09 00:00:00

The expected response should be the date and time stored in respective fields. However, the output is

id  date        time
2   0000-00-00  00:00:00
3   0000-00-00  00:00:00

Can anyone explain me why and how? I tried a lot of sites but did not find any proper explanation for this. Thank you in anticipation.

Regar开发者_如何学JAVAds, BasicGem

---

First of all, you shouldn't have to use PHP to do the hard work here. MySQL is very powerful to do these kinds of tasks on it's own.

Since you are copying all rows from table try1 into try2 (but splitting the datetime column), the following should work:

INSERT INTO
  `try2`
  SELECT
    null, DATE( `datetime_info` ), TIME( `datetime_info` )
  FROM
    `try1`

What this does is: for every record found in try1 insert the following values into try2:

• null means use the auto_increment functionality of the id column in try2 (this is presuming the id column is indeed an auto incrementing primary key field)
• DATE( `datetime_info` ) means use the DATE part of the datetime_info column of try1
• TIME( `datetime_info` ) means use the TIME part of the datetime_info column of try1

Simply doing mysql_query( $sql ) where $sql is a string that represents above query should suffice then. No need to loop through any results first with PHP.

I was gonna write a PHP solution as well, but others have already done so. I would have suggested this answer.

---

$result = mysql_query("SELECT * FROM try1");

while ($row = mysql_fetch_array($result))
{
   $split = explode(' ', $row_team['datetime_info']);

   $result_update = mysql_query("INSERT INTO try2 (date, time) VALUES ('". $split[0]."', '".$split[1]."')");
    if (!$result_update)
    die('Error: ' . mysql_errno() . mysql_error());
}

---

Because your taking it into a format and pushing it to fit another one. Just use explode() and you'll have the 2 parts you need.

list($date, $time) = explode(' ', $row_team['datetime_info']);

---

Field of type date stores the date information, meaning year, month and day (in that order). time field, as you might have guessed, stores time information in format of hour:minute:second.

If you take a look at what you did, which is inserting a datetime into date field and time field respectively - MySQL doesn't recognize the format (you are passing the data in the wrong format that's expected for date or time field), hence it stores it as default date value.

You need to parse the output of datetime_info column into the date part and time part and then feed it to mysql.