Jump to content

Selecting mutiple rows with same values


grandadevans
 Share

Recommended Posts

Hello everybody yet again,
I have a table which logs
A) The users IP address
B) The company ID of the company clicked

The table looks like this
[code]
+-----------+--------------+------+-----+-------------------+----------------+
| Field     | Type         | Null | Key | Default           | Extra          |
+-----------+--------------+------+-----+-------------------+----------------+
| clickID   | int(11)      | NO   | PRI | NULL              | auto_increment |
| date      | timestamp    | YES  | MUL | CURRENT_TIMESTAMP |                |
| companyID | int(11)      | YES  | MUL | NULL              |                |
| URL       | varchar(255) | YES  |     | NULL              |                |
| referer   | varchar(50)  | YES  | MUL | NULL              |                |
| IPAddress | varchar(100) | YES  |     | NULL              |                |
| agent     | varchar(255) | NO   | MUL |                   |                |
+-----------+--------------+------+-----+-------------------+----------------+[/code]
How do I select instances of the same IP address with the same compay ID
for example, what I want to do is
DELETE rows where the same IP address and companyID combination appear more than 4 times.

Can anybody help me out?
Thanks
John
Link to comment
Share on other sites

Hey I'm in a hurry so I have to be quick. Basically, to delete rows where the same IP address and companyID combination appear more than 4 times, you could select the first row, then loop through all the other rows, comparing the first row to the ones that are looping. Using a counter, you could count the number of times the first row is equal to the others (in terms of IP address and companyID), if the counter is 4=< you delete it. Then you move onto the second row and do the same thing. This is a very rough idea and you could make it much more effecient, maybe there is also a simple mysql statement that could be of more use (I'm not that great at mysql to know it). Hope I helped.
Link to comment
Share on other sites

Once you confirm that the following query find all these rows:

[code]SELECT companyID, IPAddress, COUNT(*) AS cnt FROM yourTable GROUP BY companyID, IPAddress HAVING cnt >= 4[/code]

You can easily switch this to a DELETE statement.

BTW, you should be storing your IPs as UNSIGNED INTs and using INET_ATOI().
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.