Link to home
Start Free TrialLog in
Avatar of teedo757
teedo757

asked on

Help with insert mysql statement

Need some help with this mysql statement

$outDate =  $_GET["mout"].$_GET["dout"].$_GET["yout"];
$inDate =  $_GET["min"].$_GET["din"].$_GET["yin"];

echo "OutDate:".$outDate; 
echo "inDate:".$inDate;

Open in new window


my output is:
OutDate:04 21 2011 inDate:04 24 2011

outdate and indate are date fields in the database. I want to eliminate the white space and put it in the correct format for my mysql table. Currently the data is inserted however I get an error about the date time data not being correct.

$sql="INSERT INTO orderitem (ID, onum,outdate,indate,itemrentqty,itemnum)
 VALUES
 ( ' ', $_GET[ordernum], '$outDate', '$inDate' ,$_GET[itemqty], $_GET[itemnum])";

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }

mysql_close($con);

Open in new window


How do I correctly insert the date into my table? Thanks
Avatar of teedo757
teedo757

ASKER

Also is there a way to fix my bad data or do I need to drop and recreate my table? Toad does not show the corrupt records....not a big deal just wondering.
Your date string is incorrectly formatted for mysql.

it should look like this:
OutDate: 2011-04-21
inDate: 2011-04-24

Try this code to correct the format:
$outDate = date('Y-m-d', strtotime($outDate));
$inDate = date('Y-m-d', strtotime($inDate));
SOLUTION
Avatar of BlackSnowman
BlackSnowman

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
wow okay well Ray and I had the same suggestion basically. The difference with mine is that you manually ensure the correct order of the date fields.
small correction to post above: it should be YYYY-MM-DD, not YYYY-DD-MM.

$outDate =  $_GET["yout"]."-".$_GET["mout"]."-".$_GET["dout"];
$inDate =  $_GET["yin"]."-".$_GET["min"]."-".$_GET["din"];

Open in new window

Well with Rays i got

OutDate:1969-12-31inDate:1969-12-31

With BlackSnowman I got the correct date but with spaces

OutDate:2011 -21 -04 inDate:2011 -24 -04

Without corrupting more data do you know if this will work or do I need to get rid of the spaces?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Trim fixed the issue.

Had to drop the table a recreate it for the error to go away. Thanks