The code below is part of a simple password manager. I get an error saying the Query is empty yet the query works just fine in MySQL. (The 1 and the test value were originally variables I just changed them to values as part of my troubleshooting). I am also aware that the column names user and password may be problematic, but I added ` around them. What else could be wrong with that code?
$change_pass_query = "UPDATE `user` SET `password` = PASSWORD('test') WHERE id = 1";
$change_pass_result = mysql_query($change_pass_query) or die('Error. Change Password Query failed: '. mysql_error());
Try formatting your SQL like this:
UPDATE `user` SET `password` = 'test' WHERE `id` = 1
http://php.net/manual/en/function.mysql-query.php
Notice the warning at the top of that page. Nobody uses mysql_query or any plain mysql functions. Research mysqli/mysqli_query, and PDO.
Here's how you could do this with PDO:
$pdo = new PDO("mysql:host=localhost;dbname=mydb","username","password");
$stmt = $pdo->prepare("UPDATE `user` SET `password` = PASSWORD(:password) WHERE id = :id");
$result = $stmt->execute(array(':password' => "test",':id' => 1));
if (!$result) die('Error. Change Password Query failed: '. mysql_error());
Here's some documentation on PDO: http://php.net/manual/en/book.pdo.php
I ended up renaming all tables and fields so that I didn't use any reserved words, as I thought that the issue might be that. The problem still happened. I then copied my code to a different PHP box, et voila, the code works just fine. I'll have to put it down to an issue with the PHP version/installation on the older box and move on. There is nothing wrong with the code.