Issue connecting to multiple databases

[wwfc_barmy_army]

Hello,

 

I'm using this code in a php file to connect to 2 databases (something that I need to do):

$conn_local = mysql_connect('localhost','root','',TRUE);
$conn_local2 = mysql_connect('localhost','root','');
mysql_select_db('db1',$conn_local);
mysql_select_db('db2',$conn_local2); 

 

I'm then trying to use this code to call it from the 1st Database:

$sql = "SELECT * FROM news_items ORDER BY news_date DESC LIMIT 0,$limit";
    $result = mysql_query($sql,$conn_local);
    if(mysql_num_rows($result)!=0){
    while($row = mysql_fetch_array($result))
...etc

 

Although I am getting this error:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\test\includes\function.php on line 17

 

If I ONLY include the 1 database and don't bother putting the ',$conn_local' into the mysql_query it will work fine and return the records needed. It only seems to be when I try and include more than 1 database.

 

Any ideas where I'm going wrong?

 

Thanks.

[trq]

if both databases are on the same server you do not need 2 different connections. Just use mysql_select_db to switch between the two databases as needed.

[wwfc_barmy_army]

Hello Thorpe.

 

Thanks for your reply.

 

It is for now, but wont be when it goes 'live'. Never the less should the method above not work? Or is there a different way?

 

Thanks.

[kickstart]

Hi

 

If you do have 2 connections to the same database then the 4th parameter on the connect will need to be true on anything other than the first connection (otherwise the 2nd connection will just return the identifier of the first connection).

 

All the best

 

Keith

[PFMaBiSmAd]

I'm going to guess that in your actual code, you have a variable scope problem.

[wwfc_barmy_army]

I'm going to guess that in your actual code, you have a variable scope problem.

 

Hello,

 

what do you mean by this?

 

Thanks.

[PFMaBiSmAd]

For the exact code snippets you posted, using the same username/password to make the connection(s), and the stated symptom (query works without the $conn_local variable in the mysql_query() statement), the most likely reason why your query is failing with an error is if the $conn_local variable doesn't exist in the same scope where the mysql_query() statement is at.

 

Your code should (but doesn't) have error checking and error reporting logic in it to tell you/log why the query is failing, both now during development and later on the live server.

[wwfc_barmy_army]

The database file is included in the header of the website so it there on all pages, although the code is in a function in a functions.php page.

 

I've tried echo'ing $conn_local although nothing is displayed (i know I wont get anything back other than an object but I would expect something to be displayed if it exists). Although surely if it's included at the top of the page is should be there when called within the function.

 

Although, I have figured that if I do this:

$sql = "SELECT * FROM dbname.news_items ORDER BY news_date DESC LIMIT 0,$limit";
    $result = mysql_query($sql) or die(mysql_error());
    if(mysql_num_rows($result)!=0){
    while($row = mysql_fetch_array($result))
        {

 

then it appears to work. (adding the dbname.table)

 

Although I didn't really want to go through specifying database names in the queries as these will change when it goes live.

 

Any suggests on what I could try?

 

Thanks.

[kickstart]

The database file is included in the header of the website so it there on all pages, although the code is in a function in a functions.php page.

 

Is $conn_local and $conn_local2 passed to the function, or declared as global within the function? If not then $conn_local within the function is totally separate to $conn_local outside the function where it is initialised.

 

All the best

 

Keith

[PFMaBiSmAd]

the code is in a function

 

You do know that every function has an isolated local variable scope so that you can write whatever code, using whatever variables you need inside that function without needing to worry about interfering with the operation of the code that is calling that function?

 

You need to pass the connection link into the function when you call the function -

 

your_function($conn_local){
    ....
    $result = mysql_query($sql,$conn_local);
    ....
}

[PFMaBiSmAd]

I also suggest setting error_reporting to E_ALL (or to a -1) and display_errors to ON (it apparently is already set to this value) so that all the php detected errors will be reported and displayed. You would have been getting a undefined notice message concerning the non-existent $conn_local variable inside of the function. You will save a TON of time.

[wwfc_barmy_army]

Ok Thanks Guys. I will look into everything you've mentioned

 

Without maybe sounding stupid, I have a number of functions already that I have been calling which have been working fine from the database without me passing a connection variable through to the function. Is that fine as long as there isn't multiple connections like i've now tried to do now?

 

Also PFMaBiSmAd, my error_reporting is already set to E_ALL and display_errors is on but I still don't get an undefined noticed message on the $conn_local.

 

Thanks again guys.

 

[kickstart]

Hi

 

If you don't specify the connection it will just use the last one made.

 

If you have multiple connections and want to use the one that wasn't the last one made then you need to specify it. However if you do this within a function you need to pass through the variable to that function, either as a parameter to the function or as a global. If you do not do this then the variable used for the connection in the function is completely separate from the one outside the function that you assigned the connection to. Within the function it will be a new variable with no value at all.

 

All the best

 

Keith

[wwfc_barmy_army]

Brill. Thanks. Learn something new everyday.

 

I'll work on it.

[wwfc_barmy_army]

Hello Guys,

 

I've come to try it this morning but I am still getting an error.

 

My database connection file:

$conn_local2 = mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('db1',$conn_local2) or die(mysql_error()); 

$conn_local = mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('sb2',$conn_local) or die(mysql_error()); 

 

Calling the function:

<?php echo get_News(3, $conn_local2); ?>

 

The function:

function get_News($limit, $connx) {
$sql = "SELECT * FROM news_items ORDER BY news_date DESC LIMIT 0,$limit";
    $result = mysql_query($sql, $connx);
    if(mysql_num_rows($result)!=0){
    while($row = mysql_fetch_array($result))
        {
....

 

The error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\mysite\includes\adv-link\adv-function.php on line 69

 

Can anyone see where I'm going wrong?

 

Thanks.

 

 

[trq]

You failed to check if your query was successful before using its result.

[Muddy_Funster]

Surely the issue here is that you are instantly overwriting the selected database to db2 at the start of each page?

mysql_select_db('db1',$conn_local);
mysql_select_db('db2',$conn_local2); 

It's not something I have tried in PHP, but the logical flow seems to be showing that to be the case.

[kickstart]

Hello Guys,

 

I've come to try it this morning but I am still getting an error.

 

My database connection file:

$conn_local2 = mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('db1',$conn_local2) or die(mysql_error()); 

$conn_local = mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('sb2',$conn_local) or die(mysql_error()); 

 

Thorpe os quite correct that you have nothing to check the return from the mysql_query, and suspect if you did the error would be that table news_items isn't found in database sb2

 

The problem is that your 2 connections are the same and the 2nd is over writing the first rather than doing a new connection so when you select the 2nd database it is selected for both connections.

 

What you need is the 4th parameter of true in the mysql_connect on the 2nd and subsequent connections.

 

All the best

 

Keith

[wwfc_barmy_army]

Thanks thorpe, Muddy_Funster and Kickstart.

 

Kickstart was spot on, i've put the 'true' into the second connection and passed the connection string to the function and it's working ok now.

 

Thanks for your help guys!

 

P.s. I must use error reporting more