Using a form to update data in MySQL

Question

Having trouble getting my form to UPDATE records in my database even after searching the web and viewing the other answers on stack-overflow.

Here is my current NON functioning code:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {

session_start();
$tablename = $_SESSION['MM_Username'];
$amount=$_POST['amount'];
$UpdateQuery = "UPDATE '" . $tablename . "' SET stock = '" . $amount . "' WHERE status = 1";
mysql_query($UpdateQuery);

}

The table i want to update has the same name as the SESSION variable MM_Username. I have a form with a textbox named amount and a Submit button that when clicked, should trigger the above code. If you need to know anything else let me know. Thanks in advance!

Answer 1

Print out if you are having your tablename in your session variable.

print $_SESSION['MM_Username'];

Also print out the $UpdateQuery and see how the mysql query is formed. Copy that query & try running it manually in mysql to see if the query is ok.

ADVISE: I see that you have used $_POST. This is fine, but I advise you to use $_REQUEST. This var in PHP has all $_POST & $_GET content. Sometimes one forgets to change the $_POST to $_GET or vice versa & ends up wasting his time, debuggin.

Answer 2

You're using the wrong quotes around your table name. Also, your query is open to SQL injection. Consider using PDO and bind parameters.

$UpdateQuery = sprintf('UPDATE `%s` SET `stock` = :amount WHERE `status` = 1',
                       $tablename);
$stmt = $pdo->prepare($UpdateQuery);
$stmt->bindParam('amount', $amount);
$stmt->execute();

Answer 3

Have MySQL tell you what the problem is. Change the last line of your code to this:

if (!mysql_query($UpdateQuery)) {
    echo mysql_error();
}

Answer 4

if (!mysql_query($UpdateQuery)) { echo mysql_error() }