如果数据库中存在字段，则更新价格，否则插入全部
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();
}
}
}
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
}
$query = "SELECT * name FROM yourtable";
$bool = true;
if($r = @mysql_query($query))
{
while($row = @mysql_fetch_array($r))
{
if( ($row['id'] == $id))
{
$bool = false;
break;
}
}
}
if($bool) {
UPDATE
}
else
{
INSERT
}