php - Syntax for ON DUPLICATE KEY UPDATE with positional placeholders -

this replace syntax

$sql = "replace 2_1_journal (number,recordday, recordmonth) values "; $insertquery = array(); $insertdata = array(); foreach ($_post['row_id'] $i => $row_id) { $insertquery[] = '(?, ?, ?)'; $insertdata[] = $row_id; $insertdata[] = $_post['date_day'][$i]; $insertdata[] = $_post['date_month'][$i]; } if (!empty($insertquery)) { $sql .= implode(', ', $insertquery); $stmt = $db->prepare($sql); $stmt->execute($insertdata); } 

however read instead of replace better use on duplicate key update

trying change this

$sql = "insert 2_1_journal (number,recordday, recordmonth) values on duplicate key update (recordday, recordmonth) "; $insertquery = array(); $insertdata = array(); foreach ($_post['row_id'] $i => $row_id) { $insertquery[] = '(?, ?, ?, ?, ?)'; $insertdata[] = $row_id; $insertdata[] = $_post['date_day'][$i]; $insertdata[] = $_post['date_month'][$i]; $insertdata[] = $_post['date_day'][$i]; $insertdata[] = $_post['date_month'][$i]; } if (!empty($insertquery)) { $sql .= implode(', ', $insertquery); $stmt = $db->prepare($sql); $stmt->execute($insertdata); } 

but not work (neither inserts nor updates)

what incorrect?

if downvote, please write in comments why (for me avoid write things cause downvote)

update

changed code this

$sql = "insert 2_1_journal (number, recordday) values on duplicate key update recordday"; $insertquery = array(); $insertdata = array(); foreach ($_post['row_id'] $i => $row_id) { $insertquery[] = '(?, ?)'; $insertdata[] = $row_id; $insertdata[] = $_post['date_day'][$i]; } 

get sqlstate[42000]: syntax error or access violation: 1064 have error in sql syntax; check manual corresponds mysql server version right syntax use near 'on duplicate key update recordday('21', ''), ('22', '')' @ line 1

changed this

$sql = "insert 2_1_journal (number, recordday) values on duplicate key update recordday=values(number)"; $insertquery = array(); $insertdata = array(); foreach ($_post['row_id'] $i => $row_id) { $insertquery[] = '(?, ?)'; $insertdata[] = $row_id; $insertdata[] = $_post['date_day'][$i]; 

get sqlstate[42000]: syntax error or access violation: 1064 have error in sql syntax; check manual corresponds mysql server version right syntax use near 'on duplicate key update recordday=values(number)('21', ''), ('22', '')' @ line 1

changed code this

$sql = "insert 2_1_journal (number, recordday) values on duplicate key update recordmonth=?"; $insertquery = array(); $insertdata = array(); foreach ($_post['row_id'] $i => $row_id) { $insertquery[] = '(?, ?, ?)'; $insertdata[] = $row_id; $insertdata[] = $_post['date_day'][$i]; $insertdata[] = $_post['date_month'][$i]; 

get sqlstate[hy093]: invalid parameter number: number of bound variables not match number of tokens

can make conclusion last example valid sql syntax. why error? number, recordday, recordmonth=? , $insertquery[] = '(?, ?, ?)'; 3 variables , 3 tokens? or wrong?

if $insertquery[] = '(?, ?)'; same sqlstate[hy093]: invalid parameter number: number of bound variables not match number of tokens

please, help. stuck.... no idea

working code!!!

try { $sql = "insert 2_1_journal (number, recordday) values "; $insertquery = array(); $insertdata = array(); foreach ($_post['row_id'] $i => $row_id) { $insertquery[] = '(?, ?)'; $insertdata[] = $row_id; $insertdata[] = $_post['date_day'][$i]; } if (!empty($insertquery)) { $sql .= implode(', ', $insertquery) . 'on duplicate key update recordday = values(recordday);'; $stmt = $db->prepare($sql); $stmt->execute($insertdata); } } catch (pdoexception $e){ echo "database error: " .$e->getmessage() .'<br>'; } catch (exception $e) { echo "general error: ".$e->getmessage() .'<br>'; } 

your comments, please. , see location of on duplicate key update!!!

whenever use code build sql statement, add line:

echo $sql; 

just before executing statement. way can see executing.

you can cut & paste sql statement directly database, , see happens.

also -- prevent sql injection, add

$row_id = mysql_real_escape_string($post['row_id']); $date_day = mysql_real_escape_string($_post['date_day']); $date_month = mysql_real_escape_string($_post['date_month']); 

to beginning of code, , use $row_id instead of $post['row_id'] in rest of code. , $date_day instead of $_post['date_day'], , $date_month instead of $_post['date_month'], etc -- post , variables.

check this: http://xkcd.com/327/