Jump to content

PHP Mysql connect to two DB's for querying


mcdrr

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.
Link to comment
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]
Link to comment
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.
Link to comment
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,
Link to comment
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.
Link to comment
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.
Link to comment
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".
Link to comment
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>';



Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.