PHP Mysql connect to two DB's for querying

[mcdrr]

Hi, I was wondering what the best way would be to connect to two different mysql databases using php.

for example right now I have

$connection = mysql_connect($user....
then the mysql query.
then a loop.

I want to add another query and connection to another db before the loop ends. Is that even possible? The main thing that I am looking to do is to echo/print the results of the each query before the next IF statement.

thanks.

[wickning1]

You can make as many connections as you want, but putting mysql_connect() inside a loop is a very bad idea. Just open two connections before the loop, and use whichever one you need to.

The $connection variable is what stores all the information about the server, so you just need two of them:

[code]$conn1 = mysql_connect(...first server...);
$conn2 = mysql_connect(...second server...);
$result = mysql_query("SELECT stuff", $conn1);
while ($row = mysql_fetch_array($result) {
    $innerresult = mysql_query("SELECT otherstuff", $conn2);
}[/code]

[fenway]

I could have sworn that I responded to this thread yesterday... just thought I'd mention that at least in other DB interfaces, provided the other DB has the same login credentials, you can switch DBs with "USE <dbname>", or just use the full dbname.tablename.columnname syntax to get to the other DB. Sometimes it's useful, sometimes it's not.

Perl can do this, but I'm not sure if PHP can.

[wickning1]

Good point, when he says different db it always makes me think different server.

PHP has mysql_select_db() for switching, although sending a USE command through might work too.

[mcdrr]

Actually the DB is on another server. I dont know if that makes a difference.

thanks,

[wickning1]

It makes a difference. You'll have to do it the way I showed you in my first response.

[mcdrr]

Here is what I have. Can someone please point me in the right direction or if someone can double check my code. Thanks for all the help.

//Connection to both MYSQL databases on two different servers.
$conn1 = mysql_connect($Dragon_host,$Dragon_user,$Dragon_pass) or die (mysql_errno().": ".mysql_error()."<BR>");
$conn2 = mysql_connect($Snort_host,$Snort_user,$Snort_pass) or die (mysql_errno().": ".mysql_error()."<BR>");
//mysql query that is going
//to be performed
$result = mysql_query("select datetime, sensor, signature, inet_ntoa(source), inet_ntoa(dest), sourceport, destport, protocols from dragon where inet_ntoa(source) like '" .$Drgsrc."' or inet_ntoa(dest) like '" .$Drgsrc."'", $conn1);
echo "connected successfully";
while ($row = mysql_fetch_array($result) )
{
$innerresult = mysql_query("select inet_ntoa(ip_src), inet_ntoa(ip_dst), layer4_sport, layer4_dport, timestamp, sig_name, sid, cid from acid_event where inet_ntoa(ip_src) like '" .$Src."' or inet_ntoa(ip_dst) like '" .$Src."'", $conn2);

echo '<tr>';
echo $row['rowname'];
echo '<td align="center">',$row['datetime'],'</td>';
echo '<td align="center">',$row['sensor'],'</td>';
echo '<td align="center">',$row['signature'],'</td>';
echo '<td align="center">',$row['inet_ntoa(source)'],'</td>';
echo '<td align="center">',$row['inet_ntoa(dest)'],'</td>';
echo '<td align="center">',$row['sourceport'],'</td>';
echo '<td align="center">',$row['sig_name'],'</td>';

I am getting the following error.
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 31
which is while ($row = mysql_fetch_array($result) )

thanks,

[fenway]

Well, you echo "connected successfully", but you're not actually checking a mysql_error($result) from your previous query to $conn1. That sounds like the culprit, since an invalid connection would be previous die() statements.

[mcdrr]

Well at this point my next question would be how would you guys go about running 2 queries on two different servers and printing the results.

Can someone please give me an example. I need it to be all in one statement as I am using If Else statements.

thanks for the help.

[fenway]

First, was the previous problem resolved? Second, wickning1 has already shown you the "way" to do this... you'll have to issue a separate query for each DB involved. I don't understand what the if/else structure has to do with this, or what you mean by "one statement".

[mcdrr]

Here is what I currently have. My first query $result runs successfully and outputs correct data. I am not sure/quite understand how to on implement the $innerresult and also how would I print the results for $innerresult query? Am I missing code for fetching results for $innerresult? How would I select a db in the second query before it is run? On my first query I am doing a @mysql_select. Thanks again for all the help again I dont have to much experience with php and mysql.

thanks again!!!

//mysql query
//being performed
@mysql_select_db($Dragon_dbname) or die( "Unable to select database");
$result = mysql_query("select soc.dragon.datetime, soc.dragon.sensor, soc.dragon.signature, inet_ntoa(soc.dragon.source), inet_ntoa(soc.dragon.dest), soc.dragon.sourceport, soc.dragon.destport, soc.dragon.protocols from dragon where inet_ntoa(soc.dragon.source) like '" .$Drgsrc."' or inet_ntoa(soc.dragon.dest) like '" .$Drgsrc."' limit 1000", $conn2);
if (!$result) {
echo 'could not run query: ' . mysql_error();
exit;
}
$row = mysql_fetch_row($result);
echo 'connected fetch';
if (!$row) {
echo 'could not fetch records: ' . mysql_error();
exit;
}
while ($row = mysql_fetch_array($result) )
{
$innerresult = mysql_query("select inet_ntoa(ip_src), inet_ntoa(ip_dst), layer4_sport, layer4_dport, timestamp, sig_name, sid, cid from acid_event where inet_ntoa(ip_src) like '" .$Src."' or inet_ntoa(ip_dst) like '" .$Src."' limit 1000", $conn1);

echo '<tr>';
echo $row['rowname'];
echo '<td align="center">',$row['inet_ntoa(soc.dragon.source)'],'</td>';
echo '<td align="center">',$row['inet_ntoa(soc.dragon.dest)'],'</td>';
echo '<td align="center">',$row['sourceport'],'</td>';
echo '<td align="center">',$row['destport'],'</td>';
echo '<td align="center">',$row['datetime'],'</td>';
echo '<td align="center">',$row['signature'],'</td>';
echo '<td align="center">',$row['sig_name'],'</td>';

[wickning1]

You need to fetch some results from $innerresult. Maybe $innerrow = mysql_fetch_array($innerresult);