-2

My PHP string contain this data : 

$OpenDate = '20-Sep-18' ;

but when i insert it into database it give me an error : 

Column not found: 1054 Unknown column 'Sep' in 'field list'' in C:\xampp

tried to replace all ' - ' to ' _ ' but it has not worked also .

EDIT :

QUERY CODE : 

$stmt = $conn->query("INSERT INTO `ticketinfo`
(`tOrderId`)
VALUES
( ".$tOrderID.") on duplicate key update `tPLU`= ".$tPLU." , `tPrice`= ".$Total1." , `tBuy`=".$OpenDate  );

the problem is in the $OpenDate

user9991765
  • 53
  • 1
  • 7
  • 3
    The right database format is `2018-09-20`. – phrogg Oct 05 '18 at 08:16
  • 2
    Please paste your query code in your question. "_Insert Query for time Php_" So, do you want to insert a "time" or a "date"? – brombeer Oct 05 '18 at 08:25
  • @PhilRoggenbuck If it's a `date` field – brombeer Oct 05 '18 at 08:26
  • Show is your code. It's pretty hard to debug code you can't see. It's like asking a car mechanic to fix your car without letting them look at it. Also, as the answer below says, you need to store it in the format `YYYY-MM-DD` (but that's irrelevant to the actual error you're getting). – M. Eriksson Oct 05 '18 at 08:27
  • 1
    Based on the error message, you are trying to add the value without enclosing it into quotes (and using wrong date format). So, instead of `20-Sep-18`, you should use `"2018-09-20"`. – slaakso Oct 05 '18 at 08:28
  • Convert this date string into MySQL acceptable format, using: `date('Y-m-d', strtotime($OpenDate))` – Madhur Bhaiya Oct 05 '18 at 09:03
  • i have added query code , thanks you so much guys for help . – user9991765 Oct 05 '18 at 13:03
  • @user9991765 use single quotes (') around your parameters, and use `mysqli_real_escape_string` also – Madhur Bhaiya Oct 05 '18 at 14:29

2 Answers2

1
  • MySQL datetime format is 'YYYY-MM-DD HH:MM:SS'. For date alone, it will be: 'YYYY-MM-DD'
  • In PHP (Application code), you will need to convert your date string to an acceptable format for MySQL. You can use strotime() function to convert your date string to a Timestamp. Then, use date() function to convert into YYYY-MM-DD.
  • Eventually, use this converted date string in your Insert query.
  • Also, based on your error message, your query parameters need escaping. Please learn and implement Prepared Statements

Try (Rextester Demo):

$OpenDate = '20-Sep-18';
$mySQLFormatOpenDate = date('Y-m-d', strtotime($OpenDate));

echo $mySQLFormatOpenDate; // test display the formatted date

/* Now use $mySQLFormatOpenDate in your SQL Insert query */

Additional Details for format options used:

  • Y A full numeric representation of a year, 4 digits Examples: 1999 or 2003
  • m Numeric representation of a month, with leading zeros 01 through 12
  • d Day of the month, 2 digits with leading zeros 01 to 31
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
0

According to https://dev.mysql.com/doc/refman/8.0/en/datetime.html the proper date format is YYYY-MM-DD, so no months in letters but in numbers. Should be 2018-09-20.

Jules R
  • 553
  • 2
  • 18