p19lord p19lord - 1 year ago 81
MySQL Question

INSERT INTO doesn't work in php codes

I know this question is sort of dumb but I can't find out where the problem is I checked it with the codes in documentation and similar codes in stackoverflow but I can't figure out the problem.
this is my code:

if (isset($_POST['buy'])) {
$id = (int) $_POST['id'];
$name = $_POST['name'];
$price = (int) $_POST['price'];
$date = date("Y-m-d h:i:sa");
$insquery = "INSERT INTO `purchases` (file_id, file_name, price, date) VALUES ({$id}, '{$name}', {$price}, {$date})";
$insResult = mysqli_query($con, $insquery);
if ($insResult) {
//do sth
} else {
//do sth else

I have tested these:
1- the post array is not empty and returns exactly those that I assigned to variables.
2- I have a table called purchases and it configured properly because I insert data in SQL and get it back successfully.
3- I have tried on SQL statement without {} around SQL variables but no luck.

and another question is after the SQL statement done how can I use the
OUTPUT Inserted.ID
as a variable in PHP?
thanks in advance.

Answer Source
  1. date is a keyword in MySql. So use backtick (`).

INSERT INTO purchases (`file_id`, `file_name`, `price`, `date`) ...

  1. Instead of using direct substitution values, you could use below methods to avoid sql injection.

Using MySQLi (for MySQL):

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";

Please refer How can I prevent SQL-injection in PHP?

  1. Use mysqli::$insert_id for last inserted ID (Docs here)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download