How Can i Insert Value in Mysql if it is Unknown?

Question

Hello Friend I need Help About Mysql. My Database Look Like This
Table name = users

Rows

id = "7" | YourLastIP = ""

MY Query is:

UPDATE users SET `YourLastIP` = `XX.XX.XX.XX` WHERE `id` = `7`

I want to insert but it also wont work for me please guide me

 INSERT INTO Users `YourLastIP` = (`XX.XX.XX.XX`) WHERE `id` = `7`

Please Help me How to Insert A value if table value not known.

Answer 1

You are using backticks which means in MySQL that you're meaning a column name, not text. Use single quotes ' instead.

Answer 2

First, the problem is that you have wrap the value with backtick, which is suppose to be single quote since it is a string literal.

UPDATE users SET `YourLastIP` = 'XX.XX.XX.XX' WHERE `id` = '7' 

Second, use INSERT ... ON DUPLICATE KEY UPDATE if you want to UPDATE existing ID or otherwise INSERT if it does not exist.

INSERT INTO users(ID, YourLastIP)
VALUES (7, 'ip_here')
ON DUPLICATE KEY UPDATE YourLastIP = 'ip_here'

• INSERT ... ON DUPLICATE KEY UPDATE Syntax

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

• How to prevent SQL injection in PHP?

Answer 3

Make the column NULLABLE in definition

Then,

CREATE TABLE PRO(ID INT, YOURLASTIP VARCHAR(15));
INSERT INTO PRO values(4, "xxx.xxx.xxx.xxx");