CODEWITHSUNDEEP
phpmysql
fish man
fish man

Reputation: 2720

how to use one mysql query, update data without duplicate?

id | title | text
1  | aa    | 
2  | aa    |
3  | aa    |

I have some data from json data, i am not sure how many datas have duplicate in it. 

$json = '[{"a":"1","b":"bb"},{"a":"2","b":"cc"},{"a":"3","b":"bb"}]';
$array = json_decode($json);
foreach($array as $key){
    UPDATE table SET text = '".$key->b."' WHERE id = '".$key->a."' and title='aa'");
}

For example, as this situation, $key->b has 2 data bb from the json data, I only want update the first one and check if bb has already in the database, then ignore update.

id | title | text
1  | aa    | bb
2  | aa    | cc
3  | aa    |      <-ignore updtae, left the data empty

I know there have an easy way, first select * from table where text != '$key->a' for check, but this will cost 2 mysql query and make one more foreach, so

how to use one mysql query, update data without duplicate?

many thanks.

Upvotes: 0

Views: 308

Answers (2)

Kaivosukeltaja
Kaivosukeltaja

Reputation: 15735

I suggest using an array to store all the values of b you have used so far and only run the UPDATE if the value didn't exist yet.

$json = '[{"a":"1","b":"bb"},{"a":"2","b":"cc"},{"a":"3","b":"bb"}]';
$usedValues = array();
$array = json_decode($json);
foreach($array as $key){
    if(!isset(usedValues[$key->b])) {
        mysql_query("UPDATE table SET text = '".$key->b."' WHERE id = '".$key->a."' and title='aa'");
        usedValues[$key->b] = true;
    }
}

EDIT: If your database already has values for text this may still produce duplicates, but you could do a SELECT DISTINCT ´text´ and populate the $usedValues array with them.

Upvotes: 0

Emmanuel Sys
Emmanuel Sys

Reputation: 815

If your database is MySQL, maybe you can use the ON DUPLICATE KEY UPDATE.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Upvotes: 1

Related Questions