How can i update a value if it is exsiting in the database, or else i want to insert it

Question

I have a table called PriceIK(priceCount,propertyID,bestPrice).

now i want to insert a record along with propertyID and bestPrice (priceCount is auto increment) If the propertyID is existing, i want to update the respective bestPrice value. If property value is not existing, i want to insert the new record (propertyID and bestPrice)

Im using php and phpMyAdmin
Im a beginner and i really need your help guys thank you in advance

Which connectivity are you using. You have not mentioned that. @Ishi Silva — Naresh Kumar P, Sep 05 '16 at 05:47
where is your code.. please edit your question and insert your code so that can guide you.. — FullStack, Sep 05 '16 at 05:50
Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — ASR, Sep 05 '16 at 05:55
@Ishi Silva. I have provided the code below to solve your problem as per your requirement. — Naresh Kumar P, Sep 05 '16 at 06:51

Milen Louis · Accepted Answer · 2016-09-05T06:55:23.707

1

From what I understood, this is the thing you need.

INSERT INTO PriceIK (propertyID,bestPrice) VALUES(1 , 5000) ON DUPLICATE KEY UPDATE bestPrice="5000"

I have used propertyID=1 and bestPrice=5000 as default

Hope this helps

edited Sep 05 '16 at 06:55

answered Sep 05 '16 at 06:03

Milen Louis

223
4
11

Hi @Milen, i have added a new row to table! now table structure is priceIK(propertyID,bestPrice,bestURL) as same as above, i want to update the bestURL values as well. can you suggest me a way please :) thank you – Ishi Silva Sep 05 '16 at 06:38
Hey @Ishi , On what criteria are you trying to add bestURL. Is the project you are working related to e-Marketing sites? If so, you should have a separate table for URL. That table should contain the bestPrice field as well. Then, if a propertyID is selected, search for the bestURL from the table matching the productID. Then update the entire row using UPDATE. Okay? :) – Milen Louis Sep 05 '16 at 07:09
INSERT INTO PriceIK (propertyID,bestPrice,bestURL) VALUES(1 , 5000, defaultURL) ON DUPLICATE KEY UPDATE bestURL="yoururl" – Milen Louis Sep 05 '16 at 07:22

score 1 · Answer 2 · answered Sep 05 '16 at 06:27

Inorder to perform this you can prefer using the MySQL INSERT ON DUPLICATE KEY UPDATE statement.

The INSERT ON DUPLICATE KEY UPDATE is a MySQL extension to the INSERT statement. If you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement and the new row causes a duplicate value in the UNIQUE or PRIMARY KEY index, MySQL performs an update to the old row based on the new values.

The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows:

INSERT INTO table(column_list)
VALUES(value_list)
ON DUPLICATE KEY UPDATE column_1 = new_value_1, column_2 = new_value_2, …;

The only addition to the INSERT statement is the ON DUPLICATE KEY UPDATE clause where you specify a list of comma-separated column assignments.

MySQL returns the number of affected rows based on the action it performed.

If MySQL inserts the row as a new row, the number of affected row is 1.
If MySQL updates the current row, the number of affected rows is 2.
In case MySQL updates the current row with its current values, the number of affected rows is 0.

Insert Statement:

INSERT INTO devices(name) VALUES ('Printer') ON DUPLICATE KEY UPDATE name = 'Printer';

This is a Insert statement since the devices table does not contain the Printer value and hence it will Insert it.

Update Statement:

INSERT INTO devices(id,name) VALUES (4,'Printer') ON DUPLICATE KEY UPDATE name = 'Server';

This will update the table since the already the Printer is present so that it will update the table values as Server.

`INSERT INTO devices(propertyID,bestPrice,bestURL) VALUES (4,'5000','http://yahoo.com') ON DUPLICATE KEY UPDATE bestURL= 'http://www.google.co.in';` As per your requirement this might be the solution @Ishi Silva. — Naresh Kumar P, Sep 05 '16 at 06:46
This will update the bestURL as `http://www.google.co.in` if the bestURL is `http://yahoo.com` conataining the propertyID with 4 — Naresh Kumar P, Sep 05 '16 at 06:47
If the `propertyID` is `4` and you need to update the `bestURL` for this `propertyID` this above code that i suggested will be the best option @Ishi Silva. Hope so this helps you to solve your problem — Naresh Kumar P, Sep 05 '16 at 06:50

score 0 · Answer 3 · edited Sep 05 '16 at 06:30

0

$sql="select * from PriceIK where propertyID='$_POST['property']'";
$query=mysqli_query($con,$sql);// Here `$con` is the DB connectivity variable
$num=$query->num_rows;// This will get the count of the executed query
if($num>0){         
$up="Update PriceIK set bestPrice='$_POST['price']' where propertyID='$_POST['property']'";
$qry=mysqli_query($con,$up); // Here `$con` is the DB connectivity variable
}else{          
$ins="Insert into PriceIK(propertyID,bestPrice) values('".$_POST['property']."','".$_POST['price']."')";
$qry=mysqli_query($con,$ins);  // Here `$con` is the DB connectivity variable       
}

Try it

edited Sep 05 '16 at 06:30

Naresh Kumar P

4,127
2
16
33

answered Sep 05 '16 at 05:54

premi

93
10

Your mysql connectivity is wrong and it will not work. You have to use `$conn` or your DB connectivity variable in the `mysqli_query`. Follow the documentation http://php.net/manual/en/mysqli.query.php – Naresh Kumar P Sep 05 '16 at 06:03
why you want to insert new record if the record is already existed.?? – FullStack Sep 05 '16 at 06:06
I didn't write connection that's write. But that is a basic step that every one knows. Here we didn't type step by step. – premi Sep 05 '16 at 06:08
@premi. Your Code looks incorrect. You have misunderstood the Question. If the row is present you have to update orelse you need to insert. – Naresh Kumar P Sep 05 '16 at 06:09
@premi.. i told you to midify the code as per `mysqli.*` conenctivity documentation – Naresh Kumar P Sep 05 '16 at 06:13
@premi.I have modified your code as per the `mysqli.*`. have a check – Naresh Kumar P Sep 05 '16 at 06:31
@premi. If so you mind it you can use the query has how i have suggested in my answer for this question since it will be very easy to do so rather than this long method. if you find it useful you can upvote for future reference. :) Thanks.. – Naresh Kumar P Sep 05 '16 at 06:32

score 0 · Answer 4 · answered Sep 05 '16 at 05:55

0

Use INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

answered Sep 05 '16 at 05:55

Blaztix

1,223
1
19
28

score 0 · Answer 5 · answered Sep 05 '16 at 05:57

0

Try the following code in PHP

$sql = "SELECT * FROM PriceIK WHERE propertyID = " . $propertyId;
$result = $connection->query($sql);

$persistent_sql = '';

if ($result->num_rows > 0) {
  $persistent_sql = "UPDATE PriceIK SET bestPrice = " . $bestPrice;
}
else {
  $persistent_sql = "INSERT INTO PriceIK(propertyID, bestPrice)   VALUES(".$propertyId.", ".$bestPrice.") ";
}

mysqli_query($connection, $persistent_sql)

answered Sep 05 '16 at 05:57

Pragash

713
7
9

Instead of this you can use mysql `DUPLICATE KEY` for Inserting a new one or Updating a Existing One. – Naresh Kumar P Sep 05 '16 at 05:59

How can i update a value if it is exsiting in the database, or else i want to insert it

5 Answers5