I'm using a php variables in mysql,but getting error while trying to add the php variables in a field and insert. Here is my sql query:
INSERT INTO my_table (quantity,price) VALUES(quantity+$value,1000) WHERE id=$id;
thanks in advance.
-
Sounds like you want to update a row instead of inserting it?Qirel– Qirel2019-05-02 05:52:24 +00:00Commented May 2, 2019 at 5:52
-
2What is the db error?Lev Buchel– Lev Buchel2019-05-02 05:53:56 +00:00Commented May 2, 2019 at 5:53
-
nope i've timestamps too that i can store each individual insertion.milon hossain– milon hossain2019-05-02 05:54:18 +00:00Commented May 2, 2019 at 5:54
-
So you don't want to update a row? You want to insert a new row then? Your question is rather unclear, we can't read your mind or see what your goal here is unless you explicitly tell us.Qirel– Qirel2019-05-02 05:58:14 +00:00Commented May 2, 2019 at 5:58
-
ok sorry if i was not clear enough in the question, and yes i did not want to update the row. i want to insert each time.milon hossain– milon hossain2019-05-02 06:04:31 +00:00Commented May 2, 2019 at 6:04
|
Show 1 more comment
3 Answers
You can not insert rows with WHERE or by adding values (like you attempt with quantity+$value), as there are no values until after the insert has completed.
If you want to update an existing row,
UPDATE my_table
SET quantity = quantity + $value,
price = 1000
WHERE id = $id
Or if you want to insert a row,
INSERT INTO my_table (id, quantity, price)
VALUES ($id, $value, 1000)
Keep in mind that these queries are insecure, as they are not using prepared statements with placeholders.
2 Comments
milon hossain
thanks for your answer Qirel. just want to know how can i insert new row for specific product id then? my case is like whenever the customer buys a product i want to store the purchase record with timestamp.
Qirel
Create a table with a primary key (probably some arbitrary ID), the customer ID (or any other unique customer-identifier), a timestamp (just use the
CURRENT_TIMESTAMP default value) and the amount.I think you are trying to update your query
UPDATE my_table
SET quantity = quantity + $value, price= 1000
WHERE id = $id
3 Comments
milon hossain
i'm not and now i know i cant use the WHERE clause in an INSERT query, i need to insert each time not update as i want to track the insertions based on timestamps later. thanks.
Sarfaraz
INSERT into mytable (logins) SELECT max(logins) + 1 FROM mytable
Sarfaraz
you can use sub query similar to above query
you cannot insert row using the "WHERE".
for insert you can use:
INSERT INTO my_table (id, quantity, price) VALUES ($id, $value, 1000);
for update you can use:
UPDATE my_table
SET quantity = quantity + $value,
price = 1000
WHERE id = $id;
for reference you can see:
https://www.w3schools.com/sql/sql_insert.asp
https://www.w3schools.com/sql/sql_update.asp
Comments
