Connect to SQL Database on Remote Server

[mekohler]

Hi, I'm new to using SQL with PHP. I'm trying to connect to a database located on a remote server. I'm working on a Mac book and using MAMP I've connected to a SQL database using the following command:

mysql_connect ('localhost:1558', 'root', 'root'))

My question is if I have I a SQL database provided by my web service provider, shouldn't I be able to replace 'localhost:1558' with the appropriate ip address so that I am able to connect to the remote data, i.e.
xxx.xxx.xxx.xx:1558 and then replace the 2 roots with my username and password.
Thanks,
Michael

 

[jpadie]

yup. you should.

but many ISP's do not allow remote access to their database servers. only local IP's are allowed.

check connectivity by telnetting to the IP address and port and see whether you get a response.

if this works and you still cannot access the database then it may be a permissions issue (which, again, could be a deliberate security constraint imposed by the host).

 

[mekohler]

Thanks for the response. The project that I'm working on is for a community resource directory. What the group wants to be able to do is have various community groups stored in a SQL database, fields for name, phone number etc. Using forms they want the ability to update data stored in the table, change phone numbers etc. If I create the site locally, will I then be able to upload it to the web and have the forms access the correct database?
How do you telnet to the IP address on a mac?
Thank you, Michael

 

[DonQuichote]

If MySQL (and a compatible version of it) is running at the provider's server, there must be a way to execute SQL on it.

There are a few things that can be tried:

If you have SSH access, you can forward a port (also known as SSH tunnel) on that machine to your own. This will encrypt everything sent to that port, and will look like a local login for the server. Some database frontends (for instance, the commercial version of SQLyog) have support for such connection built-in.

- Your provider may have phpMyAdmin installed. Not the nicest solution, but it works.

If you have shell access, you can probably upload an SQL file and use the mysql command-line client.

Good luck!

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[mekohler]

Thanks again. I've been into the control panel of my website and I found where I can change permissions. Do you know which of these should be turned on?
public
aspnet_Membership_FullAccess
aspnet_Membership_BasicAccess
aspnet_Membership_ReportingAccess
aspnet_Profile_FullAccess
aspnet_Profile_BasicAccess
aspnet_Profile_ReportingAccess
aspnet_Roles_FullAccess
aspnet_Roles_BasicAccess
aspnet_Roles_ReportingAccess
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter
Sorry about the size of the list.

 

[jpadie]

i would not have thought any of these items are relevant. but your ISP will tell you.

you first need to check whether you can connect. then whether you can authenticate to the database. telnet to test the first.

and the answer to your second question is: of course. develop locally with a local database. then when you're ready to deploy, upload the website to your servers and change the database connection parameters to point to your remote database (i.e. the database that is local to the remote site). you will have to make sure that the tables exist in both places and that the schema is identical. phpmyadmin can help you with this, or you can just build the tables you need as part of an installation routine.