How to update database using PHP variables?

Question

$Createdby=$_SESSION['adminlog'];
$total =$_POST['total'];
$due =$_POST['due'];
$date =$_POST['issedate'];
$invoiceno =$_POST['invno'];
$CmpnyName =$_POST['CmpnyName'];
$itemdetails =$_POST['item_details'];
$itemname =$_POST['itemname'];
$amtpaid =$_POST['paid'];



$query  = "UPDATE billdata SET Total='$total' Due='$due' WHERE InvoiceNo=$invoiceno";

$result = mysql_query($query);

This is the code I am using to get HTML values to variable and update particular invoice number with new data.

What is going wrong ? You should first connect to your database using mysql_connect(); Do not forget to check for SQL injection with mysql_real_escape_string() in your case. — jde, Jun 21 '15 at 08:51
add thing done db connected ,select db n bla bla with this same method i used save (insert) details to table so now m trying to renew (update) selected bill but this method not working — Sampath Munaweera, Jun 21 '15 at 08:54
Your code is a schoolbook example of `sql injection` vulnerable code. Swap from the **deprecated** and unsafe `mysql_*` API to either `mysqli` or `PDO`, and use `prepared statements` or at the least escape/validate the input data. — Jite, Jun 21 '15 at 08:56

score 4 · Accepted Answer · edited Dec 13 '19 at 16:20

First off, never use the deprecated mysql_* API.
Switch to either PDO or mysqli, both have prepared statements, which would make your code a tad bit more safe when it comes to SQL-Injections (which your code is very open for).

When a query fails, the mysql_error() global function will return the latest mysql error.
The easiest way to get information about a failing query is by adding or die(mysql_error()); after the query execution.
Example with your code:

$result = mysql_query($query) or die(mysql_error());

This will report your error and stop execute the script.

Your sql code is slightly wrong (as RST mentions), you are missing a comma between the values you are trying to set.

Using mysqli and prepared statements, your code could look something like:

// Using the mysqli object oriented style.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'username', 'password', 'database');

// Prepare the statement.
$statement = $mysqli->prepare('UPDATE billdata SET Total=?, Due=? WHERE InvoiceNo=?');
// The question marks is placeholders for the input that will be added in a while.

// Bind your parameters (ssi tells mysqli what type of params it is, s = string, i = int).
$statement->bind_param('ssi', $total, $due, $invoceno);
// Execute the statement.
$statement->execute();

// Cleanup.
$statement->close();
$mysqli->close();

thanks Jite can i know deference between mysql & mysqli ? i learnt about php my admin ( localhost ) using xammp server ! is it this mysqli is supporting to xammp server ? — Sampath Munaweera, Jun 21 '15 at 09:29
I think that most standard php installation packages includes the mysqli extension, it should be enabled by default. `mysqli` stands for Mysql Improved. It includes stuff like prepared statements (which does not exist in the old api). Its still using the `mysql` database and the same queries can be used, its even possible to convert old `mysql_*` code to `mysqli` using its procedural style, even though I personally recommend moving over to the OOP style. For more info, id recommend the PHP docs: http://php.net/manual/en/book.mysqli.php — Jite, Jun 21 '15 at 09:34

score 0 · Answer 2 · answered Jun 21 '15 at 08:51

0

$query  = "UPDATE billdata SET Total='$total', Due='$due' WHERE InvoiceNo=$invoiceno";

There should be a comma between the sets of values. It is not a good idea to use the value from $_POST() as they are, better perform some validation checks.

answered Jun 21 '15 at 08:51

RST

3,899
2
20
33

$query = "UPDATE billdata SET Total='$total', Due='$due' WHERE InvoiceNo='$invoiceno";' this should be and it works Thanks for your support – Sampath Munaweera Jun 21 '15 at 09:12

How to update database using PHP variables?

2 Answers2