Jump to content


Photo

Selecting mutiple rows with same values


  • Please log in to reply
3 replies to this topic

#1 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 06 June 2006 - 08:58 AM

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
+-----------+--------------+------+-----+-------------------+----------------+
| 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 |                   |                |
+-----------+--------------+------+-----+-------------------+----------------+
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
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>

#2 nickk

nickk
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 06 June 2006 - 10:25 AM

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.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 June 2006 - 05:22 PM

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

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

You can easily switch this to a DELETE statement.

BTW, you should be storing your IPs as UNSIGNED INTs and using INET_ATOI().
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 grandadevans

grandadevans
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 07 June 2006 - 06:28 AM

Cheers mate,
I've also implemented the suggestion about storing IP addresses.
John
Please sign our petition and support our troops<br />
<a href="http://www.postedove...ition.php"><img src="http://www.postedove...o_campaign.jpg" /></a>




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users