Don't add a row with data that already exists ?

Pellefant

Hello! 🙂

I do have this code, which works fine:

$Query = "INSERT INTO addressbook(memberid, orgid) values('$memberid', '$orgid')" or die (mysql_error()); 
mysql_db_query ("abcdef", $Query, $Link) or die (mysql_error());

But....how do I do, so if there already is a row with the memberid and orgid data existing, I do instead of adding it again to the table, I need to print "Organisation is already in address book"

matto

well, check the database in the first place ...

query:
select count(*) from addressbook where memberid='$memberid' and orgid='$orgid'

if this does not return 0, echo "already in the address book"
else do your insert

Sxooter

Two ways. One is the way listed, which is fine, unless someone else inserts it right after your check and right before your insert. This is called a race condition.

What you have is a data constraint. Data constraints are "choke points" for a database. That means they're the part most likely to cause a traffic jam effect. The further you move the constraint from the database into first server side code, then client side code, the slower the constraint checking will generally be, and the more error prone as well.

So, the best way to handle this is to set a unique constraint on that field combination.

create unique index table_f1f2_undx on table (field1, field2);

Now, you just insert and check for an error. If you get an error saying duplicates not allowed or some such, then you return the "nice and pretty" one to the user. You can hide SQL errors in your query using the @ operator:

$res = mysql_query($conn,"$query");
if error -> tell user
else -> must have worked!

The advantage to this method is that it is paranoid of bad data. I.e. no mistake you or the user makes can duplicate those two fields, because the database won't let you.

Pellefant

Hello! 🙂

Thanks for the response. I am trying with this, but it never says that the row already exists. 🙁

Very thankful for more help.

$Query2 = "select count(*) from addressbook where memberid='$memberid' and orgid='$orgid'"; 
$Result2 = mysql_db_query ("abcdef", $Query2, $Link) or die (mysql_error());

if(mysql_num_rows($Result2) == 0){ 

echo "Organisation is already in address book";

}else{ etcetera....

tsinka

Hi,

there's a little error 🙂

SELECT COUNT(*) ...

always returns a row. You have to read out the value an to check if is 0 or >0.

stolzyboy

if you are using mysql_num_rows, you can simply do

$Query2 = "select anyfieldname from addressbook where memberid='$memberid' and orgid='$orgid'";
$Result2 = mysql_db_query ("abcdef", $Query2, $Link) or die (mysql_error());

then do your mysql_num_rows

tsinka

Hi,

this will work also but it would be very time expensive if there are many records in the table (e.g. million of records).

stolzyboy

any sql statement regardless what you are doing, with a millions records is going to take time, and as long as you say select the id field or something like that (which should be indexed), it will go much quicker than expected

tsinka

Hi,

you're right, sorry for that one. But wouldn't be a count(id) be faster then a select name from. With count a database only counts the number of records while selecting even one field will cause the database to read out the value and to create a record set that can then be used ?

I don't want to annoy you, just a thought 🙂

Pellefant

Originally posted by tsinka
Hi,

there's a little error 🙂

SELECT COUNT(*) ...

always returns a row. You have to read out the value an to check if is 0 or >0.

How do I read out the value please?

stolzyboy

even when counting, it still has to count the records that are
selected, so either way you are getting the same results
and number of records returned, and like i said, if you
select just the id field it won't make a difference whether
you count or not, and also, there would be no reason to
use count since you are gonna use mysql_num_rows to
count the array anyway, just thought i'd let you know,
and no you are not bothering/annoying me, i find it
fun most of the time helping people and trying to help
them understand things they are confused on

stolzyboy

did you try this

$Query2 = "select id from addressbook where memberid='$memberid' and orgid='$orgid'";
$Result2 = mysql_db_query ("abcdef", $Query2, $Link) or die (mysql_error());

tsinka

Sorry again, seems like I'm getting blind. What I posted is only true if you have no WHERE clause which isn't the case here.

I just oversaw it.

Shame on me 🙂

Sxooter

A couple of quick points.

A select count() without a where clause is never cheap. Even if you're using an index, on a large dataset, the index may be a considerable portion of the size of the table, and the database has to scan the entire index to get a correct count. It's still more expensive than select id from table where id=1 if id is indexed, because the database never has to actually look in the table, just the index, where count() may access the table. Note that count(id) probably wouldn't hit the table, as it's data is also held in the index.

Two, you guys are still doing it the wrong way. Sorry, but with a race condition like this, you WILL eventually wind up with duplicate data.

Client1: select id from table where id=1; = FALSE
Client2: select id from table where id=1; = FALSE
Client2: Insert into table (id,data) values (1,'looks safe');
Client1: Insert into table (id date) values (1,'looks safe here too');

Trust me, I'm an old timer. just do it the right way, use a unique constrained index, then insert without care, check to see if it went, and if it didn't then retry the whole thing. You can do it silently while the user waits, and you won't get incoherent data into your database.

stolzyboy

yes sxooter, you are very right, but we are talking very, very, very little amount of time between the

sql select check

and the

insert into

so duplications won't likely happen that often, and i am
not necessarily telling him to use id as his checker, that
is just a fieldname i thru in there, i had anyfieldname in
there at first, but thought that would just confuse him,
i have used this method for many sites with flawless
results, this with a 100,000+user site, where there are
many checks similar to this, not just for inserting member
ids, but similar tasks, i'm not trying to get into an argument,
i'm just telling you the facts of my experiences

also didn't think it would be very easy to talk someone thru
your suggestion, much easier to let them know this way out,
and if they need a better solution so be it

Sxooter

True. And, if you add a unique index (easy easy step) then you still have the security of my method, but easier to program. And since the chance of an error happening is pretty low in most systems, when it does happen, the user can just see a screen with a MySQL error on it like once every couple years. Then just refresh or whatnot and it'll work.

The important step is that unique index.

stolzyboy

yep, sorry to try and rain on your parade, i wasn't really looking
for a debate, i know how these threads can move along when
two people disagree and was hoping not to go down that path

Sxooter

Oh no, I'm not angry, or any of that. Just pointing out that either method is safe as long as a unique index constraint is in place.

Pellefant

Thanks for all help.