1,105,344 Community Members

Insert if doesn't exist else update

Member Avatar
mbarandao
Posting Whiz in Training
290 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

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

Member Avatar
mbarandao
Posting Whiz in Training
290 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
1
 

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?

Member Avatar
veedeoo
Veteran Poster
1,016 posts since Oct 2011
Reputation Points: 314 [?]
Q&As Helped to Solve: 166 [?]
Skill Endorsements: 17 [?]
 
0
 

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..

Member Avatar
mbarandao
Posting Whiz in Training
290 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
mbarandao
Posting Whiz in Training
290 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
mbarandao
Posting Whiz in Training
290 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Question Answered as of 2 Years Ago by diafol and veedeoo
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article