2

I have one task. I have to insert couple of columns to a mysql table. 

insert into `alreadysent` VALUES (NULL,'".$mobile_number."','".$user."','".$sender."','".date('Y-m-d H:i:s')."');

if same mobile_number is inserted for 10 times, it should accept. But it should not accept after 10th insertion for that mobile_number.

I would have done it easily using select statement and insert if count less than 10. But i cannot do in this senario. SO checking for some other optimal option. We can stop insertion on duplicate entry if we declare column as unique, similarly is there anything which can give exception after n number of inserts? Thank You!

vikram
  • 77
  • 8
  • 7
    That is Business logic and should not be in SQL query. put it into your php code – Jens Aug 30 '17 at 12:55
  • 1
    Learn about prepared Statements to prevent SQL injection – Jens Aug 30 '17 at 12:56
  • 1
    Why is it that you cannot SELECT and count before you insert? – BeetleJuice Aug 30 '17 at 12:56
  • 1
    "But i cannot do in this senario." Why not? Wrap it all in a transaction and check the count before each insert, just like you said. What's wrong with that? There's not really any other way to do it. Maybe a trigger would work, that's the only other thing I can think of. – ADyson Aug 30 '17 at 13:01
  • P.S. Your code is vulnerable to SQL injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com/ gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. – ADyson Aug 30 '17 at 13:01
  • I might think about a couple of ways to implement such a functionality, but if you want to keep it in the database you can try doing it with a `TRIGGER` - check [**this post**](https://stackoverflow.com/a/8559284/5452965), but try using `BEFORE INSERT` – codtex Aug 30 '17 at 13:02
  • @ADyson, Thanks for comment, i just gave a sample insert query to make users understand my need. I cant use select to reduce the load(as of my boss :-) ) – vikram Aug 30 '17 at 13:05
  • 1
    well you'll have to use one somewhere, whether in PHP or inside a trigger, either way you need a SELECT. How else do you determine the current contents of the table? If your boss insists that you can't use a SELECT, ask them to research the appropriate solution, since they want to impose an apparently arbitrary and unworkable restriction. If they're really that worried about load, a select on a single table isn't that much of a problem, assuming it's indexed properly. There are almost certainly more productive ways to optimise the database and application. – ADyson Aug 30 '17 at 13:06
  • 1
    You can blindly INSERT but you cannot use a SELECT because of load? Really? – jeroen Aug 30 '17 at 13:08
  • @ADyson, true! I have got some answers below, will try them. I also have to see the table optimization since it should hold crores of data. – vikram Aug 31 '17 at 05:43
  • @jeroen, Yes, but not blind insertion. – vikram Aug 31 '17 at 05:44

2 Answers2

1

You can try with Conditional Insert like this : 

INSERT INTO TABLENAME(MOBILENUMBER, USER, SENDER, DATE) 
SELECT 'XXXX', 'XXXX', 'XXXX', 'XXXX'
    FROM DUAL
    WHERE  (SELECT COUNT(*) FROM TABLENAME
                         WHERE MOBILENUMBER = 'XXXX') < 10
Mr. Go
  • 567
  • 1
  • 5
  • 20
1

You can do something like this:

DECLARE @cnt INT

SELECT @cnt = COUNT(*) FROM table_name where mobile_number = '".$mobile_number."'

IF @cnt < 10

INSERT INTO table_name VALUES(col1, col2, col3 .....)
Amrinder Singh
  • 5,300
  • 12
  • 46
  • 88