I am creating a web page where you can edit local listing sites for clients. I am using the INSERT ... ON DUPLICATE KEY UPDATE syntax in mysql and php. Below is the code:
edit-listings-exec.php
<?
//Array to store validation errors
$errmsg_arr = array();
//Validation error flag
$errflag = false;
$member_id = $_SESSION['MEMBER_ID'];
$userresult = mysqli_query($link, "SELECT username
FROM members
WHERE member_id = '$member_id'");
$row = mysqli_fetch_array($userresult);
$username = $row['username'];
//run a for loop to check for duplicates
for ($i=1; $i < 10; $i++) {
$selectbox = $_POST['listing'.$i];
for ($j=1; $j < 10; $j++) {
if ($j != $i) {
if ($_POST['listing'.$j] === $selectbox && $_POST['listing'.$j] != "") {
$errmsg_arr[] = "Duplicate listing source '$selectbox'";
$errflag = true;
break;
}
}
}
}
//if there are input validations redirect back to main page
if($errflag) {
$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
session_write_close();
header("location: edit-listings.php");
exit();
}
//run loop to define post variables
for ($k=1; $k < 10; $k++) {
${'listing'.$k} = mysqli_real_escape_string($link, $_POST['listing'.$k]);
${'listing'.$k.'_site'} = mysqli_real_escape_string($link, $_POST['listing'.$k.'_site']);
}
//insert listings into local_listings table for the user
$qry = "INSERT INTO local_listings (
member_id,
username,
listing1,
listing1_site,
listing2,
listing2_site,
listing3,
listing3_site,
listing4,
listing4_site,
listing5,
listing5_site,
listing6,
listing6_site,
listing7,
listing7_site,
listing8,
listing8_site,
listing9,
listing9_site)
VALUES (
'$member_id',
'$username',
'$listing1',
'$listing1_site',
'$listing2',
'$listing2_site',
'$listing3',
'$listing3_site',
'$listing4',
'$listing4_site',
'$listing5',
'$listing5_site',
'$listing6',
'$listing6_site',
'$listing7',
'$listing7_site',
'$listing8',
'$listing8_site',
'$listing9',
'$listing9_site')
ON DUPLICATE KEY UPDATE
listing1 = '$listing1',
listing1_site = '$listing1_site',
listing2 = '$listing2',
listing2_site = '$listing2_site',
listing3 = '$listing3',
listing3_site = '$listing3_site',
listing4 = '$listing4',
listing4_site = '$listing4_site',
listing5 = '$listing5',
listing5_site = '$listing5_site',
listing6 = '$listing6',
listing6_site = '$listing6_site',
listing7 = '$listing7',
listing7_site = '$listing7_site',
listing8 = '$listing8',
listing8_site = '$listing8_site',
listing9 = '$listing9',
listing9_site = '$listing9_site'";
$result = mysqli_query($link, $qry);
if (mysqli_affected_rows($result) < 1) {
$errmsg_arr[] = "Unable to insert listings for $username";
$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
header ("location: edit-listings.php");
}
elseif (mysqli_affected_rows($result) === 1) {
$_SESSION['RESULT'] = "New listings for '$username' inserted successfully";
header ("location: edit-listings-success.php");
}
elseif (mysqli_affected_rows($result) === 2) {
$_SESSION['RESULT'] = "Listings for '$username' have been updated";
header ("location: edit-listings-success.php");
}
?>
The data is successfully inserting into the local_listings table but mysqli_affected_rows always returns a value of 0. It should be returning a value of 1 if it inserts a new row or 2 if it simply updates. Any ideas why this is happening?
