1

I'm trying to save a string variable into mysql using a line of php.

'UPDATE `printed` SET `letterReturned`= '.$returnedDate.'  WHERE `defendantId` = '. $defendantId.';';

$returnedDate is a string looking like this '10/01/2018'. I've verified with gettype that it is a string when it goes in but what end up in the databse is this: 0.004955401387512388

How on earth is it saving as a decimal? I'm stumped and have been unable to find anything related to this by googling it. There must be a name for this kind of thing.

Any Ideas Greatly appreciated. Please enlighten me.

MarcM
  • 2,173
  • 22
  • 32
Elliot Robert
  • 355
  • 1
  • 4
  • 13
  • What is the data type of letterReturned? –  Jan 25 '18 at 17:44
  • posting your table schema would really help – Rotimi Jan 25 '18 at 17:44
  • 1
    You need to quote your variables – aynber Jan 25 '18 at 17:45
  • First of all, the default date format for MySQL is `yyy-mm-dd`, so your value `10/01/2018` is not good. Secondly, you have to enclose your values in apostrophes. Thirdly, many people will now jump it and tell you that your query is susceptible to SQL injection, so be prepared for the stampede. Now back to your main question, what is the type of the `letterReturned` field in the database? Most likely your problem lies there. – Racil Hilan Jan 25 '18 at 17:47
  • 2
    You are wide open for SQL injection. I recommend using prepared statements and parameter binding (docs for [PDO](https://secure.php.net/manual/en/pdo.prepared-statements.php) and [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php)). With that, you'll never have to worry about quoting issues with your variables. – aynber Jan 25 '18 at 17:47
  • 1
    BTW, the reason why you're getting a decimal is because it's doing math, since it's not quoted: 10 divided by 10 divided by 2018... – aynber Jan 25 '18 at 17:49

1 Answers1

4

Funny situation!!! :D

Notice that the calculating 10 / 1 / 2018 = 0.004955401387512388

Your are missing quotes in your orignal query assignment, then arithmetic division is performed before inserting value to table.

Try:

$query = "UPDATE `printed` SET `letterReturned`= '".$returnedDate."'  WHERE `defendantId` = ". $defendantId.";";

As suggested in a comment, take a look at When to use single quotes, double quotes, and back ticks in MySQL for clarification.

MarcM
  • 2,173
  • 22
  • 32
  • Funny indeed. In hindsight it's obvious what's going on. The forwards slashes acting as division symbols and using the incorrect quotes caused this problem. Others where correct when they pointed out the antiquated syntax but this code is for internal purposes so I did what was quickest. My mistake. Thank you all for your help. – Elliot Robert Jan 26 '18 at 10:40