Results 1 to 17 of 17
-
05-08-2006, 02:04 AM #1Junior Guru Wannabe
- Join Date
- Aug 2004
- Posts
- 93
mysql newbie: how to connect to multiple databases?
I want to gather counts of records from tables in several different databases and display the results as just numbers next to some text like this:
blog1
posts (3)
comments (12)
blog2
posts (11)
comments (90)
blog3
posts (8)
comments (34)
Here's a first crack at it using 3 different databases (all have same userid/password):
PHP Code:@ $db = mysql_connect('localhost','myuserid','mypasswd');
mysql_select_db('db01');
$qryblog01_posts = "select count(*) -1 as ct from wp_posts";
$qryblog01_comments = "select count(*) as ct from wp_comments";
$result01_posts = mysql_query($qryblog01_posts);
$result01_comments = mysql_query($qryblog01_comments);
if ($result01_posts && $result01_comments)
{
$array01_posts = mysql_fetch_array($result01_posts);
$array01_comments = mysql_fetch_array($result01_comments);
}
mysql_close;
@ $db = mysql_connect('localhost','myuserid','mypasswd');
mysql_select_db('db02');
$qryblog02_posts = "select count(*) -1 as ct from wp_posts";
$qryblog02_comments = "select count(*) as ct from wp_comments";
$result02_posts = mysql_query($qryblog02_posts);
$result02_comments = mysql_query($qryblog02_comments);
if ($result02_posts && $result02_comments)
{
$array02_posts = mysql_fetch_array($result02_posts);
$array02_comments = mysql_fetch_array($result02_comments);
}
mysql_close;
@ $db = mysql_connect('localhost','myuserid','mypasswd');
mysql_select_db('db03');
$qryblog03_posts = "select count(*) -1 as ct from wp_posts";
$qryblog03_comments = "select count(*) as ct from wp_comments";
$result03_posts = mysql_query($qryblog03_posts);
$result03_comments = mysql_query($qryblog03_comments);
if ($result03_posts && $result03_comments)
{
$array03_posts = mysql_fetch_array($result03_posts);
$array03_comments = mysql_fetch_array($result03_comments);
}
mysql_close;
//Then I display the counts like this:
echo " (".$array01_posts[0].") (".$array01_comments[0].")";
echo " (".$array02_posts[0].") (".$array02_comments[0].")";
echo " (".$array03_posts[0].") (".$array03_comments[0].")";
Do I need to close and open the connection each time? Is there a way to just switch the database? Can someone point me to sample code that does this?
The code above is working, but I'm sure it's a hack. Any suggestions for improvement welcome. Do I need to use an array? What kind of error handling should I use?
Thanks in advance.
-
05-08-2006, 02:09 AM #2Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
You don't need to close the connection, just switch the database each time
PHP Code:mysql_connect('localhost','foo','bar');
mysql_select_db('zoo');
// -- do some queries on 'zoo' database
mysql_select_db('zoo1');
// -- do some queries on 'zoo1' database
mysql_select_db('zoo3');
// -- do some queries on 'zoo3' database
mysql_close(); // -- when you are finished with the CONNECTION, close it (not with the database).
-
05-08-2006, 02:16 AM #3Junior Guru Wannabe
- Join Date
- Aug 2004
- Posts
- 93
Don't know why I didn't see this... man was that easy...
PHP Code:@ $db = mysql_connect('localhost','myuserid','mypasswd');
mysql_select_db('db01');
$qryblog01_posts = "select count(*) -1 as ct from wp_posts";
$qryblog01_comments = "select count(*) as ct from wp_comments";
$result01_posts = mysql_query($qryblog01_posts);
$result01_comments = mysql_query($qryblog01_comments);
if ($result01_posts && $result01_comments)
{
$array01_posts = mysql_fetch_array($result01_posts);
$array01_comments = mysql_fetch_array($result01_comments);
}
//mysql_close;
//@ $db = mysql_connect('localhost','myuserid','mypasswd');
mysql_select_db('db02');
$qryblog02_posts = "select count(*) -1 as ct from wp_posts";
$qryblog02_comments = "select count(*) as ct from wp_comments";
$result02_posts = mysql_query($qryblog02_posts);
$result02_comments = mysql_query($qryblog02_comments);
if ($result02_posts && $result02_comments)
{
$array02_posts = mysql_fetch_array($result02_posts);
$array02_comments = mysql_fetch_array($result02_comments);
}
//mysql_close;
//@ $db = mysql_connect('localhost','myuserid','mypasswd');
mysql_select_db('db03');
$qryblog03_posts = "select count(*) -1 as ct from wp_posts";
$qryblog03_comments = "select count(*) as ct from wp_comments";
$result03_posts = mysql_query($qryblog03_posts);
$result03_comments = mysql_query($qryblog03_comments);
if ($result03_posts && $result03_comments)
{
$array03_posts = mysql_fetch_array($result03_posts);
$array03_comments = mysql_fetch_array($result03_comments);
}
mysql_close;
-
05-08-2006, 09:21 AM #4WHT Addict
- Join Date
- Oct 2001
- Location
- Mountain Home Arkansas
- Posts
- 131
I use muliple databases all the time, and you don't really need to switch between them, just open them with different resource id's, and specify the resource link identifer in your queries. ( I accually always write my queries that way, incase I need to connect to a different database later on in development )
PHP Code:$db1 = mysql_connect('localhost','myuserid','mypasswd');
$sel1 = mysql_select_db('db01');
$db2 = mysql_connect('localhost','myuserid','mypasswd');
$sel2= mysql_select_db('db02');
$res1 = mysql_query($query, $db1);
$res2 = mysql_query($query, $db2);
rocket science is more fun when you accually have rockets!
-
05-08-2006, 09:41 AM #5Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
In your case, PHP isn't actually going to open up another connection, just re-use the one you already made because the credentials are the same; so in essence you have really just wasted typing time
If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.
-
05-08-2006, 08:44 PM #6Junior Guru Wannabe
- Join Date
- Aug 2004
- Posts
- 93
Thanks for the reply, guys - helpful discussion.
I'm still wondering ifPHP Code:mysql_fetch_array
Is this a hack or just the way it's done in mysql?
-
05-08-2006, 09:42 PM #7WHT Addict
- Join Date
- Oct 2001
- Location
- Mountain Home Arkansas
- Posts
- 131
well, mysql_fetch_array() is not the only way, and I can't 100% say that it's the best way, but I think it's the most common way to loop through a database.
You probably want to use it in a while loop.
PHP Code:$res = mysql_query($query, $db);
while ( $array = mysql_fetch_array($res) ){
echo $array['field1']. - '.$array['field2'].'<br />';
}
rocket science is more fun when you accually have rockets!
-
05-09-2006, 02:50 AM #8Web Hosting Master
- Join Date
- Jul 2003
- Location
- Kuwait
- Posts
- 5,104
Its not a hack, its just one of the many ways to get the information. You can use any of the mysql_fetch_* functions.
-
05-09-2006, 09:00 AM #9Junior Guru Wannabe
- Join Date
- Aug 2004
- Posts
- 93
10-4.
The other thing I can do is lose the if statements. If the queries fail, the variables will be empty (non-printing) anyway.
And I should probally wrap the whole thing in
PHP Code:if ($db)
{
...
}
-
05-09-2006, 09:10 AM #10WHT Addict
- Join Date
- Oct 2001
- Location
- Mountain Home Arkansas
- Posts
- 131
the while() loop will take care of that for you without the need of an if() statement, the while() loop will only run while there is records to be found in the table
rocket science is more fun when you accually have rockets!
-
05-13-2010, 08:41 AM #11Newbie
- Join Date
- May 2010
- Posts
- 7
multiple databases
What I have so far is this:
$connection="...";
$username="...";
$password="...";
$database1="...1";
$database2="...2";
$link=mysql_connect($connection,$username,$password) or die(mysql_error());
mysql_select_db($database1) or die( "Unable to select database");
$query1 = "SELECT * FROM table1";
$result1 = mysql_query($query1);
mysql_select_db($database2) or die( "Unable to select database");
$query2 = "SELECT * FROM table2";
$result2 = mysql_query($query2);
while($nt2=mysql_fetch_array($result2))
{
...
}
These two databases are on the same server, have the same connection string, username, and password. When I get to the 'while($nt2=' line, I get a "supplied argument is not a valid MySQL result resource" error. It seems like this query is trying to pull from db1 instead of db2 as specified. I tried putting a mysql_select_db right before the query, but that didn't help. Any ideas?
-
05-13-2010, 11:47 AM #12Web Hosting Master
- Join Date
- May 2009
- Posts
- 766
-
05-13-2010, 05:58 PM #13Newbie
- Join Date
- May 2010
- Posts
- 7
Almost, but not quite
Ok, now I have this, based on the above example...
$db1 = mysql_connect($connection,$username,$password) or die(mysql_error());
$sel1 = mysql_select_db($database1);
$db2 = mysql_connect($connection,$username,$password) or die(mysql_error());
$sel2 = mysql_select_db($database2);
$query1 = "SELECT * FROM yp_events ORDER BY event_title";
$result1 = mysql_query($query1, $db1);
$nt1=mysql_fetch_array($result1, $sel1);
^
|
This line produces a "supplied argument is not a valid MySQL result resource" error. I tried:
$nt1=mysql_fetch_array($result1, $db1);
and
$nt1=mysql_fetch_array($result1);
but all with the same results. What am I doing wrong?
I need to do a while on $nt1, so I thought I could do while($nt1) instead of while($nt1=mysql_fetch_array($result1)), but I can't get the fetch array to work. Any ideas?
-
05-16-2010, 08:25 AM #14Newbie
- Join Date
- May 2010
- Posts
- 7
Solved
Here's what I came up with from the advice:
$connection="localhost";
$username="user";
$password="pass";
$database1="db1";
$database2="db2";
$db1 = mysql_connect($connection,$username,$password) or die(mysql_error());
$sel1 = mysql_select_db($database1, $db1);
$query1 = "SELECT * FROM table1";
$result1 = mysql_query($query1, $db1);
$db2 = mysql_connect($connection,$username,$password) or die(mysql_error());
$sel2 = mysql_select_db($database2, $db2);
$query2 = "SELECT * FROM table2";
$result2 = mysql_query($query2, $db2) or die(mysql_error());;
while($nt2 = mysql_fetch_array($result2, $db2))
{
...
}
This actually works. Thanks, guys!
-
09-16-2010, 12:36 AM #15Newbie
- Join Date
- Sep 2010
- Posts
- 11
Hi Daltman, thanks for posting this code. I'm actually trying to do the same thing but i'm also using Jqeuery to display the information. Later on i'll post the code i have so far, but i was wondering if you were also using Jquery?
-
09-16-2010, 03:23 PM #16Newbie
- Join Date
- May 2010
- Posts
- 7
I've never used jquery, and don't know much about it. Sorry.
-
09-23-2010, 08:12 AM #17Newbie
- Join Date
- Nov 2009
- Location
- India
- Posts
- 6
Thanks for sharing..