I have a site I'm working on that has all of the site content in one mysql database. I recently added the ability for visitors to register to gain access to restricted content. That includes registering, changing a password, and retrieving a lost password, etc. Because the visitor will need Select, Insert, and Update privileges, I was concerned about granting those privileges to unknown visitors for the entire site DB, so I chose to make a separate DB for external users.
Everything is working great, registration, activation, changing password and retrieving a password. The problem now is that I am trying to access the ext_users DB from within the same Admin area for the general site content, but it only works on my development server (MAMP Pro 2.1.1, Apache 2.2.22, mysql 5.5.25, php 5.4.4) not on my live server (centos 5.9 i686 virtuozzo – vps, Apache 2.2.21, mysql 5.0.96, php 5.?) Here is what I did.
I created two connection scripts each with their own variables, and did a require_once() for each in admin.php. This first one is to the general site DB:
$hostname_siteadmin = "localhost";
$database_siteadmin = "generalsiteDB";
$username_siteadmin = "generalsiteUSER";
$password_siteadmin = "generalsitePWD";
$siteadmin = mysql_pconnect($hostname_siteadmin, $username_siteadmin, $password_siteadmin) or trigger_error(mysql_error(),E_USER_ERROR);
The second one is to the external user DB:
$hostname_vusradmin = "localhost";
$database_vusradmin = "extusersiteDB";
$username_vusradmin = "extusersiteUSER";
$password_vusradmin = "extusersitePWD";
$vusradmin = mysql_pconnect($hostname_vusradmin, $username_vusradmin, $password_vusradmin) or trigger_error(mysql_error(),E_USER_ERROR);
As I said, on my testing server this works with no problems, but on the production server only the connection script which is loaded last works. This would seem to indicate a variable conflict, but again, the variables are different for each connection script as shown above. My error handling script returns this error when attempting to view the registered users:
mysql_num_rows(): supplied argument is not a valid MySQL result resource
It points to line 33 which is the last line in this mysql query:
mysql_select_db($database_vusradmin, $vusradmin);
$query = "SELECT user_id FROM ext_users";
$result = mysql_query($query);
$totalrecords = mysql_num_rows($result);
When I view the output of my error handling script I see two different connection variables each of which has been assigned a Resource id. I also see the username and password for each connection, and they are both correct. The error on line 33 seems to indicate that the query is not producing anything, which is borne out by the error message content:
[query] => SELECT user_id FROM ext_users
[result] =>
the result is empty.
To sum up, this works perfectly on my development server, but not on my production server. The error report shows both connections being made, each having a correct username and password. A separate Resource id has been assigned to each connection, but the first DB interaction with ext_users, the Select query, fails. I have checked, double-checked, and triple checked the values of the connection script with those on the live server, and they are all correct. I have a DB user vuseradm with Select, Insert, Update, and Delete privileges on the live server. What could this be? Is there a server setting that might be affecting this?
Any helpful suggestions would be most welcome.
Resolved
I recreated both the connection script and the mysql_select_db and query, and now it is working just fine. I still don't see where the difference is between the original and the new version, but hey, who cares, it's working fine now.
Sorry for troubling everyone with this trivial, yet frustrating matter. Many thanks to all who replied.
