i've got php/mysql script yielding strange results on date field. along process, dates fine until end. final result has every entry in date field '0000-00-00'. i'm totally stuck , don't know else do. can tell issue php not interpreting date, don't know how fix it. here code:
$sql = "create table temp_workouts (my_date date, sg_id int(11), loc_id int(11))"; $result = mysql_query($sql); if (!$result) { $tag_success = "failure"; $tag_message = mysql_error(); echo encodejson($tag_success, $tag_message); die(); } $sql = "select * my_table"; $result = mysql_query($sql); while ($row = mysql_fetch_array($result, mysql_assoc)) { $my_date = $row['my_date']; echo $my_date . " "; //<--this output looks perfect $sql = "insert temp_table (my_date) values ($my_date)"; $result2 = mysql_query($sql); } die(); when flip on myphpadmin , @ table, entire column my_date contains '0000-00-00'. how can php recognize 'y-m-d' formatted date? thanks. appreciate help.
your immediate problem don't use quotes around date values in insert statement.
change
$sql = "insert temp_table (my_date) values ($my_date)"; to
$sql = "insert temp_table (my_date) values ('$my_date')"; ^ ^ now, can use insert ... select syntax achieve goal in 1 go
insert temp_table (my_date) select my_date my_table therefore part of code
$sql = "select * my_table"; $result = mysql_query($sql); while ($row = mysql_fetch_array($result, mysql_assoc)) { $my_date = $row['my_date']; echo $my_date . " "; //<--this output looks perfect $sql = "insert temp_table (my_date) values ($my_date)"; $result2 = mysql_query($sql); } can changed to
$sql = "insert temp_table (my_date) select my_date my_table"; $result2 = mysql_query($sql); on side note: consider switching either pdo or mysqli , use prepared statements.
