local and remote database connection

[teedoff]

Hi new to php and I'm trying to learn how to set up connections to the database on my remote server. I'm using dreamweaver to create recordsets, then dispaying the results. Dreamweaver creates a connection file(I named student.php) automatically based on your database connections and login info. However, this poses a problem when I upload my files to a remote server. Of course the connection file has the local db login info, NOT the remote db server info.

I created a copy of the student.php and changed the db information and saved this file to my remote server, but it seems to not work for me quite right. This was how our instructor suggested and demoed succesfully to us.

My question is how is this normally handled by experienced php coders? I would love to find a better way thabn having to manage two files of the same name while keeping them separate on local and remote servers respectively. Thanks for any help and please remember, I'm very new to php...lol so no suggestions TOOOO hard...lol Thanks!

[pritaeas]

In most everyday developing situations the database server name is 'localhost'. If you then make sure your local and remote DB both use the same username, password and database name, then there is no need to change your connection file.

[teedoff]

Hey pritaeas, so databases on remote servers should normally be named localhost as well? The remote db we're using is named something like "MySQLC6.webcontrolcenter.com" Wow that seems strange..lol I didnt know that. So I guess as it is, I do need to create two separate connection files, one for localhost and one for the above mentioned db server, and figure out why thats not working.

Also I had forgot I posted something similar to this the other day. lol Sorry about that. Thanks for your help!

[pritaeas]

It can be localhost if the mysql server is located on the same machine as the webservice. In your case (as I see now) it might be that this is a dedicated mysql server, not on the same machine as the webservice you're using.

[teedoff]

Yes that is the case....so thats why i was wondering how something like that would normally be handled. I know our instructor said that was the only way he found to do this. But there are always other, maybe better ways to do anything. I will say, this is a new class offered at our local community college, and the instructor, who is very smart, comes from a coldfusion background and has just learned php recently.

[pritaeas]

Well, if it is a dedicated server and you cannot connect from one location, but you can from another, then it may be a firewall issue. What kind of error do you get when you try to connect ?

[teedoff]

Oh no..let me explain again. The problem is with the connection setup. When I created a query in dreamweaver, I filled out the dialogue box with db host name, user name, password, and named the connection "student".

Now, since putting the query page on the remote server, along with the student.php connection file, then remotely all the connection specifics are wrong. Here is the connection file I'm talking about called student.php:

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_student = "localhost";
$database_student = "student";
$username_student = "root";
$password_student = "somePassword";
$student = mysql_pconnect($hostname_student, $username_student, $password_student) or trigger_error(mysql_error(),E_USER_ERROR); 
?>

This works locally. Now since all that is different on the remote server the school uses, our instructor had us create another file student.php to be placed on the remote server but change the hostname, username, and password to reflect the remote info. Now that should work, it does for him, but it doesnt seem to work for me. I'm thinking maybe its because dreamweaver automatically uploads related files for any page? maybe? but I thought I turned that preference off. Either way, the jist of it is I have to have two database connections named the same, but with different login in info. One placed on the remote only, the other saved locally on my machine only.

[silviuks]

Maybe your server do not accept remote connection.

[pritaeas]

Ah okay. You could add a check in the connection file to see what IP address the machine has, and depending on that set the connection you need. Then you can use the same file. A similar method can be to check for a specific file.

[teedoff]

hmm lol ok that makes sense. Its above my head at this point, so do you know where I could find a tutorial or some examples to do this?

[pritaeas]

How about:

if (file_exists('remote-settings.php')) {
  $hostname_student = "MySQLC6.webcontrolcenter.com";
  $database_student = "someDatabase";
  $username_student = "someUsername";
  $password_student = "somePassword";
}
else {
  $hostname_student = "localhost";
  $database_student = "student";
  $username_student = "root";
  $password_student = "somePassword";
}

Put an empty file called remote-settings.php in the same folder as your connection file on the remote server (but not on your local machine).

[teedoff]

ok and I put the above code in the head section of every query page? before the actual query I presume.

[pritaeas]

If this is the code in the student.php file you mentioned, you can just include it where you need it.

[teedoff]

ok thanks! I'll try it out when I get off work! thanks so much for the help!

[teedoff]

ok Ive tried several times and ways to get it working lol. Its still working locally, but not remotely. I put the code above in my student.php file which is here:

<?php 
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"

if (file_exists('remote_student.php')) {
  $hostname_student = "MySQLC6.webcontrolcenter.com";
  $database_student = "student";
  $username_student = "gtcc";
  $password_student = "****";
}
else {
  $hostname_student = "localhost";
  $database_student = "mysql";
  $username_student = "root";
  $password_student = "******";
  $student = mysql_pconnect($hostname_student, $username_student, $password_student) or trigger_error(mysql_error(),E_USER_ERROR);
}

?>

Then i put a blank file on my remote server in the same folder as the student.php and named it remote_student.php. Then here is my actual query on the query1.php:

<?php require_once('Connections/student.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_student, $student);
$query_Recordset1 = "SELECT a.Model, a.Style, a.Color, a.`Size`, a.QuantityInStock  
FROM tblswimwear a
ORDER BY a.Color, a.`Size`DESC";
$Recordset1 = mysql_query($query_Recordset1, $student) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

When I run the query remotely, I dont get any errors, just a blank page.

[pritaeas]

True, the connect is in the else part of the if. It should be the last line, after the closing curly bracket.

[teedoff]

hmm ok here is what I have now with the same results.

<?php 
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"

if (file_exists('remote_student.php')) {
  $hostname_student = "MySQLC6.webcontrolcenter.com";
  $database_student = "student";
  $username_student = "gtcc";
  $password_student = "*****";
}
else {
  $hostname_student = "localhost";
  $database_student = "mysql";
  $username_student = "root";
  $password_student = "*****";
}
  $student = mysql_pconnect($hostname_student, $username_student, $password_student) or trigger_error(mysql_error(),E_USER_ERROR);

?>

Sorry to be such a pain and newbie! lol. I just dont know enough about php and db connections yet to analyze whats wrong.

[pritaeas]

Still no error message ?

[teedoff]

no error message, the page just loads blank as before. I will mention that I'm using a drop down list to select the queries, I have 8, but that shouldnt matter as it is working locally.

[pritaeas]

Do you check for errors coming from the select_db or the queries the same way you do when connecting ?

[teedoff]

you mean like here?

mysql_select_db($database_student, $student);$query_Recordset1 = "SELECT a.Model, a.Style, a.Color, a.`Size`, a.QuantityInStock  
FROM tblswimwear a
ORDER BY a.Color, a.`Size`DESC";
$Recordset1 = mysql_query($query_Recordset1, $student) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);$totalRows_Recordset1 = mysql_num_rows($Recordset1);

[pritaeas]

If you do:

mysql_select_db($database_student, $student) or die(mysql_error());

still no errors ?

[teedoff]

ok I added that here like this:

mysql_select_db($database_student, $student) or die(mysql_error());
$query_Recordset1 = "SELECT a.Model, a.Style, a.Color, a.`Size`, a.QuantityInStock  
FROM tblswimwear a
ORDER BY a.Color, a.`Size`DESC";
$Recordset1 = mysql_query($query_Recordset1, $student) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

made sure I put the new file to the remote server, tested it locally and it worked...then opened it remotely and still blank page with no error msgs..lol

[pritaeas]

Can't think of anything else right now, although it has to be something obvious.

[teedoff]

lol It has to be. It's driving me crazy though..lol Its always the simple things that pose the problems.

[teedoff]

Ok well I have it working now. The student.php was being uploaded to the remote server, or vice versa each time I changed either the local db info, or remote db login info. I had thought that might be the case, but couldn't find how to change that in DW preferences until yesterday.

Now I still want to know why the code you gave me wouldnt work, so I may keep working in that. I think I like that way (the if statement) better than having to worry about and manage two different connection files with the same name. I mean its fairly easy to manage, just with cloaking the folder those files are in, but still can be confusing. Thanks again!