INSERT INTO doesn't work in php codes

Question

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_default_timezone_set("Europe/London");
 $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.

try to debug $insResult = mysqli_query($con, $insquery) or die(mysqli_error($con)); — Passionate Coder, Aug 09 '16 at 11:00
Troubleshooting such things: (1) check for errors returned from `mysql_query()`. (2) display the text of query from your php program right before you run it. (3) You probably need quote marks around the date information in your query. — O. Jones, Aug 09 '16 at 11:01
LAST_INSERT_ID() is a MySQL function yielding the most recently inserted autoincrement id. — O. Jones, Aug 09 '16 at 11:02
Try to print insert query - echo $insquery and execute directly in phpmyadmin to check . — Afshan Shujat, Aug 09 '16 at 11:05
i get this error: `You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUTPUT Inserted.ID (file_id, file_name, price, date) VALUES (1, 'طراحی وب' at line 1` — p19lord, Aug 09 '16 at 11:07

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

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

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

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";
$stmt->execute();

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

Use mysqli::$insert_id for last inserted ID (Docs here)

edited May 23 '17 at 12:33

Community

1
1

answered Aug 09 '16 at 11:06

Tamil

1,193
9
24

*"date is a reserved keyword in MySql. So use backtick"* - That is false, it is just a "keyword" and not a "reserved" word; there is no "reserved keyword" term. Look for yourself https://dev.mysql.com/doc/refman/5.7/en/keywords.html - there is no `(R)` next to it. So please lose that misconception and pass on false information to others. – Funk Forty Niner Aug 09 '16 at 11:44
@Fred-ii- : Thanks for your correction and i have updated my answer. – Tamil Aug 09 '16 at 12:05

INSERT INTO doesn't work in php codes

1 Answers1