Jump to content

Selecting mutiple rows with same values


grandadevans

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. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.