connection script

working but i can't figure out the how of it

[nyteshade]

I'm still learning http stuff and have re-focused on PHP and the MySQL; while cleaning up some code I kinda thought my connection script smelled funny.

if ($_SERVER['SERVER_NAME']=="localhost" 
 OR $_SERVER['SERVER_NAME']=="192.168.1.1" 
 OR $_SERVER['SERVER_NAME']=="192.168.1.2"){

 //local host
 $db_hostname = "localhost";
 $db_username = "my_local_username";
 $db_password = "my_local_password";
 $db_database = "local_visitor";
 $mysqli = new mysqli($db_hostname, $db_username, $db_password, $db_database);

} else {

 //remote host
 $db_hostname = "localhost";
 $db_username = "my_remote_username";
 $db_password = "my_remote_password";
 $db_database ="remote_visitor";
 $mysqli = new mysqli($db_hostname, $db_username, $db_password, $db_database);
}

I structured this to be an includes so that it would work locally at home and remotely at my hosting service.

1) I can not figure out how the if control works but it does and this script has never failed! I would think that removing

($_SERVER['SERVER_NAME']=="localhost"

would work since the ip addresses are given, but it will fail when removed AND not work if the two ip addresses are removed? Isn't the if control satisfied when the 1st OR operand evaluates to TRUE? But then it would never connect remotely?!?!

2) Why is 'localhost' the remote hostname? Is it because MySQL server is seeing all it's children databases under the name 'localhost'? Honestly, I can't remember how I figured this out, I must have been guessing back then, but now I know it's too dangerous to guess about making a good connection; so, I need to know.

Usually by the time I get this much typed in I've figured it out but I've been staring at this for awhile now and it's given me a headache... hold on, now I just tried this:

if ($_SERVER['SERVER_NAME']=="localhost")
{
//local host
$db_hostname = "localhost";
$db_username = "my_local_username";
$db_password = "my_local_password";
$db_database = "local_visitor";
$mysqli = new mysqli($db_hostname, $db_username, $db_password, $db_database);
}
else
{
//remote host
$db_hostname = "localhost";
$db_username = "my_remote_username";
$db_password = "my_remote_password";
$db_database ="remote_visitor";
$mysqli = new mysqli($db_hostname, $db_username, $db_password, $db_database);
}

...and it works both locally and remotely with 'localhost' as the value for both $db_hostname vars? When I put this in my conn.inc file:

printf('<br/><br/>'.$_SERVER['SERVER_NAME']);

...then locally it says 'localhost' and remotely it says 'www.mydomainname.com'. Arrgh! What's going on? I don't mind digging in and experimenting but I'd only be guessing again... hmm, so when the client requests a www.mydomainname.com page then the hosting service intercepts the 'localhost' symbol? and gives it the value of www.mydomainname.com? Just a guess. A real explanation most appreciated, thanks all.

[incrediBILL]

Because $_SERVER['SERVER_NAME'] is a is predefined variable which takes on the value of "Host:" from the HTTP header sent by the browser.

[nyteshade]

incrediBill: yes, I understand that... what I meant to do was:

printf('<br/><br/>'.$db_hostname);

...at the end of my login.php file. It returns 'localhost' both locally and remotely after the if statement (sorry about that, was tired and flustered).

Ahah! I think I've got it now. So the second version works because when local SERVER_NAME equals 'localhost'; and when remote it equals 'www.mydomainname.com' (I knew that! really I did!) what was confusing me was setting $db_hostname = 'localhost' in the else condition and it works. If I set it to www.mydomainname.com then it fails. So regardless of local or remote, $db_hostname always equals 'localhost'?

[rocknbil]

Your SERVER_NAME environment variable has nothing at all to do with your mySQL server name. Nothing. The fact that it populates it locally is just confusing you. :-)

When you eliminate setting a hostname for a mySQL connection, you're right. It defaults to localhost. But it's a different localhost, it's a mysol localhost, not a web server localhost. So being "local" to your domain name is often perfectly legitimate. on a remote host.

For many web servers - especially VDS/VPS or dedicated - this will be correct, or sometimes it's like so: mysql.example.com (or similar.) Shared hosting environments usually have a remote mysql server, like mysql.youraccountname342345354.com or something.

The overall advantage: it gives you more control over connectionsand an added layer of security. You can control who connects to the mysql server and from where. By default, localhost is always allowed so you shouldn't have to make any special permissions in mysql to run it from your site. But if you wanted to say, connect to your web server's database from your local computer, you'd need to first find out what the "real" name of your mysql server is (guaranteed it's not "localhost") and then assign permissions for your remote user to connect to it.

grant all privileges on database_name.* to 'user'@'123.456.789.00' identified by 'user-password';

Or if this user can connect from anywhere,

grant all privileges on database_name.* to 'user'@'%' identified by 'user-password';

A bit more dangerous but if you're unsure of the IP from which your local computer might be connecting, might be the only way.

[nyteshade]

Your SERVER_NAME environment variable has nothing at all to do with your mySQL server name. Nothing. The fact that it populates it locally is just confusing you. :-)

Something went click in my head, got it! And I see why the hosting service uniquely prefixes subscriber db names with a piece of our domain names. Ok, I'm good for now, thx!