Only 10 inserts per day in table per IP address

Question

I will have the following table (say a user table) that will contain this data:

id -- (auto increment)

username -- (unique)

password -- (not relevant to question)

IP address -- (only allowed 10 user creations per day)

request_number -- (number of times a single ip address has inserted into the database)

Is there any way to constrain a database to raise an error upon insert if those factors are not met?

I will be coding in PHP and I would HIGHLY prefer that I did not have to perform two queries, or test events in PHP to meet my goals.

I would like to find a way to meet these goals purely in database schema. Is there a way?

(Please no, "why don't you check with php?" That would be slower than a single transaction and I would like to find a way to do it with mysql table schema and raise an exception if my conditions are not met)

Thanks in advance everyone!

It would be marginally slower. Create a stored procedure and upon the mentioned condition use `SIGNAL`. PHP will interpret that as an Exception. — N.B., Oct 15 '14 at 15:09
That may be the answer that I have been looking for. I was under the impression that PHP could not interpret exceptions raised by stored procedures. Can you go into detail in an answer? — steve, Oct 15 '14 at 15:10
Using PDO, and setting it into Exception mode allows you to catch any database errors as an Exception. The subject is covered on www.php.net/PDO pretty well and popular PHP ORMs (Doctrine, Eloquent, Propel) handle that in a good way. If you have problems finding info on how to do it exactly, line by line of code - I can provide you with complete example, but I believe that nudging people in the right way usually helps more than handing out finished code. — N.B., Oct 15 '14 at 15:13
I would agree.. and my code is already doing that! :) I meant more raising a signal with a stored procedure.. and making it an answer that I can accept. And I wish I could upvote it 10000 times... — steve, Oct 15 '14 at 15:14
I can't see why id (or possibly username) and/or password would be columns in this table!?!? (or vice versa) — Strawberry, Oct 15 '14 at 15:17
@N.B. You should post something that I can upvote an accept, regardless of if you want to show working code. I will figure that out — steve, Oct 15 '14 at 15:46
I'd recommend constraint triggers. See: http://stackoverflow.com/questions/14247655/mysql-check-constraint — Jaaz Cole, Oct 15 '14 at 15:48
Both signals and triggers are acceptable. Triggers are marginally faster but in this case will create a race condition, signals and a stored procedure will be a bit slower, but the race condition will not exist. The question then becomes what drawbacks can you live with for the situation :) Thanks everyone! — steve, Oct 15 '14 at 17:40

score 0 · Accepted Answer · answered Oct 15 '14 at 15:53

As per comments, and I'm not too keen whether this is of sufficient quality for an answer - using SIGNAL will be interpreted as an Exception in PHP (PDO > Exception mode). Using triggers (to check the condition), you can SIGNAL and stop the execution (if 10 inserts occurred already). PHP (PDO) will correctly interpret it as an Exception and you can handle error display from there.

Only 10 inserts per day in table per IP address

1 Answers1