PHP MySQL Update If Exist or Insert If Not

PHP MYSQL UPDATE if Exist or INSERT if not?

I believe you are looking for the following syntax:

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

Note: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

MySQL Documentation: INSERT ... ON DUPLICATE KEY UPDATE Statement

Insert into a MySQL table or update if exists

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

If Record exists UPDATE MySql Table or else INSERT using PHP

i am giving you simple demonstration here how to achieve that. first you need to do select data.something like this.

"SELECT * FROM request WHERE agency_id='$agency_id' ";

Now check if the data exist make update query

"UPDATE request SET field name='$value' WHERE agency_id='$agency_id'";

otherwise something like this.

INSERT INTO forex_rates_new (`agency_id`,`currency_name`,`rate`,`logo`,`created_at`) 
VALUES('".$agency_id."','".$currency_name."','".$rate."','".$logo."', now())

Your code should be like this.

$qry=mysqli_query($con,"SELECT * FROM request WHERE agency_id='$agency_id' ");
$rowCheck=mysqli_num_rows($qry);
    if ($rowCheck>0) { // if data exist update the data
        $qry=mysqli_query($con,"UPDATE request SET field name='$value' WHERE agency_id='$agency_id'");  
    }
    else{ // insert the data if data is not exist
        $qry=mysqli_query($con,"INSERT INTO request (field name) VALUES('$value')");
    }

Remember given code is open to sql injection attack. this is logic
only this is not full code.to avoiding sql injection attack use
prepared statements.

SQL update if exist, insert if not exist not working

adding a second key to the customer_id made this working:

$sqlsms = "INSERT INTO sms (number, customer_id, stat) VALUES('$phone', '$compID', '$smsPromo') ON DUPLICATE KEY UPDATE stat = '$smsPromo'";

SQL : INSERT if no exist and UPDATE if exist

The feature you are looking for is called UPSERT and it is the part of SQL-2008 Standard. However not all DBMS-s implement it and some implement it differently.

For instance on MySQL you can use:

INSERT ... ON DUPLICATE KEY UPDATE

syntax (link to docs)
or

REPLACE INTO

syntax (link to docs).

These methods require you to have a proper PRIMARY KEY: (username_data name_offer_data) in your case.

Some PHP frameworks support this feature too provided you are using ActiveRecord (or similar) class. In Laravel it is called updateOrCreate and in Yii it is called save(). So if you are using a framework try to check its documentation.

If you are using neither framework nor modern DBMS you have to implement the method yourself. Run SELECT count(*) from data_tester WHERE username_data = ? AND name_offer_data = ?, check if it returned any rows and call an appropriate UPDATE/INSERT sql

MySql Table Insert if not exist otherwise update on non-unique columns

You are looking for insert . . . on duplicate key update. But first, you need a unique index:

create unique index unq_test_userID_name on test(user_ID, name)

Then you can do an insert:

insert into test(user_ID, name, value)
    values (36, 'colvis', '3')
    on duplicate key update value values(val);

A big caveat here: Storing multiple values in a comma-delimited list is almost never the right thing to do. There are rare exceptions to this rule, which is why I've answered the question.

The right solution is probably a junction table, TestValues, with one row per "test" and one row per "value". In that case, what you want to do is easy:

insert into TestValues(test_id, value)
    values (test_id, val);

---

Related Topics