如果数据库中存在字段，则更新价格，否则插入全部

Question

i need somehow to check if field exist in db, and then if exist, just update price, if not, insert fields. I am using simplxml for parsing data to db from xml.

here is my code without if statement, just insert into two databese.

I need to check from db products if ident exist, so if not exist, do all that code down, if exist just update price in db products

foreach ($lib->product as $data) {
    $manufacturer = (string) $data->manufacturer;
    $ident = (string) $data->id;
    $name = (string) $data->name;
    $category = (string) $data->category;
    $subcategory = (string) $data->subcategory;
    $price = (int) ($data->price * 1.2 * 1.4 * 1.1);
    $image = (string) $data->images->image[0];


    $insert = $db->prepare('INSERT INTO products (ident, manufacturer,name,category,subcategory,price,image) VALUES (?, ?, ?, ?, ?, ?, ?)');
    $insert->bind_param('sssssss', $ident, $manufacturer, $name, $category, $subcategory, $price, $image);
    $insert->execute();


    foreach($data->specifications->attribute_group as $group) {
        $attribute_group = (string) $group->attributes()['name'];

        foreach($group as $attr) {
            $attribute = (string) $attr->attributes()['name'];
            $value = (string) $attr->value;
            $insert = $db->prepare('INSERT INTO spec (attr_group,attr_name, attr_value, product_id) VALUES (?, ?, ?, ?)');
            $insert->bind_param('ssss', $attribute_group, $attribute, $value, $ident);
            $insert->execute();

        }

    }

}

Answer 1

To do in one query, look up MySQL's ON DUPLICATE KEY UPDATE functionality for INSERT.

Then use $insert->rowCount() if you're using PDO or $insert->affected_rows for mysqli.

If the first insert tried to already insert a key that existed and updates a value, then rowCount()/affected_rows will be 2; if it just inserted a record then rowCount()/affected_rows will be 1. It will be 0 if the INSERT was unsuccessful.

e.g. for PDO:

switch($insert->rowCount()) {
    case 2:  // UPDATE occurred thanks to 'ON DUPLICATE UPDATE KEY'
        // SOME CODE HERE IF YOU LIKE
        break;
    case 1:  // INSERT occurred as no duplicate
        // CODE TO INSERT INTO SECOND TABLE
        break;
    case 0:
    default:
        // NEITHER THE ABOVE OCCURRED SO CODE TO HANDLE ERROR
}