Jump to content


Photo

PHP Mysql connect to two DB's for querying


  • Please log in to reply
11 replies to this topic

#1 mcdrr

mcdrr
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 05 April 2006 - 04:56 PM

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.

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 05 April 2006 - 05:13 PM

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:

$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);
}


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 April 2006 - 06:33 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 06 April 2006 - 12:08 PM

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.

#5 mcdrr

mcdrr
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 06 April 2006 - 04:25 PM

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

thanks,

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 06 April 2006 - 07:55 PM

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

#7 mcdrr

mcdrr
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 10 April 2006 - 08:22 PM

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,

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 April 2006 - 08:58 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 mcdrr

mcdrr
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 11 April 2006 - 01:41 AM

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.

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 April 2006 - 01:45 PM

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".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 mcdrr

mcdrr
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 11 April 2006 - 09:25 PM

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>';





#12 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 11 April 2006 - 11:44 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users