-3

Sorry it's been a while since I've done some MySQL queries, so this is probably a really simple answer. Can someone please check this code and see why this isn't doing anything to the database

$Reference = "031";
$Message = "Test Message";

mysql_connect("localhost", "secretlol", "secretlol") or die(mysql_error());

mysql_select_db("db630112365") or die(mysql_error());

mysql_query("UPDATE quotes SET Body='$Message' WHERE Reference='$Reference';") or die(mysql_error());

Any ideas, would be greatly appreciated :)

I was thrown the following error:

Unknown column '031' in 'where clause'

Yet, the value does exist.

Not sure if this helps:

Schema:

enter image description here

Values:

enter image description here

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Steve Dew
  • 17
  • 3
  • 4
    Obviously no rows match that criteria – John Conde Sep 06 '16 at 12:40
  • 6
    ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Sep 06 '16 at 12:40
  • 1
    @krunalnerikar values should ***never*** be backticked – Jay Blanchard Sep 06 '16 at 12:41
  • what did `mysql_error()` throw back or does it ever make it there in the first place? and error reporting http://php.net/manual/en/function.error-reporting.php – Funk Forty Niner Sep 06 '16 at 12:41
  • Error thrown back now is: Unknown column '031' in 'where clause' – Steve Dew Sep 06 '16 at 12:44
  • ^ There you go, @JohnConde was right from the get go. – Funk Forty Niner Sep 06 '16 at 12:45
  • I promise there is a row with the reference 031 though, and it's not updating – Steve Dew Sep 06 '16 at 12:48
  • 1
    @Fred-ii- I think that error differs with John's comment. That error seems to indicate `$Reference` is being used as the column, not the value (which the code doesnt support). – chris85 Sep 06 '16 at 12:50
  • @chris85 I deleted my comments; I honestly don't know where to throw myself here lol – Funk Forty Niner Sep 06 '16 at 12:52
  • We need to see the db schema for this. – Funk Forty Niner Sep 06 '16 at 12:52
  • Thanks @Chris85 that's what I thought. Surely the error would throw back saying something about a missing row, not a missing column? – Steve Dew Sep 06 '16 at 12:52
  • @SteveDew Is this your actual query, or did you simplify it? The error messages suggests your where clause is different. – chris85 Sep 06 '16 at 12:53
  • This is the actually query, I haven't simplified it. – Steve Dew Sep 06 '16 at 12:56
  • @SteveDew You posted the schema; thanks. However, can you include some values that are in db? Make sure also that there is no whitespace, such as either leading or trailing. Plus, if you do have a value of `031`, then try changing it to `31` and then your `$Reference = "031";` to `$Reference = "31";` - If that works, then the `0` is an issue. Do a var_dump() to see what the query is also. – Funk Forty Niner Sep 06 '16 at 13:01
  • 1
    You're really close to use the [reserved word 'References'](https://dev.mysql.com/doc/refman/5.6/en/keywords.html) – Jay Blanchard Sep 06 '16 at 13:06
  • @Fred-ii- Tried removing the leading 0. Still throwing back the same error. – Steve Dew Sep 06 '16 at 13:08
  • @JayBlanchard varchar(15) – Steve Dew Sep 06 '16 at 13:09
  • You sure you chose the right database and/or table? I honestly don't know why what I suggested would not work. @SteveDew – Funk Forty Niner Sep 06 '16 at 13:10
  • are you absolutely sure the SQL statement above is exactly what you have in your application? I set this up and tried it, as you have it posted and it works fine. The error being thrown indicates that you're trying to access a column named '031', not the value, as if you'd done something like `"UPDATE quotes SET Body='$Message' WHERE $Reference='whatever';"` – Robert Wade Sep 06 '16 at 13:10
  • @RobertWade Promise the query is: mysql_query("UPDATE quotes SET Body='$Message' WHERE Reference='$Reference';") or die(mysql_error()); – Steve Dew Sep 06 '16 at 13:12
  • The only thing I can think of is that maybe you have a PHP Constant named Reference in your application. Would that be a possibility? If you're defining 'Reference' as a constant, it would be accessible as just "Reference" without the "$", therefore 'Reference' in your where clause would be substituted with '031' which would explain why its failing on a column named '031'. Try changing your variable name to something else, and see if it fixes it. – Robert Wade Sep 06 '16 at 13:15
  • I have no idea what I have done, but this is no working perfectly? – Steve Dew Sep 06 '16 at 13:20
  • 1
    Write your SQL statement to a variable and echo it out on the page and see what it looks like, then share it with us. – Robert Wade Sep 06 '16 at 13:20
  • Ok, yes apologies folks, it seems those leading 00's were causing the problem. I am now stripping them out. Thank you all very much for your help! – Steve Dew Sep 06 '16 at 13:24
  • which is what I wrote originally wrote earlier *"Plus, if you do have a value of 031, then try changing it to 31 and then your $Reference = "031"; to $Reference = "31"; - If that works, **then the 0 is an issue.** Do a var_dump() to see what the query is also."* and is probably treated as an octal, where I deleted a comment about that earlier. @SteveDew So I was right from the get go myself. – Funk Forty Niner Sep 06 '16 at 13:26
  • This still doesn't explain the error though. Weird. – Robert Wade Sep 06 '16 at 13:28
  • 1
    @RobertWade There is a reason. See one of John Conde's answers http://stackoverflow.com/a/11165118/1415724 it has something to do with how MySQL stores varchar characters with leading zeros. Zerofill is the clincher here. See also http://stackoverflow.com/a/37528724/1415724 – Funk Forty Niner Sep 06 '16 at 13:31
  • @SteveDew You could have CAST'ed it which would have worked. http://dev.mysql.com/doc/refman/5.7/en/cast-functions.html and http://stackoverflow.com/a/37528724/1415724 – Funk Forty Niner Sep 06 '16 at 13:33
  • Very interesting. I set up a demo in MySQL workbench based exactly off of his schema, and it worked fine. I guess i get what you're saying... I just still don't understand why the column name error based off what he was using. It seems like the 'wrong' error in this case. It would seem like the error in that case would be more like `Row with Reference 031 not found` as opposed to `unknown column 031`. – Robert Wade Sep 06 '16 at 13:34
  • @SteveDew I posted an answer below for you to mark the question as solved. I posted it as a community wiki since I didn't want rep from this. – Funk Forty Niner Sep 06 '16 at 13:51

1 Answers1

0

Posting as a community wiki, since the issue was found in comments, being the leading zeros.

What you could have done also was to CAST it.

References:

The leading zero could very well be treated as an octal by MySQL and is being ignored.

Therefore, it's best not to store leading zeros as a row's value. I would suggest using an int type if you're going to be using integers for storage values.

You can always alter the column's type for this.

Community
  • 1
  • 1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141