what is a problem with this code. I cannot go into insert function if table is empty, it is always doing update. Can anyone help me to solve this thing?
while ($row = $result->fetch_object()) {
if ($result->num_rows > 0) {
$updatequery = sprintf("UPDATE stat_mailings SET cat_id='%s', mailing_name='%s', mailing_unique_id='%s', segment_id='%s',
campaign_id='%s', landing_page='%s', total_sent='%s' WHERE mailing_id='%s'",
$row->category_id,
mysqli_real_escape_string($mysqli, $row->mailing_naam),
$row->unique_id,
$row->mailing_segment,
mysqli_real_escape_string($mysqli, $row->utm_campaign),
mysqli_real_escape_string($mysqli, $row->landing_page),
$row->mailing_total_sent,
$row->id
);
$mysqli->query($updatequery);
echo $error = $mysqli->error;
} else {
$insertquery = sprintf("INSERT INTO stat_mailings SET mailing_id='%s', cat_id='%s', mailing_name='%s', mailing_unique_id='%s', segment_id='%s',
campaign_id='%s', landing_page='%s', total_sent='%s'",
$row->id,
$row->category_id,
mysqli_real_escape_string($mysqli, $row->mailing_naam),
$row->unique_id,
$row->mailing_segment,
mysqli_real_escape_string($mysqli, $row->utm_campaign),
mysqli_real_escape_string($mysqli, $row->landing_page),
$row->mailing_total_sent
);
$mysqli->query($insertquery);
echo $error = $mysqli->error;
}
Mysql has great feature exactly for this purpose - ON DUPLICATE query.
It looks like
INSERT INTO t SET f1=val,f2=val2 ON DUPLICATE KEY UPDATE f1=val,f2=val2
and so on.
Only make sure you have an UNIQUE index on the key field.
So, you can get rid of your SELECT query and it's result checking.