MySql query grouping in php script

Anon

Hi,

I have a mysql database storing snort/Demarc data. I am writing my own html front end to query the database, and all works quite well. I now need to refine it a bit and group all the attack signatures together from each unique source IP address. When it is displayed, I only want the source IP address displayed once and the other fields displayed below it. Here is my current script:

<?php

$db=mysql_connect("localhost", "database", "password");
mysql_select_db("Demarc",$db);

$query="select INET_NTOA(ip_src), INET_NTOA(ip_dst), sig_name, timestamp FROM ip
hdr, event, signature WHERE INET_NTOA(ip_dst) BETWEEN '111.111.111.111' AND
'111.111.222.222' && event.cid=iphdr.cid && event.signature=signature.sig_id ORDER BY
timestamp DESC";

$result=mysql_query($query);

$number=mysql_numrows($result);

$i=0;

while($i < $number) {
$ip_src=mysql_result($result,$i,"INET_NTOA(ip_src)");
$ip_dst=mysql_result($result,$i,"INET_NTOA(ip_dst)");
$signature=mysql_result($result,$i,"sig_name");
$timestamp=mysql_result($result,$i,"timestamp");

?>
<table border="2" bgcolor="#b1d2f3">

<tr>
<td>Source Address</td><td><? echo "$ip_src"?></td>
</tr>
<tr>
<td>Destination Address</td><td><? echo "$ip_dst"?></td>
</tr>
<tr>
<td>Attack Signature</td><td><? echo "$signature"?></td>
</tr>
<tr>
<td>Time</td><td><? echo "$timestamp"?></td>
</tr>
</table>

<?
$i++;
}
?>

Perhaps I just need another loop of some sort? Any help is appreciated.

-Robin

sarahk

I'm not sure what all the inet stuff is doing but consider the following

1) change your query to name first field and use it in the order clause

$query="select INET_NTOA(ip_src) as ip_src, INET_NTOA(ip_dst), sig_name, timestamp FROM ip
hdr, event, signature WHERE INET_NTOA(ip_dst) BETWEEN '111.111.111.111' AND
'111.111.222.222' && event.cid=iphdr.cid && event.signature=signature.sig_id ORDER BY ip_src,
timestamp DESC";

2) when looping through the results have a new variable

$lastIP = "x";

then when you go to echo $ip_src
<? echo ($ip_scr == $lastIP ? " ": "$ip_src"?>

then just before you finish the loop
$ip_src = $lastIP;

You could check to see if you needed to do that but it's probably no less efficient to do it.

Anon

The INET_NTOA simply converts the IP address as it is stored in the database to dotted decimal format.

This isn't working for me, can you clarify what variable you are actually using as ip_src is how the info is stored in the database, can you rewirte this using a different name for the first field?

Many thanks,
Robin

Anon

Got it!!!! See below

$query="select INET_NTOA(ip_src), INET_NTOA(ip_dst), sig_name, timestamp FROM ip
hdr, event, signature WHERE INET_NTOA(ip_dst) BETWEEN '206.105.22.49' AND '206.1
05.22.54' && event.cid=iphdr.cid && event.signature=signature.sig_id ORDER BY ti
mestamp DESC, INET_NTOA(ip_src)";

$result=mysql_query($query);

$number=mysql_numrows($result);

$i=0;

if($lastIP==$ip_src) {
?>

<tr>
<td>Destination Address</td><td><? echo "$ip_dst"?></td>
</tr>
<tr>
<td>Attack Signature</td><td><? echo "$signature"?></td>
</tr>
<tr>
<td>Time</td><td><? echo "$timestamp"?></td>
</tr>
</table>

<?
} else {

<tr>
<td bgcolor="#11f3ff">Source Address</td><td bgcolor="#11f3ff"><? echo "$ip_src"
?></td>
</tr>
<tr>
<td>Destination Address</td><td><? echo "$ip_dst"?></td>
</tr>
<tr>
<td>Attack Signature</td><td><? echo "$signature"?></td>
</tr>
<tr>
<td>Time</td><td><? echo "$timestamp"?></td>
</tr>
</table>

<?
}
$lastIP=$ip_src;
$i++;
}
?>