PHP mysql query/data extraction from two DB's

[rscrsc]

Hi, I was wondering if I could please get some help or ideas on how to accomplish this task that I have been working on for a few days now.

What I want to do is have an html page where you enter an IP adddress and it will search two mysql databases for entries of the IP.

IE enter 192.168.1.1
search both sql1db and sql2db and spit out the entire row. Which is made up of IP, Timestamp, date, alertname, iphhdr

here is the code that I currently have. I am new to mysql and php so if my coding is bad please bare with me.

<?
$host = "localhost";
$user = "test";
$pass = "test";
$dbname = "IPalert";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
// This command will perform the query
$query= "select * from signature where sig_name= '" . $_POST['IP'] . "'";
//this will try to spit out the previous sql query
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
//this is a line break before the spit output
while ($line = mysql_fetch_row($result) )
{
echo $line[0]."\t".$line[1]."\t".$line[2]."t".$line[3]."t".$line[4]."<br>";

now here are where I am stuck. When when I run the query I get the correct results, but how do I show the name of each of the columns in the tables. Instead of just showing the results line by line. Also how to I make my script at the same time also search another database on the same machine.

thanks very much for the help in advance.
If you have any questions please feel free to ask.

thanks again!

[khaki monster]

are you talking about html table?

[jatar_k]

Welcome to WebmasterWorld rscrsc,

as far as outputting column names, since they are always the same you could just hard code them and add them to the echo line. You could also use a foreach loop to output them

foreach ($line as $key $value) {
echo $key . ' : ' . $value . "\t";
}

but hardcoding the names might be simpler

as for how to then connect to a second db on the same machine. Since the $connection var is still good you just could issue another mysql_select_db call and then issue your query again and parse through your results.

[SeanW]

For multiple dbs, fully qualify your select statement:

SELECT * from db2.table;

You should be able to join between dbs too

SELECT * FROM db2.table AS t2, db1.table AS t1 WHERE t1.id = t2.id

and so forth.

Sean

[rscrsc]

I am having a little problem trying to get this statement to work. I am sure its because I am doing something wrong.

where am I supposed to add this in my statement. Am I supposed to remove any line before adding this?

foreach ($line as $key $value) {
echo $key . ' : ' . $value . "\t";
}

I have tried adding it after the my last line of code and I have also tried removing my echo statements and adding the lines above. But when I add the lines above I stop getting results.

thanks again for help!

[jatar_k]

it replaces this line

echo $line[0]."\t".$line[1]."\t".$line[2]."t".$line[3]."t".$line[4]."<br>";

like this

while ($line = mysql_fetch_row($result) )
{
foreach ($line as $key $value) {
echo $key . ' : ' . $value . "\t";
}

and then whatever comes after that would follow inside your while loop

[rscrsc]

Thanks for the reply. This is how my code currently looks like. I have made the changes like you suggested, but the problem is when I try to run the same search that I ran before I just get a blank screen with no results.

Any ideas or advice as to what else could be wrong.

thanks again for all your help!

<?
$host = "localhost";
$user = "snort";
$pass = "snort";
$dbname = "snort";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
// This command will perform the query
$query= "select * from signature where sig_name= '" . $_POST['IP'] . "'";
//this will try to spit out the previous sql query
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
//this is a line break before the spit output
while ($line = mysql_fetch_row($result) )
{
foreach ($line as $key $value) {
echo $key . ' : ' . $value . "\t";
}
?>

[jatar_k]

I have had that problem before with hosts, was an untrapped error, for some reason error reporting was turned off.

dumb
have you seen parse errors before with your site (just in case it is the same thing)?

maybe I am totally losing it and my code doesn't work at all.

The other thing you could do is use an html table to output your results and put your column headings hard coded right in

[rscrsc]

Yeah in the past when I have had a code error it told me what it was. Does anyone else have any ideas? What I am trying to is have the colums names automatically printed after the query runs.

for example if I type in 192.168.1.1

it should display this

time ipaddress sig_id date
4.30 192.168.1.1 test_sig 4/4/2004

Currenly with the code I have i get the following

4.30 192.168.1.1 test_sig 4/4/2004

thanks again for the help. I would really like to not use html code if possible to print the tables. Any further help would be very much appreciated as is all the help that everyone have alreay supplied.

thanks again!