ON DUPLICATE KEY UPDATE Conditions
 

I've this

Which is updating the existing row where the supplied gamecode value (foreign key) matches, though it also allows the row to be cleared and overwritten by submitting the same gamecode value again.

I'm trying to achieve the same thing as above, except only when (gamename)="".
I've tried a few things such as CASE WHEN & THEN VALUES, but must be doing it wrong.
Goal is to allow the existing row where the FK (gamecode) matches to be updated but only if the gamename column in that row is blank.

Shed of light appreciated :)

thats only looking to update a record, if it were blank then the row would be wmpty and you would need to INSERT it

or maybe try another blank key:

Shouldnt this be in the MYSQL section?

Yes it should be. Moving to mysql.
I'm not sure if you can use a case within an on update clause, but you can use an if:
Old pedant may have some better ideas for this as well.

Thanks for the suggestions, I will try these tips out when I am home this evening.

Personally, for something like this I would opt to make a SELECT query first, to check the existing conditions, and then do either an INSERT or UPDATE (or nothing at all!) as appropriate. Surely this doesn't happen often enough that doing two queries instead of one will make any real difference on the site's performance?

But I don't see why Fou-Lu's trick won't work.

So, I tried the following

And it gives me

Same deal with Arcticwarrio's suggestion. I tried changing a few things, but unable to overcome the syntax error.

@Old_Pendant you're correct in that it may not happen very often, though I'd just prefer previous entries to be safe, as I cannot control whether or not a visitor checks to see if a game has been added before adding it again and overwriting the previous entry with possibly less compatibility information.

How can I incorporate conditions using a SELECT query before an INSERT and ON DUPLICATE KEY UPDATE? I've yet to try anything like this. My experience with SQL is limited.

Missing the apostrophes around the PHP variables. Why did you think you could omit them in the UPDATE section if you needed them in the INSERT section???

But I'd do this:

That was indeed the problem.

Seems to be working as needed this way :thumbsup:
Very helpful.. silly to not include the apostrophes, my mistake. But at least it all worked out and I learned something new!
Using gamename column for every IF did allow it to UPDATE, but only the game name column would be updated. Rest of the submission form inputs are ignored, and the rest of the columns would be left blank.

Still say a SELECT followed by either INSERT, UPDATE, or nothing would be better.

If you did that all in a Stored Procedure, it would still only involve one call from PHP.

And why did you reject my idea about adding the mysql_real_escape_string??

I'm not sure how to go about using SELECT to get the same results as IF gets me. The idea is to always UPDATE the existing row, but only if there is nothing in the row's gamename column.

I'm already using
I see that your method is cleaner and a lot less code, as I use the above for each input name, but
gives me a syntax error on }

The semicolon on the end of the line is missing.
I don't use PHP so I tend to make typos like that.

Whoa whoa, you're mixing mysql and mysqli libraries here.
Since you are using mysqli, you can just bind the variables and be done with it. No need to escape them at all. Old Pedant's function is good and clean for mysql library, but it won't be as clean with the mysqli since the mysqli resource is (rightfully at that) not global, so it would need either globalization or passing to the function (preferable). So the end result is simply a function call that will be near identical to the use of the original code. Instead of
$gamename = mysqli_real_escape_string($link, $_POST['gamename']);, you'd have $gamename = clean($link, $_POST['gamename']);, which if the only purpose is to execute mysqli_real_escape_string, I'd beg question on the overall usefulness of breaking it down into a separate function.

Statement that up instead. I've never done a statement with an on duplicate update, but I can't see why it would be any different. I'll just pull the VALUES instead:
Like that. Untested, and I wrote it in procedural to match what you have, so I can't be 100% sure that I got it right :P

I noticed as well that you have 2x different queries. I wrote this one with the assumption that it can go ahead and update so long as the gamename is empty, but you have a different one here that checks for each field as empty. Use whichever is correct.

This is excellent.. as I have been wanting to get into prepared statements. As I understand it, the use of mysqli_real_escape_string is not needed with this method?

Though, currently each input requires an }else{ and also I use regex to change some of the inputs. To me, it seems easier to handle each one independently and just use the mysqli_real_escape_string.

Here is an example of how I use some of the input fields.
I run gamecode input thru regex to guarantee a specific character format, and I use the output variable for the INSERT VALUES ('$cleangc'). Can I simply apply this variable to your method? Example..

Or would I also need to include it as
And a question, what is 'ssssssssss' doing?

I have to say, this strikes me as indicative of bad DB design:
Why does the name of the table need to be a variable? Surely you don't have more than one table with the same fields??? Or did we discuss this before?