PHP database connection

Question

I need to connect to a remote mysql database in PHP.

Specifically I have this as a constant: define("DATABASE_SERVER", "localhost"); ...

$db = @mysql_connect(DB_SERVER, DB_USER, DB_PASS);

I want to copy the site to my local machine but still connect to the main database.

Can I do this? if so, how? All I have is FTP access.

Answer 1

I would guess that your database server is refusing remote connections. If you have cPanel there's a section called something like "Remote Database Access" where you can enable them to certain IP ranges.

Answer 2

You need the IP/URL of the DB and put that instead of localhost.
You need to make sure the DB configuration allows remote connections/outside the local network.
Once you get the IP of the DB (should be given by the support team, if it is being supported), you will know.

If all you need to do is copy the data and build it locally on your machine, so you don't destroy live data, use the export tab in PHPmyadmin. I assume you don't have too much data.
PHPmyadmin->select the DB (from the left frame)->press the export TAB (top, right frame).
Should be clear from there.
You can export it as SQL and run this SQL on your local machine.

Answer 3

I suggest that you export the database in question, and import it locally. This way, you won't destroy live data if you mess up.

Many, if not most, hosting providers also provide a DB interface like phpMyAdmin. You can export the database from there.

Answer 4

The simplest approach is to have DATABASE_SERVER be defined as database (or something equally meaningful) and put an entry in your hosts file. On your local machine this can point to the remote address, on the server this will point to 127.0.0.1.

define('DATABASE_SERVER', database);

On windows your sites file is:

c:\windows\system32\drivers\etc\hosts

with:

database <remote_ip>

Linux:

/etc/hosts

with:

database 127.0.0.1

Side note: is your remote DB a production DB? If so you shouldn't really be using the remote DB for development work, unless you're using a separate dev DB - even then I wouldn't run the risk of rogue dev queries eating up the remote DB resources.

Update: Doh, missed you're limited to FTP access.

Answer 5

+1 gnud. You shouldn't use the production database for development work, even if you could configure it to listen for external access on a public internet interface(*). One slip of the mouse and you're scribbling over important live data. Instead you should be working on a standalone development server of your own, from which you can push the code changes to the live server when you they're working and tested.

(*: Which is in itself a very bad idea: if the passwords are too weak or you're running an old version of MySQL with exploits available you are going to get hacked by automated probes very quickly.)

You'd normally export the database from the shell using the mysqldump dbname > dbdump.sql command, and import it on your local server by piping it into mysql dbname < dbdump.sql. There might also be a MySQL admin web interface installed on the server you could use. But if all you've got is FTP, you haven't really been given the tools you need to do development work. See if you can get an empty or dummy database to test the site with.

(What's more, FTP is a nasty business... really in this century you should be using SFTP.)