best way to have multiple database connections

[Tommybs]

Hi,

I'm getting more into the database security side of my web apps. Creating separate users for select, insert,delete etc. and then granting each user specific privaleges on different tables, sprocs etc.

The issue I'm having though is that for some reason, a lot of my queries fail when passing in a resource identifier.

$dbr = mysql_connect($host,$name,$pass);//dbr only has select
mysql_select_db($db,$dbr);

$dbw = mysql_connect($host1,$name2,$pass3);//dbw can insert update etc.
mysql_select_db($db,$dbw);

The problem I have is that when i then do something such as

$q = mysql_query("insert into table(field) values(value)", $dbw);

I'll receive an error with regards to the invalid resource identifier. What's the best way to go about this kind of setup. Shoud I use a function that returns the correct handle such as

function getConn($key){
$dbh;
 switch($key){
  case "reader":
   $dbh = mysql_connect($host,$name,$pass);
break;
   case "writer":
$dbh = mysql_connect($host1,$name1,$pass1);
break;
case default:
$dbh = mysql_connect($host,$name,$pass);
break; // reader only
}
mysql_select_db($db, $dbh);
return $dbh;
}

Would appreciate thoughts on this, as it is one thing I really want to master.

thanks

[Tommybs]

Nevermind, I sorted it, I wasn't referencing the correct variable.... :S what can I say I'm tired and we all make mistakes.

[Tommybs]

Ok, the issue above was sorted but I'm still seeing an issue. I'm not sure if it's related to using the link identifier or not but this is really bugging me. I've got a stored procedure that is return a list of IDs and storing them in array. I do a dump on the array and all the ids are there everytime I hit the page. Using these IDs I then loop through and use them to create an object. On the object creation it also pulls back a title related to the ID in question. All this information is definitely in the database. Calling both sprocs in my mysql client pulls back all the correct information.

However in php it only seems to display the correct information every other time! I get a Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource error for every other line. I can't see why this is happening. It's using the same connection string, all the info is correct in the database. In fact if I change the connection it uses it brings back the opposite every other line ( e.g. with 8 rows conn1 brings back 1,3,5,7 and conn2 brings back 2,4,6,8 )

What's weird is if I refresh the page, and refresh whilst it is refreshing it display everything correctly.

This is really bugging me. I also have a authentication check running for a logged in user, which also seems to be failing on certain calls. My header links display as if I'm logged out, yet the information I'm viewing is only available to logged in users.

I'm sure I never had these issues before stored procedures and multiple connections with different permissions. But I want to use these advanced features!

FYI I'm using mysql not mysqli ( mysqli seems even more buggy ).

Please someone shed some light before I tear out my hair!

Thanks

[Tommybs]

It appears this only seems to happen with stored procedures and not with inline queries, the whole reason I want to use stored procedures is to separate this logic!

[coopster]

I use the mysqli functions without issue. I ran into one bug early on in PHP 5.3 but that bug was resolved in a version update. You are likely getting errors on your connection but you don't see them because you are not monitoring the mysql_connect function for a successful connection. Monitor that and dump the mysql_errno and mysql_error if there is an issue.

What happens if you use the new_link argument on your mysql_connect? I realize you probably wouldn't need to because the parameters are indeed different but it's worth a shot.

[Tommybs]

Yeah, I've tried all that, you have to use the extra parameters for stored procedures anyway. I just don't get how on the same page some queries connect and some say there's an invalid resource, especially when it's only looping through rows of the same query!

Mysqli is the same for me, every now and then it just reaches a query and bums out, I might start looking more at the mysql side of things in case the problem lies there, though running the queries in the shell does seem to give me the correct results without any hang ups.