mySQL database on diffrent server

[nats01282]

Hello all. im new to mySQL database so dont know very much so sorry if its a daft question.

I have a mySQL database with a company called 5quidhosting but my hosting is with heart internet.

Can i get a web page hosted with heart to access the database on 5quidhosting?

or do i need to get a mysql databse with the same company who do my hosting?

[genesisgrace]

Hi Nats,

In order to make things easier for yourself, why not move your database to your current hosting company? Personally, I would not have it any other way.

Genesisgrace

[nats01282]

Heart internet offer me better deals then 5quidhosting, except they dont offer me a database, :(

Heart internet is a better run service then 5quidhosting.

i will lose out on over 5 products that i use. because 5quidhosting dont have them,

But if i create the database is they a way to access it ? and use it
it would be great if i could

[Fbody]

Hello all. im new to mySQL database so dont know very much so sorry if its a daft question.

I have a mySQL database with a company called 5quidhosting but my hosting is with heart internet.

Can i get a web page hosted with heart to access the database on 5quidhosting?

or do i need to get a mysql databse with the same company who do my hosting?

It is possible to do with your database host and web server separate, but it's a little inefficient.

You will have to speak with your database host and get from them the web address/name and a username/password to be able to access it from an external web server. It is likely that the address you already have is not the address your PHP script(s) will need to use to be able to access it.

An example (use of phpMyAdmin assumed):
You currently use the address interface.someserver.net to access your database through the phpMyAdmin interface. The phpMyAdmin interface then connects to another server called wackoname.anotherserver.com that the database actually resides on.

To access your database from your server you will need to get the "wackoname..." address, the username and password will probably be the same as what you already have. Once you have those, you can generate a connection string for your PHP installation's mySQL module.

I find it simpler to put the connection information and a connection function in a small file on a secure part of the server. When a page needs to access my database server, I include the file and call the simplified connection function.

<?php /* somefile.php */
require_once("theconnectionfile.php");

function someFunction() {
 $conn = connectFunction();

 /* ... whatever you need to do ... */

 @mysqli_close($conn);  //terminate the connection
}

// END somefile.php FILE
?>


<?php /* theconnectionfile.php */
//based on use of "Improved MySQL" module (which I use on my server)
//the "standard" module requires you to select the actual database in a separate operation
$db_host="wackoname.anotherserver.com";
$db_user="username";
$db_pass="password";
$db_name="dbName";


/***** connect_db(): Establish connection to MySQL server *****/

function connectFunction()
{
 global $db_host, $db_user, $db_pass, $db_name;
 $link=@mysqli_connect($db_host,$db_user,$db_pass,$db_name);
 if (!$link)
  {
  die("There has been a permanent system error!<br />Please inform the site administrator.");
  }
 return $link;
}

// END theconnectionfile.php FILE
?>

[genesisgrace]

Hi Nats,

If for example you want to send information from a form on your web site (hosted by Heart) to a database that you have set up for the form data, I doubt it very much that it would work, having the database on a different server.

I am not sure if you would be happy to hear this but I would find me a hosting company that offers everything that I need. There are some good hosting companies out there.

Gracemarie

Heart internet offer me better deals then 5quidhosting, except they dont offer me a database, :(

Heart internet is a better run service then 5quidhosting.

i will lose out on over 5 products that i use. because 5quidhosting dont have them,

But if i create the database is they a way to access it ? and use it
it would be great if i could

[Fbody]

It should work as long as nats has the right address(es).

When you have your database and web servers with the same host, they are still likely to be on different pieces of equipment even though they have the same domain. Is that situation really any different than the one in question?

There probably will be an (hopefully slight) efficiency issue because of the totally separate server locations, but it should work.

[genesisgrace]

Hi Nats,

I have another suggestion. You could do a simple test by creating a web page with the database connection such as:

mysql_connect("localhost", "your username", "your password") or die(mysql_error());
mysql_select_db("database name") or die(mysql_error());

Then you could use mysql_query to retrieve information from the database, for example:

$result = mysql_query("SELECT * FROM table name") 
or die(mysql_error());
echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Birthday</th> <th>Username</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>"; 
    echo $row['name'];
    echo "</td><td>"; 
    echo $row['birthday'];
    echo "</td><td>"; 
    echo $row['username'];
    echo "</td></tr>"; 
} 


echo "</table>";

The results should show on your web page. If it does not, then the two different servers are not working for you.

Genesisgrace

[Fbody]

Hi Nats,

I have another suggestion. You could do a simple test by creating a web page with the database connection such as:
mysql_connect("localhost", "your username", "your password") or die(mysql_error());
mysql_select_db("database name") or die(mysql_error());

Genesisgrace

This definitely will not work. The term "localhost" refers to the same piece of equipment that the script is running on. We already know that the database will not be on the same piece of equipment as PHP.

To access the database on another piece of equipment, you will need to replace "localhost" with the appropriate address i.e. "wackoname.anotherserver.com". So the connection string would become:
mysql_connect("wackoname.anotherserver.com", "your username", "your password") or die(mysql_error());

nats will have to get the actual connection information from 5quidhosting

[genesisgrace]

I agree that localhost would have to be replaced with the appropriate address, but this is just an example for him to test.

This definitely will not work. The term "localhost" refers to the same piece of equipment that the script is running on. We already know that the database will not be on the same server as PHP.

To access the database on another piece of equipment, you will need to replace "localhost" with the appropriate address i.e. "wackoname.anotherserver.com". So the connection string would become:
mysql_connect("wackoname.anotherserver.com", "your username", "your password") or die(mysql_error());

[nats01282]

i dont know if this is any good but i found this

Remote Database

Access Hosts

You can allow external web servers to access your MySQL databases by adding their domain name to the list of hosts that are able to access databases on your web site.

Add Access Host
Host (% wildcard is allowed):

do i just put "www.mydomain.com"

But then how do i connect to it...?

[diafol]

Sounds like you've got something like cPanel? See screenshot.

You can probably get your heart server address from inserting something like:

<?php echo "Server address: " . $_SERVER['SERVER_ADDR'];?>

into one of your pages.

Now take the address and place it into your cpanel.

I'm no expert on this - I'm assuming a bit here.

[cwarn23]

Sounds like you've got something like cPanel? See screenshot.

You can probably get your heart server address from inserting something like:

<?php echo "Server address: " . $_SERVER['SERVER_ADDR'];?>

into one of your pages.

Now take the address and place it into your cpanel.

I'm no expert on this - I'm assuming a bit here.

I Agree totally. In that picture shows where you enter in the remote host(s). There may be more than one in this situation depending on the isp setup. I have done this setup myself and is very simple. First in the site with mysql like ardave said you will need to enable remote hosts. The first remote host will be %.website_without_database.com and notice it uses the subdomain %. Remember to replace the domain with your real domain. Then in the below script replace the host (first parameter) with the website that contains your database. Then enter your username and password. Then run the script and follow the next step.

<?php
mysql_connect('website_with_mysql.com','username','password') or die(mysql_error());

For example, if I were doing a remote host to my website it would look something like this (password is different though)

<?php
mysql_connect('cwarn23.net','root','abc987zyx') or die(mysql_error());

Now you will probably see an error. The error will say that it cannot access the database due to some security violation. To solve this copy and past the host name in the message into the box ardave pointed out. Now you should have 2 remote hosts set in your cpanel. Then you may need to run the above script another 1 or 2 times to get all of the hosts to enter into your cpanel remote hosts. After that you should see a blank page which means the details are correct and you can copy and past the mysql connect function into your project.

If you have any troubles can you please mention the two main domains you have (not addon domains) and which one has the database as well as any errors you get.

[nats01282]

ok now i have got in touch with 5quid hosting and they have just told me

To connect remotely, you must add the IP address of the connection you're using to the 'Remote MySQL' icon on your control panel and let us know what that IP address is so that we can adjust the firewall on the server in question.

Now i have tried everything you guys have asked me to do and when i access my webpage it says

Host 'my_ip_address' is not allowed to connect to this MySQL server

does that mean that it is working and that im waiting on 5quidhosting to adjust there firewall to my ip??

[nats01282]

i have copyied and pasted this from my phpinfo, im sure this allows me to have mysql but im not sure

mssql
MSSQL Support enabled
Active Persistent Links 0
Active Links 0
Library version FreeTDS

Directive Local Value Master Value
mssql.allow_persistent On On
mssql.batchsize 0 0
mssql.charset no value no value
mssql.compatability_mode Off Off
mssql.connect_timeout 5 5
mssql.datetimeconvert On On
mssql.max_links Unlimited Unlimited
mssql.max_persistent Unlimited Unlimited
mssql.max_procs Unlimited Unlimited
mssql.min_error_severity 10 10
mssql.min_message_severity 10 10
mssql.secure_connection Off Off
mssql.textlimit Server default Server default
mssql.textsize Server default Server default
mssql.timeout 60 60

mysql
MySQL Support enabled
Active Persistent Links 0
Active Links 0
Client API version 5.0.45
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib -lmysqlclient

Directive Local Value Master Value
mysql.allow_persistent On On
mysql.connect_timeout 60 60
mysql.default_host no value no value
mysql.default_password no value no value
mysql.default_port no value no value
mysql.default_socket no value no value
mysql.default_user no value no value
mysql.max_links Unlimited Unlimited
mysql.max_persistent Unlimited Unlimited
mysql.trace_mode Off Off

[Fbody]

The top half of that is related to Microsoft SQL Server, but the bottom is the MySQL module information for your PHP server. That means you can use MySQL, but you will have to get your addresses sorted out with 5quid.

[nats01282]

5quid have told me they have unblocked my ip address.

when i go to my cpanel and go to remote mysql
I have
1. my web address
2. my full IP
3. My Ip with %

now i have wriiten a php file that

<?php
mysql_connect('my ip address', 'databse username', 'databse password') or die(mysql_error());

after i upload the file and test it i get the message

Host 'my_ip_address' is not allowed to connect to this MySQL server

is it correct putting my ip address in the php code ?
It dont seem right to me
What goes in there and were do i find it lol

[nats01282]

:-o

i have done it !!!!!!

:) thank you every one who helped me