Allowing N Duplicates

[jas511]

Hi, I am wondering if it is possible to write a query that inserts data as long as there are less than N duplicates.

For example, I have a game where I want a user to be able to submit no more than 10 entries. I don't want to check this constraint in PHP code because there is a possibility of problems when a user tries to insert from multiple machines. Basically, I'd like to write a query that says something like:

INSERT INTO mytable (field1,field) VALUES(1,2) WHERE there are less than 10 entries with field1=1 and field=2. Is this possible?

Thanks,
Jeff

 

[ingresman]

Maybe....
I treid a quick sub select style query i.e.

 select * from  mytable where (select count(*) from mytable) > 1;

which worked but

insert into mytable values (1,2) where (select count(*) from mytable) > 1

doesn't because insert doesn't support the where clause, so you might be reduced to writing a stored procedure in this case

 

[jas511]

Thanks for the reply, but won't a stored procedure still run into the same problem writing code to handle it would, for example, I could write this code:

if ( recordCountForUser(1) < 3 ) {
// If a user makes an insert from two computers when record cound = 2, they can both get to this code at once and the database will end up with 4 records, which is why I'd like to restrict this at the database level.
insertRecordForUser(1)
}

 

[ingresman]

Are you restricting by actual machine rather than a logged in user name ?. How do you detect the machine name ? by IP ?
I think your table structure will need to take this into account.
Or are you talking about two users hitting the table at the same time, in which case you need a locking strategy such as an optimisic lock (basicaly add a count columns to the tavle and increment every time the table is updated. When you select from the table also select the count columns and when you do an update make the where clause say ... and count = $countRead. The insert should fail if some else has updateed it)

 

[jas511]

I am trying to restrict by a logged in user name, but I still feel like the code I've shown above suffers from the issue of if a single user logs in from two machines and submits the info at the same time...

 

[ingresman]

In that case I would use an optimistic locking scheme.