accessing 2 databases

[franches]

I have a problem with my code. I don't know if I did it right. In my $SQLString it is accessing two database. In my database TEST i have StaffTable and in my database datalog I have Employees table. I was trying to echo $NewTableRec; but nothing is echoed instead I got the message "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ...". Is there someone could help me solve my problem?

$dbHost = "localhost";
$dbUser = "root";
$dbname = "TEST";
$dbname2="datalog";

$db = mysql_connect($dbHost,$dbUser); // Connection Code
$rs=mysql_select_db($dbname);
$rs2=mysql_select_db($dbname2);

$SQLString="SELECT StaffTable.PIN AS PIN, StaffTable.NickName AS NickName, StaffTable.Name AS Name FROM StaffTable INNER JOIN datalog.Employees ON StaffTable.PinProfile = datalog.Employees.Payroll_id WHERE StaffTable.PinProfile = '".$_SESSION['username']."'";

$rs=mysql_query($SQLString);
$NewTableRec=mysql_num_rows($rs);
echo $NewTableRec;

[jatar_k]

same connection so you can't just swap dbs like that, also mysql_select_db only returns boolean and sets the active db for the specified connection. You need 2 seperate connections

$db1 = mysql_connect($dbHost,$dbUser);
mysql_select_db($dbname,$db1);

$db2 = mysql_connect($dbHost,$dbUser);
mysql_select_db($dbname2,$db2);

you will have to be sure to specify the proper link identifier in any subsequent mysql_query calls.

[Salsa]

The very first thing I'd do is to work mysql_error() into the code so you can have better hints as to what's wrong, like:

if(!($rs = mysql_query($SQLString))) die("query failed. MySQL says: ".mysql_error());
if(!($NewTableRec=mysql_num_rows($rs))) die("num_rows failed. MySQL says: ".mysql_error());

Next, you don't need to open two connections, or to even use mysql_select_db(), much less twice. (You must then qualify which database(s) to use in your query, however, like: database.table.field.) I think your biggest problem is that it's your $dbname2 (datalog) that is current at the time your query is sent, not $dbname (TEST) as you are thinking. That's because your second call to mysql_select_db() has made $dbname2 current. Assigning your mysql_select_db()'s to variables does nothing to change this; it's your most recent call made that sets the current database.

Assuming that your $SQLstring is okay, simply removing your second call to mysql_select_db() might make it work. That's because, when you wrote the $SQLstring, you assumed that it was your TEST database that was current, and you qualified your $SQLstring accordingly, specifying "datalog.Employees.Payroll_id," for example, even though it was your datalog database that was current, and leaving MySQL unable to find "StaffTable.PIN" while it would have found "TEST.StaffTable.PIN" (IF EXISTS:-)

Just a final tidbit: I'm not sure of your thinking in assigning both a call to mysql_select_db() and to mysql_query() to the variable $rs. The two functions are not related in the way you may be thinking. Your first assignment to $rs would simply be a shortcut to typing out "mysql_select_db($dbname)" someplace later in the script. However, you overwrote that $rs value when you used it to represent the id of the result set returned by mysql_query();

I hope this helps.

[franches]

thank you very much guys.

[coopster]

Although Salsa is correct, there is one caveat to this statement...

Next, you don't need to open two connections, or to even use mysql_select_db(), much less twice.

True, you wouldn't need to open two connections, but this will work only if the databases are running on the same server and the userid in the connection has the correct authorizations.

[Salsa]

That's right, Coopster, but in this context (one in which Franches is accessing the databases as root and not using a password) I assumed he was just working on a development machine and had more than enough privileges. That might have been a bad assumption though, because I can't really think of another good reason why he'd have these two tables in different databases.

Also, I meant no disrespect to Jatar by contrdicting him. I 100% respect his great contributions here and am usually in awe of his answers. Thanks!

Franches: one of the things that really glared at me in your code that I also didn't mention before was what I said above about accessing the databases as root and with no password. Absolutely do not do that in a production environment. Immediately set a password for root, and for accessing MySQL from scripts, create a new user with limited privileges and also require a password. I'm even strict with myself about this on my local machine.

I wish you all well.

[jatar_k]

>> I meant no disrespect to Jatar by contrdicting him

contradict me all you like, I sure not right lots of the time or, as in this case, I was thinking of the wrong context. I was thinking different hosts etc.

and geez Salsa, you're right, don't apologize for that. ;)

>> in awe of his answers

aw shucks, I have to say though coop and I had a good laugh at that. I more often feel far from awe inspiring, heh.

thanks though, a very hearty compliment.