Jump to content

Archived

This topic is now archived and is closed to further replies.

mcdrr

PHP Mysql connect to two DB's for querying

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Actually the DB is on another server. I dont know if that makes a difference.

thanks,

Share this post


Link to post
Share on other sites
It makes a difference. You'll have to do it the way I showed you in my first response.

Share this post


Link to post
Share on other sites
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,

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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".

Share this post


Link to post
Share on other sites
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>';



Share this post


Link to post
Share on other sites
You need to fetch some results from $innerresult. Maybe $innerrow = mysql_fetch_array($innerresult);

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.