/    Sign up×
Community /Pin to ProfileBookmark

INSERT and/or UPDATE using ON DUPLICATE KEY UPDATE issue

Copy linkTweet thisAlerts:
Mar 18.2009

the code below saves but only the last field. When I update that field it updates just fine, but its confined to only that field to insert or update.

so, its working but for only one field and not both.

[code=php]$fieldstoinsert=array(‘purchaser’, ‘orderno’);

$query = “INSERT INTO formdata (userid, fieldid, value, saveas) VALUES”;
foreach($_POST as $fieldid => $value){
if(in_array($fieldid,$fieldstoinsert)){
if(trim($value)!=””){
$query.= “(‘$userid’, ‘$fieldid’, ‘$value’, ‘$saveas’),”;
}
}
}
$query=substr($query,0,-1);
//Primary key is userid
$query .= ” ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)”; [/code]

echo $query;

[code=php]INSERT INTO formdata (userid, fieldid, value, saveas) VALUES(’27’, ‘purchaser’, ‘me’, ‘test1′),(’27’, ‘orderno’, ‘1234’, ‘test1’) ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)[/code]

Structure from old post: [URL=”http://www.webdeveloper.com/forum/showthread.php?t=183148″]http://www.webdeveloper.com/forum/showthread.php?t=183148[/URL]

to post a comment
PHP

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@StevishMar 19.2009 — You're not using userid or saveas as any kind of key are you (primary, unique, etc)? If you are, then it is just updating the same row twice because the userid and saveas fields are the same in both instances, which, if either are keys, would cause that function to overwrite the first with the second.
Copy linkTweet thisAlerts:
@StevishMar 19.2009 — AHA! I just found a command that might help you:

[code=php]$query = "REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES";
foreach($_POST as $fieldid => $value){
if(in_array($fieldid,$fieldstoinsert)){
if(trim($value)!=""){
$query.= "('$userid', '$fieldid', '$value', '$saveas'),";
}
}
}

$query=substr($query,0,-1); [/code]

From what I'm reading about it at http://dev.mysql.com/doc/refman/5.1/en/replace.html, it seems like this will insert rows unless a primary or unique key matches with an existing row, in which case it will overwrite it. It seems like the syntax is exactly the same as an INSERT command.
Copy linkTweet thisAlerts:
@JodarecodeauthorMar 19.2009 — I tried that and did not work, so I tried again creating a new column called id as primary and auto increment, still did not work.

To recap:

[B]userid[/B] is always the same for the user whos logged in

[B]fieldid[/B] is all the fields in the array in $fieldstoinsert

[B]value[/B] is all the values that will be inserted and/or updated

[B]saveas[/B] is always the same for the saved form value sets and different for each form

So I had an idea and took out the id column so it was back the way it was then made fieldid primary, I figured since there was nothing with the same name in the column "fieldid", that it might work.

To my surprise it seems to work perfectly, it INSERTs and/or UPDATEs at the same time.

I'm not sure if that was sloppy coding as the rest but it seems to work with no errors.
×

Success!

Help @Jodarecode spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.25,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...