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.
Old pedant may have some better ideas for this as well.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
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.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Hack - Part 1 - Infection, gamename), region = IF (gamename = '', U, region), mo' at line 3
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:
Code:
// ensure no sql injection and also put the apostrophes around each value
// so that you only have to do this once!
function clean( $val )
{
return "'" . mysql_real_escape_string( $val ) . "'"
}
// do it for each value:
$gamename = clean($gamename);
$cleangc = clean($cleangc);
$region = clean($region);
$mode = clean($mode);
$vmc = clean($vmc);
$smb = clean($smb);
$hdd = clean($hdd);
$usb = clean($usb);
$notes = clean($notes);
$comp = clean($comp);
$sql = "INSERT INTO $tbl_name(gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp)
VALUES($gamename,$cleangc,$region,$mode,$vmc,$smb,$hdd,$usb,$notes,$comp) "
ON DUPLICATE KEY UPDATE
gamename = IF(gamename = '', $gamename, gamename),
region = IF (gamename = '', $region, region),
mode = IF(gamename = '', $mode, mode),
vmc = IF(gamename = '', $vmc, vmc),
smb = IF(gamename = '', $smb, smb),
hdd = IF(gamename = '', $hdd, hdd),
usb = IF(gamename = '', $usb, usb),
notes = IF(gamename = '', $notes, notes),
comp = IF(gamename = '', $comp, comp)";
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Seems to be working as needed this way
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.
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:
PHP Code:
<?php // pull your connection in here // Gather ye' variables with a simple pull. Walk the $_POST through a stripslashes if get_magic_quotes_gpc() is enabled: if (isset($_POST['gamename'], $_POST['gamecode'], /*...etc */)) { $gamename = $_POST['gamename']; $gamecode = $_POST['gamecode']; // etc $tbl_name = 'yourtable'; $sql="INSERT INTO $tbl_name (gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE gamename = IF(gamename = '', VALUES(gamename), gamename), region = IF (gamename = '', VALUES(region), region), mode = IF(gamename = '', VALUES(mode), mode), vmc = IF(gamename = '', VALUES(vmc), vmc), smb = IF(gamename = '', VALUES(smb), smb), hdd = IF(gamename = '', VALUES(hdd), hdd), usb = IF(gamename = '', VALUES(usb), usb), notes = IF(gamename = '', VALUES(notes), notes), comp = IF(gamename = '', VALUES(comp), comp)"; if ($stmt = mysqli_stmt_prepare($link, $sql)) { mysqli_stmt_bind_param($stmt, 'ssssssssss', $gamename, $gamecode, $region, $mode, $vmc, $smb, $hdd, $usb, $notes, $comp); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); } else { printf("Failed to create a statement: %s" . PHP_EOL, mysqli_error($link)); } }
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.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
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:
PHP Code:
<?php
// pull your connection in here
// Gather ye' variables with a simple pull. Walk the $_POST through a stripslashes if get_magic_quotes_gpc() is enabled:
if (isset($_POST['gamename'], $_POST['gamecode'], /*...etc */))
{
$gamename = $_POST['gamename'];
$gamecode = $_POST['gamecode'];
// etc
$tbl_name = 'yourtable';
$sql="INSERT INTO $tbl_name (gamename, gamecode, region, mode, vmc, smb, hdd, usb, notes, comp)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
gamename = IF(gamename = '', VALUES(gamename), gamename),
region = IF (gamename = '', VALUES(region), region),
mode = IF(gamename = '', VALUES(mode), mode),
vmc = IF(gamename = '', VALUES(vmc), vmc),
smb = IF(gamename = '', VALUES(smb), smb),
hdd = IF(gamename = '', VALUES(hdd), hdd),
usb = IF(gamename = '', VALUES(usb), usb),
notes = IF(gamename = '', VALUES(notes), notes),
comp = IF(gamename = '', VALUES(comp), comp)";
if ($stmt = mysqli_stmt_prepare($link, $sql))
{
mysqli_stmt_bind_param($stmt, 'ssssssssss', $gamename, $gamecode, $region, $mode, $vmc, $smb, $hdd, $usb, $notes, $comp);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
else
{
printf("Failed to create a statement: %s" . PHP_EOL, mysqli_error($link));
}
}
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..
Before you post, read our: 
