CODEWITHSUNDEEP
phpmysqlsqldatabaseinsert
Zayn
Zayn

Reputation: 71

SQL : INSERT if no exist and UPDATE if exist

I have a program that can perform inserts and updates to the database, I get the data from API. This is sample data when I get:

$uname    = $get['userName'];
$oname    = $get['offerName'];
$visitdata= $get['visits'];
$convdata = $get['conversion'];

I save this data to database sql. (sucess) this is a sample:

$sql = "INSERT INTO data_tester(username_data, name_offer_data, visit_data, conversion_data) VALUES('$uname','$oname', '$visitdata', '$convdata')";

Sample data in database table 

id | username_data | name_offer_data | visit_data | conversion_data
1  | MOJOJO        | XXX AU          | 177        | 13
2  | MOJOJO        | XX US           | 23         | 4

Now, I want to save data $uname, $oname, $visitdata, $convdata if NOT EXIST and UPDATE $visitdata, $convdata where $uname, $oname if EXIST

How to run the code with a simple query. Please give me an example. Thank you.

Upvotes: 0

Views: 533

Answers (4)

Ruslan Bes
Ruslan Bes

Reputation: 2735

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

Upvotes: 1

Kevin Yan
Kevin Yan

Reputation: 1236

Please ensure there is a unique key on column username_data, if so Mysql's ON DUPLICATE KEY UPDATE is suitable for this case, the SQL statement is like that:

$sql = "INSERT INTO data_tester(username_data, name_offer_data, visit_data, 
conversion_data) VALUES('$uname','$oname', '$visitdata', '$convdata')
ON DUPLICATE KEY UPDATE username_data = '$uname', name_offer_data = 
'$oname', visit_data = '$visitdata', conversion_data = '$convdata'"

Upvotes: 0

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

You can simply use replace into command instead of insert into command.

$sql = "REPLACE INTO data_tester(username_data, name_offer_data, visit_data, conversion_data) VALUES('$uname','$oname', '$visitdata', '$convdata')";

It is one of mysql good and useful feature. I used it many times.

Upvotes: 0

JoelBonetR
JoelBonetR

Reputation: 1572

it's simple, try this:

if(isset($get['userName'])){
$sql = "SELECT * FROM data_transfer WHERE userName = ".$userName.";";
$result = connection()->query($sql);
$rs = mysqli_fetch_array($result);
connection()->close();
    //if is not void, means that this username exists
if ($rs != ''){
    mysqli_free_result($result);
    //InsertData

}
else{
   mysqli_free_result($result);
    //UpdateData
}

*chech that you have to use your PrimaryKey on where clause to ensure there are only one of this. if you use an ID and you don't get it by $_GET, you'll have to modify something to ensure non-duplicated data. For example, checking that userName cannot be duplicated or something similar

Upvotes: 0

Related Questions