drop columns that don't return data

[stevejacobsen]

I'm working on a PIX log analyzer. I've read the log into a database and now I'm working on an adhoc query page. I have every field in a form where I can select SourceIP, DestinationIP etc. I have the form and the sql query setup to act on all fields of the table. In some cases the query returns data on in all columns in other the columns are blank because there is no data in that particular selection.

What I'd like to do is to have the query not return the columns that do not have data.

This is where I started. When I selected some IP addresses it returned rows that only had information in half of the columns.

$sql="SELECT * FROM pixlog WHERE SourceIP LIKE '$SourceIP%' AND DestinationIP LIKE '$DestinationIP%'";
$rs=mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($rs) or die(mysql_error());

Here's my table:

CREATE TABLE pixlog (
ID int(10) unsigned NOT NULL auto_increment,
Date date default NULL,
Time time default NULL,
MessageNumber tinytext,
Protocol tinytext,
SourceIP tinytext,
SourcePort tinytext,
SourceInterface tinytext,
DestinationIP tinytext,
DestinationPort tinytext,
DestinationInterface tinytext,
Message text,
PRIMARY KEY (ID),
KEY ID (ID)
) TYPE=MyISAM;

Here's a link to an image of the result I get:

http://www.the-jacobsens.com/log.png

In this case I'd like for the Protocol, SourcePort, Source Interface and Destination port columns to not be returned in the sql query. If that could happen then I can dynamically build the adhoc queries based on the output of the sql statement.

/steve

[nemonoman]

Try something like:

$sql="SELECT Protocol, SourcePort, SourceInterface, Destination FROM pixlog WHERE SourceIP LIKE '$SourceIP%' AND DestinationIP LIKE '$DestinationIP%'";
while($row = mysql_fetch_array($rs)){

$protocol[]=$row[0];
$sourceport[]=$row[1];
$sourceinterface[]=$row[2];
$destination[]=$row[3];
}