1

I have a table with ips and a count of the amount of times the ip accessed a certain page. I want to add one to the count if a row exists with the ip, otherwise create a row with the ip and set the count to 1.

I tried

INSERT INTO mytable (ip,count) VALUES ('" + ip + "',1) ON DUPLICATE KEY UPDATE count=count+1;

But ip isn't unique or primary, so it just keeps creating rows with the same ip. I tried making ip unique, but it won't let me. When I try to create a text column and make it unique, phpmyadmin says BLOB/TEXT column 'test' used in key specification without a key length.

I'm alright with using two statements. How can I accomplish this?

mowwwalker
  • 16,634
  • 25
  • 104
  • 157
  • If you want to use `UNIQUE` you'll have to normalize the table first with all the duplicates rows before you can run your `ALTER` to make the column `UNIQUE`. Probably easiest to create a new table, `SELECT INTO` it with the aggregated results (e.g. `SUM` etc...), and then `DROP` the old table and rename the tmp table to the old table name. – Yzmir Ramirez Apr 20 '12 at 21:01

2 Answers2

6

Change ip to UNSIGNED INT, create a UNIQUE constraint on it and use this:

INSERT
INTO    mytable (ip, count)
VALUES  (INET_ATON($ip), 1)
ON DUPLICATE KEY
UPDATE
        count = count + 1

To retrieve the ip in dot-decimal notation (like 192.168.1.1), use:

SELECT  INET_NTOA(ip)
FROM    mytable

You may also create a prefixed index:

CREATE UNIQUE INDEX ux_mytaable_ip ON mytable (ip(15))

but using INT column to store an ipv4 address is preferred.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 2
    @Walkerneo: IPv4 address is in fact a 32-bit number. You may use `INET_NTOA` and `INET_ATON` to convert to and from dot-decimal notation. – Quassnoi Apr 20 '12 at 20:59
  • @Walkerneo: yes. Try running `SELECT INET_ATON('123.156.78.8')` – Quassnoi Apr 20 '12 at 21:02
0

You need to start out with a select. (A pseudo-codish answer - you'll need to adjust for your specific dialect.)

So do a 

select count(*) from mytable
where ip = $ip;

If the returned count is greater than 0 do

update mytable
set count = count + 1
where ip = $ip

else

insert into mytable($ip, count, <other fields>...) values (ip, 1, <other values>...);
gnuchu
  • 1,496
  • 13
  • 21