Insert if doesn't exist else update

[mbarandao]

Hello,

I have a strange occurence when trying to execute the following sql:

if($select_232 == 'Member Credit' && $paymenttype == 'Visa' || $paymenttype == 'Cash' || $paymenttype == 'Check' || $paymenttype == 'Master Card' || $paymenttype == 'Discover' || $paymenttype == 'Amex' || $paymenttype == 'Other')  
{

$result = mysql_query("UPDATE player_credit SET credit_amount='$new_creditbal', credit='yes', total_credit_value='$new_creditvalue', date_of_credit=CONCAT('$dateinserts', date_of_credit), credit_purpose='$credit_purpose2', typeofcredit='$paymenttype', details = CONCAT('testing', details) WHERE playerid='$playerid'") or die(mysql_error());         
    $message= 'Updated successfully!';


if (mysql_affected_rows() == 0) {
$result = mysql_query("INSERT player_credit (id, playerid, total_credit_value, credit_amount, date_of_credit, credit_purpose, details, typeofcredit, credit) VALUES('', '$playerid', '$new_creditvalue', '$paymentamount', '$theDate', '$credit_purpose2', '$paymentref_credit2', '$paymenttype',  'yes')") or die(mysql_error());  
    $message= 'Inserted successfully!';
}
}

The above code works fine if it is in a separate file but when incorporated inside a file with other sqls, only the update statment works. I have for the past few days trying to identify the problem with no success.

Any thoughts!
Mossa

[diafol]

try:

VALUES(NULL, ... 

instead of ''

I'd echo the SQL to the screen to see if it has the expected content. If so, copy it from the screen and paste it into a phpmysql window to see if an error is reported

[mbarandao]

Thanks ardav!

VALUES(NULL,...

change.

When the sql is echoed, nothing is displayed. It appears that the insert statement is skipped if the "if statement" that calls for it is true. Running the statement directly through phpmysql works fine with no errors.

[diafol]

How about using a REPLACE INTO statement? Or

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

The second may be better as you're referencing existing data for an update, which won't exist in the first example as the record is deleted then written (I think).

However the difference in both sql statements means that this may not be possible.

What about querying for an existing id (SELECT) if it exists - UPDATE, else INSERT. This however creates two queries.

I am a little confused as to how the player_id is used. So, perhaps the second example would work for you?

[veedeoo]

Hi,

Try, changing either $result to something else e.g. $result2 .. I think your scripts is just experiencing a minor naming collisions when integrated each other. Is this script will be running on production server? If yes, can you at least hash those CC numbers even at the minimum.. I don't know, but it is just so tempting for someone to make your script throw up all those numbers. Before considering any hashing and securing credit numbers for database storing, please read this white paper about it.

I am sorry for being such an A__ , I can't help it, whenever someone is posting script that has a lot of personal info. in it.. I just can't stop from crinching..

[mbarandao]

I have tried INSERT INTO .. ON DUPLICATE KEY; same outcome. I even tried (select) if EXIST and then update else insert. this too, did not work.
Playerid is the unique field.

[diafol]

I'm assuming that player_id is always passed by your script.

Did you try veedeoo's suggestion? If it is indeed due to collisions, changing the sql won't help.

[mbarandao]

veedeoo, thanks for chiming in. I have previously tried different names with the same thinking --concerning collision. this too did not work. I just gave it another try, still no luck. Thanks for your concern about the integrety of the data. Fortnuately the information in the code does not have any credit card information.

Any other thoughts as to what is happening?

[mbarandao]

issue resolved! problem was with an extra bracket with the code. found and re-positioned and now working as intended!

Thanks for the help!

Mossa