ON DUPLICATE KEY UPDATE Conditions

[bemore]

Originally Posted by Old Pedant

I have to say, this strikes me as indicative of bad DB design:

    $tbl_name = 'yourtable'; 
    $sql="INSERT INTO $tbl_name (gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp) 
        VALUES  ...

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?

We've discussed this before
Though, I do have multiple tables now. I only ever UPDATE or INSERT into the one table, though, and I still have it set as a variable simply because that's what I did in my very first PHP/MySQL script so I just continue to do it that way. It could easily be INSERT INTO opl_comp, but why should it be? Honestly, I don't see any reason to assume bad DB design based off of calling a table with a variable, or not. I see no difference in the method or result no matter how it is done, and it'd all be personal opinion in the end.

[Fou-Lu]

I'm actually with you on this one; the $tbl_name doesn't necessarily indicate a structural fault. Especially if you want to write a system where a prefix may be added, you may choose to do something of the sorts (or assemble with {$prefix}actual_table_name for example). What IS a problem though is this: $mode = implode(",", $_POST['mode']); which is definitely a structural problem as you are putting a collection into a single field.
'sssssssss' represents the datatype of each input variable. These are all strings. Other options are 'i' for integers, 'd' for doubles, and 'b' for binary.
You can't use each of these directly. The problem is that mysqli_real_escape_string isn't sensitive to magic_quotes_gpc, and that mysqli_stmt is not sensitive to mysqli_real_escape_string. So lets take my data as \\mymachine\Fou-Lu's share\.
If magic_quotes_gpc is enabled, that automatically becomes \\\\mymachine\\Fou-Lu\'s share\\. With mysqli_real_escape_string, that now becomes \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. Insert this using mysqli_query and the stored result is \\\\mymachine\\Fou-Lu\'s share\\. If you insert it using prepared statements, you get \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. So all of these are corrupting your data.
With a regular query you need to escape it. But only once. With a prepared statement you do not escape it as it becomes a part of the input. The data and structure are different pieces of the puzzle, so you cannot corrupt the structure with providing it data such as I want.
So this is why you must:
1. Check for magic_quotes_gpc. If enabled, issue stripslashes to input (\\\\mymachine\\Fou-Lu\'s share\\ now becomes \\mymachine\Fou-Lu's share\);
2. If you are using prepared statement, no other steps are necessary (data is still: \\mymachine\Fou-Lu's share\).
3. If you are not using prepared statements, issue mysqli_real_escape_string to escape it (ie: data is now: \\\\mymachine\\Fou-Lu\'s share\\)

Does that make more sense?

[Old Pedant]

the $tbl_name doesn't necessarily indicate a structural fault. Especially if you want to write a system where a prefix may be added, you may choose to do something of the sorts (or assemble with {$prefix}actual_table_name for example).

But that should only apply if you are creating something meant to be installed on many different machines. For a "one off" there's no reason to have an adjustable prefix or any other reason to have multiple table names.

[bemore]

Originally Posted by Old Pedant

But that should only apply if you are creating something meant to be installed on many different machines. For a "one off" there's no reason to have an adjustable prefix or any other reason to have multiple table names.

For the persistence surrounding it, I have changed every instance of $tbl_name to opl_comp and removed the $tbl_name = opl_comp variable

Originally Posted by Fou-Lu

I'm actually with you on this one; the $tbl_name doesn't necessarily indicate a structural fault. Especially if you want to write a system where a prefix may be added, you may choose to do something of the sorts (or assemble with {$prefix}actual_table_name for example). What IS a problem though is this: $mode = implode(",", $_POST['mode']); which is definitely a structural problem as you are putting a collection into a single field.
'sssssssss' represents the datatype of each input variable. These are all strings. Other options are 'i' for integers, 'd' for doubles, and 'b' for binary.
You can't use each of these directly. The problem is that mysqli_real_escape_string isn't sensitive to magic_quotes_gpc, and that mysqli_stmt is not sensitive to mysqli_real_escape_string. So lets take my data as \\mymachine\Fou-Lu's share\.
If magic_quotes_gpc is enabled, that automatically becomes \\\\mymachine\\Fou-Lu\'s share\\. With mysqli_real_escape_string, that now becomes \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. Insert this using mysqli_query and the stored result is \\\\mymachine\\Fou-Lu\'s share\\. If you insert it using prepared statements, you get \\\\\\\\mymachine\\\\Fou-Lu\\\'s share\\\\. So all of these are corrupting your data.
With a regular query you need to escape it. But only once. With a prepared statement you do not escape it as it becomes a part of the input. The data and structure are different pieces of the puzzle, so you cannot corrupt the structure with providing it data such as I want.
So this is why you must:
1. Check for magic_quotes_gpc. If enabled, issue stripslashes to input (\\\\mymachine\\Fou-Lu\'s share\\ now becomes \\mymachine\Fou-Lu's share\);
2. If you are using prepared statement, no other steps are necessary (data is still: \\mymachine\Fou-Lu's share\).
3. If you are not using prepared statements, issue mysqli_real_escape_string to escape it (ie: data is now: \\\\mymachine\\Fou-Lu\'s share\\)

Does that make more sense?

Yes it is making sense thank you for the detailed explanation. I'm now working on adjusting over to using prepared statements.

[Old Pedant]

I thought we had discussed the table name stuff before, but wasn't sure.

I have no real object to using a variable for the table name under the circumstances.

It's just that about 90% of the time when I see that usage it means the programmer has set up multiple tables with the same structure in the mistaken belief the MySQL can 't handle more than a few dozen records per table or some other such nonsense.