How to tell if the insert was a pass or fail.

[tommytx]

$sql = "UPDATE test SET fname='$fname', lname='$lname', phone='$phone', fvisit='$fvisit' WHERE Link = '$Link'" or exit(mysql_error());

 

I am using the above to do some updates into a data base and it works fine, but i need some way to detect when the update fails so I can the do an insert.

 

Basically I have some updates and new entries in a text file that I am using php to attempt and update and if that fails it is assumed that the record does not exist so now I need to switch over and do an insert.

 

AT this point the updates are working fine, but if the record does not exiist it it not alerting me and just simply skips that record... I added the errror checker at the end as you see above but it is not showing any failure...

 

Anyone have an idea how as it parses the text file will let me know if it fails to update so that I can immediately using something like if fails then do an insert... or add record..

 

Shouldn't the above code be throwing an error for the ones that the $Link is not found so I can do insert on that record?

 

[sader]

if(mysql_affected_rows())

{

}

[tommytx]

Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier .

 

I am not too good at this but i did look up mysql affected rows() and it seems to give the number of affected rows, but not the identity of each updated row..

 

Could you expound a little so i might catch one...

 

Bottom line what I need to be able to  do is do update on lets say 20 rows, then do insert on any rows that failed the update. I had thought about just searching all the links to see which links are in the data base and which are not and the write the code to insert all the ones that were in the data base and insert all the ones that the link was missing from.  But surely its easier than that...

 

Sounds like the idea you offered would work if i could just understand it better.

 

 

 

[tommytx]

Typo:

write the code to (update) all the ones that were in the data base and (insert) all the ones that the link was missing from.  But surely its easier than that...

 

[gizmola]

There's a way to do this, but it's the opposite of how you're thinking about it.  What you instead try and do is an INSERT and use the ON DUPLICATE KEY UPDATE clause. 

 

However that depends on having a key violation occur on the insert, which requires that this link column be unique or the primary key, which might not be the case.

 

Truthfully, I think your best course in this case (as best I understand it with the minimal info you've provide) is to use the method you described, and get a list of link rows that actually exist, and build your updates off that, and then insert new rows that aren't in the list. 

 

Pseudocode:

 

Start with list of links in an array. Use this array to build the IN of your query.

 

Something like a:  SELECT DISTINCT link FROM TABLE where Link IN ('link1', ....) would give you a list of links that actually exist. 

 

Then PHP's http://www.php.net/manual/en/function.array-diff.php would let you combine the original list and the "actually exists" list to get the "INSERT" list.  Obviously your fist list that comes back from your query is the list of rows you UPDATE for.

 

 

[tommytx]

Thanks everyone for all the help, using your input I came up with this end result and i am posting it here as others may be able to use it.. It works great, either adding a new record if Link does not exist or editing the record if the record does exist.  Link is a long about 50 unique characters that identifies a unique record.

 

The only thing I am not sure about is why I had to send the Insert command twice... if you simply send the one long command, it will not add (insert) new records.. it will only update.  But that is ok as it only has to run against maybe 20 records each day so speed ins not a problem... and of course this is super fast anyway even with the duped command.

 

Anyone have any thoughts whey it requires the clean "Insert" to work...

But its not really important as it works like a big dog... thanks for the ideas..

 

 

$sql = "INSERT INTO `test` VALUES ('$fname', '$lname', '$phone', '$fvisit', '$Link')";

mysql_query($sql);

 

$sql = "INSERT INTO `test` VALUES ('$fname', '$lname', '$phone', '$fvisit', '$Link') ON DUPLICATE KEY UPDATE fname='$fname', lname='$lname', phone='$phone', fvisit='$fvisit'";

mysql_query($sql);

 

 

[tommytx]

Actually I suppose it is really adding the record (insert) if not already there and then immediatley doing an update... but what the hell it works.. just extra unnecessary commands but unless anyone has a better idea this is great...

It can't hurt to make a record then do an instant update... same data anyway..

Does this make sense....

 

[Maq]

It works great, either adding a new record if Link does not exist or editing the record if the record does exist.

 

If this is your intention, you can use REPLACE INTO.  If the entry exists, the row will be updated, if not, it will insert a new entry.