Mysql INSERT query of variables in PHP

Question

I have spent two days trying to figure out why this statement wouldn't insert those data into my database. Been reading through all similar questions here as well and tried with no luck. This is the statement:

$sql = "INSERT INTO removal_shipment_detail (request_date, order_id, shipment_date, product, product_name, delivered, quantity, local, tracking, update) VALUES ('".$request_date."','".$order_id."','".$shipment_date."','".$product."','".$product_name."','".$delivered."','".$quantity."','".$local."','".$tracking."','".$update."')";

Where quantity is a INT here. I've exchanged single quote with double quote back and force combined with the concatenation added and deleted as well, but none of those combinations would work.

Thanks, please advice!

It may be your data is crossing available limit of data types or data mismatched. Check datatype and character length — milan kyada, Nov 20 '16 at 08:39

score 3 · Answer 1 · edited May 23 '17 at 12:13

3

update is a MySQL reserved word, so you can't use this word as your column name like that.

Here's the reference:

http://dev.mysql.com/doc/refman/5.7/en/keywords.html

Escape your columns using backticks, like this:

$sql = "INSERT INTO removal_shipment_detail (`request_date`, `order_id`, `shipment_date`, `product`, `product_name`, `delivered`, `quantity`, `local`, `tracking`, `update`) VALUES ('".$request_date."','".$order_id."','".$shipment_date."','".$product."','".$product_name."','".$delivered."','".$quantity."','".$local."','".$tracking."','".$update."')";

Sidenote: Learn about prepared statements because right now your query is susceptible to SQL injection. Also see how you can prevent SQL injection in PHP.

edited May 23 '17 at 12:13

Community

1
1

answered Nov 20 '16 at 08:20

Rajdeep Paul

16,887
3
18
37

Thank you! Yeah I am still a noob and there are a lot for me to learn. Thanks for your tip I will try and then back with an update. Thanks for your supported info as well! :D – Hang Nov 20 '16 at 08:26
Sorry, but this seem not to work either :( I will read through your material and see what to add – Hang Nov 20 '16 at 08:29

Karol Gasienica · Answer 2 · 2016-11-20T08:34:56.787

0

If you are using MySQLi you can check your SQL errors with following methods:

Object oriented style

if (!$mysqli->query("QUERY_HERE")) {
    printf("Errormessage: %s\n", $mysqli->error);
}

Procedural style

if (!mysqli_query($con, "QUERY_HERE")) {
    printf("Errormessage: %s\n", mysqli_error($con));
}

More informations can be found in PHP: mysqli:$error

Also I dont like connecting string with . when it's not necessary (it' always harder to read). Your syntax can be as follows:

$sql = "INSERT INTO removal_shipment_detail (`request_date`, `order_id`, `shipment_date`, `product`, `product_name`, `delivered`, `quantity`, `local`, `tracking`, `update`) VALUES ('$request_date','$order_id','$shipment_date','$product','$product_name','$delivered','$quantity','$local','$tracking','$update')";

edited Nov 20 '16 at 08:34

answered Nov 20 '16 at 08:21

Karol Gasienica

2,825
24
36

Wow! Thanks for your prompt reply! Let me check on that and update... – Hang Nov 20 '16 at 08:24
Hi! I copied your code into my PHP and the first one didn't print out anything and the second one only says "Errormessage:" on the webpage. Does it mean it has no error?? Thanks! – Hang Nov 20 '16 at 08:34
Show me your way how you use mysqli :) – Karol Gasienica Nov 20 '16 at 08:35
if (!mysqli_query($con, $sql)) { printf("Errormessage: %s\n", mysqli_error($con)); } – Hang Nov 20 '16 at 09:38
Is this the right way :D? – Hang Nov 20 '16 at 09:38
It depends on how is it done in your code. Post something more of code in your question. – Karol Gasienica Nov 20 '16 at 09:38

score 0 · Answer 3 · answered Nov 20 '16 at 08:32

try to use intval

$sql = "INSERT INTO removal_shipment_detail 
    (request_date, order_id, shipment_date, product, product_name, delivered, quantity, local, tracking, update) 
VALUES
    ('".$request_date."','".$order_id."','".$shipment_date."','".$product."','".$product_name."','".$delivered."',".intval ($quantity).",'".$local."','".$tracking."','".$update."')";

score -1 · Answer 4 · answered Nov 20 '16 at 08:24

Firstly, if quantity is an INT, then it need not be surrounded with quotes.

Secondly, avoid using reserved words such as "update" for your field names. It may confuse the database engine.

Rajdeep Paul's answer is also good but he failed to talk about the quantity field which has the INT data type.

Try this

$sql = "INSERT INTO removal_shipment_detail (request_date, order_id, shipment_date, product, product_name, delivered, quantity, local, tracking, update1) VALUES ('".$request_date."','".$order_id."','".$shipment_date."','".$product."','".$product_name."','".$delivered."',".$quantity.",'".$local."','".$tracking."','".$update."')";

Notice that the field name "update" has been changed to "update1" to avoid confusing of the sql interpreter and also the quotes surrounding the quantity value have been removed.

I hope this helps.

Thank you Shola! I tried but still the data doesn't show in my database. That's so weird... The connection is good without a problem. I'm debugging it by adding the data one by one. — Hang, Nov 20 '16 at 08:40

Mysql INSERT query of variables in PHP

4 Answers4