我有一个帖子表,每个帖子都有一个IP地址,我想通过计算该IP在数据库中出现的次数,然后将其出现在屏幕上的次数来查看该IP发布了多少次。基本上是这样的:
MySQL表:
id entry ip
1 abc 19.123.14.5
2 cde 19.123.14.5
3 efg 12.231.22.9我希望代码接受它,计算重复项,并像这样输出计数:
id entry ip count
1 abc 19.123.14.5 2
2 efg 12.231.22.9 1到目前为止,以下是我的代码(不起作用):
$result = mysql_query("SELECT ip, entry, id, COUNT(ip) AS A FROM table_name AS C GROUP BY ip HAVING COUNT A > 1 ORDER BY id DESC");
$i = 0;
while($row = mysql_fetch_array($result)) {
$id = $row['id'];
$entry = $row['entry'];
$ip = $row['ip'];
$count = ?????;
$i++;
?>
<tr width="100%" align="center">
<td><?php echo $i; ?></td>
<td><?php echo $id; ?></td>
<td><?php echo $entry; ?></td>
<td><?php echo $ip; ?></td>
<td><?php echo $count ?></td>
<td>
<form style="display:inline;" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input type="hidden" value="<?php echo $ip; ?>" name="ip" />
<input type="hidden" value="<?php echo $id; ?>" name="id" />
<input type="submit" value="Ban IP" name="submit" />
</form>
</td>
</tr>
<?php
}任何帮助都将不胜感激!
编辑:不起作用:首先,从我的代码中可以明显看出,变量$count没有赋值给它,因为我不知道要放什么。其次,我得到了这个错误:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource发布于 2011-10-26 03:18:35
SELECT ip, min(entry), max(id) as id, COUNT(ip) AS A
FROM table_name AS C
GROUP BY ip
ORDER BY max(id) DESC由于您希望按IP和条目分组,因此缺少执行MAX(id)。您可以删除Having>1,因为您希望根据示例输出看到counts =1
示例脚本:
declare @table table
(
id int,
entry varchar(20),
ip varchar(20)
)
insert into @table
values
(1, 'abc', '19.123.14.5'),
(2, 'cde' , '19.123.14.5'),
(3 , 'efg', '12.231.22.9')
SELECT max(id) as id, ip, max(entry), COUNT(ip) AS count
FROM @table AS C
GROUP BY ip
ORDER BY max(id) asc产生:
id ip count
----------- -------------------- -------------------- -----------
2 19.123.14.5 cde 2
3 12.231.22.9 efg 1发布于 2011-10-26 03:36:41
您对ip=19.123.14.5具有不同的id和entries。你想选哪一个?
只计算ip,而完全忽略id和entry:
SELECT ip, COUNT(*)
FROM table_name
GROUP BY ip
12.231.22.9, 1
19.123.14.5, 2用于从可能连接到同一ip的许多行中选择随机行(这是特定于MySQL的,在其他数据库中不起作用):
SELECT id, entry, ip, COUNT(*)
FROM table_name
GROUP BY ip
3, efg, 12.231.22.9, 1
1, abc, 19.123.14.5, 2 // Might also be: 2, cde, 19.123.14.5, 2对于选择所有行,以及ip计数:
SELECT *, (SELECT COUNT(*) FROM table_name t2 WHERE t1.ip = t2.ip)
FROM
table_name t1
1, abc, 19.123.14.5, 2
2, cde, 19.123.14.5, 2
3, efg, 12.231.22.9, 1发布于 2011-10-26 03:24:15
SELECT t.id, t.entry, t.ip, q.IpCount
FROM (SELECT ip, MIN(id) AS MinId, COUNT(*) AS IpCount
FROM table_name
GROUP BY ip) q
INNER JOIN table_name t
ON q.ip = t.ip
AND q.MinId = t.id
ORDER BY t.id DESC;https://stackoverflow.com/questions/7894573
复制相似问题