Connecting to remote MySQL database

@jburattiMar 27.2010

bear with me on this one a little… I gave myself a crash course in web design several months ago, and learned a lot, but haven’t used it since and also forgot most of what I taught myself and what is possible. What I want to do is basically connect my Microsoft Access database (located on my local machine/computer) to my MySQL database that is hosted on my website. I did research, and connecting MS Access and MySQL is possible, but my problem is with my web hosting provider. I use Yahoo as the host and apparently they do not allow remote connections to my MySQL database. I did more research and figured out there are workarounds to actually allow those remote connections.

I found this website/software called RazorSQL that in short, you upload a .PHP page to the server, which connects to the MYSQL database, and then the local RazorSQL software connects to that .php page, hence “linking” to the database, allowing remote connections to that datbase. This is basically what I am loking for, but the problem is I can only use their software to connect to the MySQL database and not MS Access as I was hoping to.

The way I figured out how to connect MS Access to a MySQL database is by a driver provided from the MySQL website. It is a basic simple connection form asking for server name, user, pass and database name. The problem with using this to connect directly to my remote MYSQL database is that Yahoo doesn’t provide a server name to my MySQL database only “MySQL” which I’m assuming is the server name “AFTER” you are already connected to the main server or already on the website.

What I was thinking is (is it even possible) to create a similar .php page that opens my MySQL database and then use the MySQL driver to connect to that .php page in the same fashion that RazorSQL does it like explained above. First, without getting into details, would this be possible? And if so, what would be involved/how hard would it be to create this, keeping in mind my limited knowledge of php. Or, I will supply the php code that RazorSQL uses (which seems pretty simple, but I really dont know how to understand it) and maybe someone can assist me in simple modifications to allow it to work with the MySQL driver. Thanks in advance for your help and sugestions on this one.

to post a comment

PHP

3 Comments(s) _↴

@Jarrod1937Mar 28.2010 — #So your local machine is acting as a server to? Do you have a static ip where you're hosting the local access database? I'm assuming you're referring to mysql's odbc driver? What is your end goal with this setup?

Generally the mysql odbc driver connects to the remote database by an ip address of the database server, a username and password and the name of the database to connect to. This also requires that you're on the mysql remote access list (assuming they have the server setup correctly). The thing to understand is that this does not run through php, it is not the middleman, the driver connects straight to the database server and mysql. Reading RazorSQL's page it, though i could be mistaken, looks like it can use php as a bridge to the server to the desktop application. However, this is more than likely a php page whose code is loaded with functions used by the desktop app but translated to php form, for which the application sends requests to, sending the appropriate data when needed. Think of it as the desktop application is acting as a browser sending http get requests to something similar to phpmyadmin. From what i understand it is not bridging direct access to the local database like a remote connection would.

@jburattiauthorMar 28.2010 — #So your local machine is acting as a server to? Do you have a static ip where you're hosting the local access database? I'm assuming you're referring to mysql's odbc driver? What is your end goal with this setup? [/quote]

Thanks for your response... to answer these questions, My local machine is not quite acting as a server too. It is a simple Access database located on one machine in my office. We do not have a server type of network in our office, just 3 PC's simple networked through windows. I do not have a static IP where my Access DB is located, (can easily get one if need be though), yes I am referring to the mysql odbc driver, and my goal is very simple. Basically I just want to create a quote/feedback and possibly an online order system (not an order like a shopping cart) for users of my website to fill out a form and have that data submitted straight to my local Access database. From what I remember when I taught myself the little I do know, this traditionally could be done by setting it up where the form is submitted straight to an email address, or creating a complex web based application to retrieve the data, but neither of these options are ideal for my situation. The person who will be retrieving the data is not reliable on constantly checking email, so that option is out, the web based system is too complex for me to create, and also, I would like to try to eliminate manually re-copying data from one system to another to reduce human error. (ie. from the online mysql db to my access db) I have created a pretty nice Access db for common tasks in our office and it would be nice if I can submit forms/user information from my website visitors straight to my Access db!

Generally the mysql odbc driver connects to the remote database by an ip address of the database server, a username and password and the name of the database to connect to. This also requires that you're on the mysql remote access list (assuming they have the server setup correctly). The thing to understand is that this does not run through php, it is not the middleman, the driver connects straight to the database server and mysql. [/quote]

Yes I understand this, and my problem is that the company that hosts my website is Yahoo and they state that you cannot connect remotely to your mysql database, only through phpmyadmin which is also hosted on their server. Yahoo does not supply an IP address for the database server, the only database server name they provide for us to use is "mysql" which is the same for every person that has a site through Yahoo. I was thinking since RazorSQL can technically work around this restriction and actually connect, I was hoping that there would be some way that it can be configured to have MS Access do the same thing rather via the mysql odbc driver or another way.

Reading RazorSQL's page it, though i could be mistaken, looks like it can use php as a bridge to the server to the desktop application. However, this is more than likely a php page whose code is loaded with functions used by the desktop app but translated to php form, for which the application sends requests to, sending the appropriate data when needed. Think of it as the desktop application is acting as a browser sending http get requests to something similar to phpmyadmin. From what i understand it is not bridging direct access to the local database like a remote connection would.[/QUOTE]

I dont completely understand "how" it works, but I figured roughly the same thing. When connecting to my mysql db from the Razorsql software, first I have to upload a simple php file to my website. Then in the Razor software, I enter the address of that php file where it asks for the server of my mysql db, then user pass and everything else the same. That php file I uploaded is not very complex at all. I dont really understand/cant read it with my knowledge, but it doesn't seem loaded with functions. I have attached the php file for people to analyze how it possible works.

So basically in short what I am trying to do is connect my MS Access database to my mysql database by using some type of workaround to the restriction of remote connections to the mysql database. I figured my original question/suggestion could of been a starting point, but if anyone has any other suggestions I am all ears.

Thanks

[upl-file uuid=8fd82e2f-1bdc-448a-bda2-48a81e233e81 size=2kB]razorsql_mysql_bridge.zip[/upl-file]

@criterion9Mar 28.2010 — #I would skip messing with the access database at all. If you are wanting to make the changes to your hosted MySQL database create an administration interface in your hosted space and use that to provide your functionality.

Alternatively you could create a script that makes a "backup" of your online or offline database (since this is the basic gist I'm getting from your description) and then updates the other database at an interval. I would still say this could cause more problems than it is worth and your best bet is to either suck it up and write a web based application or hire someone else to create it for you.

Also in #PHP _↴

Any ideas on making a plugins system?mysql_num_rows(): is not a valid MySQL result resource PHP script test challenges for a Newbie

Success!

Help @jburatti spread the word by sharing this article on Twitter...

Tweet This

Connecting to remote MySQL database

3 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Connecting to remote MySQL database

3 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

3 Comments(s) _↴

Also in #PHP _↴