Leaderboard

The other day, I wanted to use the mysqli_connect function to connect to a DB on an external host that was not 'localhost'. Specifically, I was using XAMPP on a computer and was trying to connect to to a DB on a separate domain that I own, which is on a server maintained by my hosting company. I have (and know) all the necessary credentials for the DB, but I kept getting errors when trying to connect. I used the same user name, password and DB name as when its 'localhost', by for the host argument, I tried several combinations like domain-name.com, etc., but nothing worked. Does anyone have any experience with this sort of thing and know how to connect to DBs on other hosts? Thanks.

It's not all the privileges you need to grant but rather the ability to connect from other hosts, not localhost. Ideally, the connection script is on a fixed IP address and you can grant permissions to dbUser@'ACTUAL IP ADDRESS'. You have to make sure you do that first. The external connections/ports/etc. is a secondary, less common issue.

I hear you! Larry's the guru! He's the mastermind genius! You are a brainiac, too, HartleySan! Thanks again for the wonderful assistance! You are by no means a random dude, HartleySan; moreover, you are incredibly kind and extremely helpful--brilliant even--with a wonderful future in Web development! Thanks again!

Larry, maybe, but definitely not me. In fact, Larry corrects me all the time. I'm just a random dude hoping to work as a Web developer someday. Anyway, good luck.

I think you might be misunderstanding things a bit. To start with, it doesn't really matter where the actual DB files are stored on your localhost because you would never need to manipulate them directly. (If you really are curious though, they're in the xampp\mysql\data\ folder in Windows.) Instead, you should always use some sort of interface application, like phpMyAdmin, to manage and edit your DBs. By using phpMyAdmin, you can also easily import and export databases. In terms of the Web root folder, the one thing that you should probably place outside of it is the PHP file that calls the mysqli_connect function to connect to the DBs. Larry talks a lot about this in his book, so if you follow his advice, you should be fine. I've migrated from my localhost to a Web hosting server many times, and in terms of the DB, things are pretty simple. All you have to do is export the DB from your localhost and then import it from the interface your Web hosting company provides. More specifically, from phpMyAdmin on your localhost, there is an Export tab you can use. Simply select the DB or tables you want to export, and then click the Export button. By default, the exported file will be a .sql file, which should be fine. To import the database, you first need to access the version of phpMyAdmin your hosting service provides for you. The tricky thing is that a lot of hosting services block the Import tab in phpMyAdmin, but luckily, there's an easy workaround. .sql files can be opened in text editors. If you open a .sql file in a text file (which might take a second if the DB is big), you'll see that all it is is a bunch of INSERT statements (and a few other statements) for recreating the DB from scratch. As such, all you have to do is copy all the text in the .sql file, click on the SQL tab in phpMyAdmin, paste all of the SQL code in the text area, hit OK, wait a second, and BOOM! you have your entire DB on your hosting service. That's about it. Let us know if you have any other questions.

Hello, friends. Sorry to finish this post with some very basic questions, but I am going to be developing on localhost and then transferring over to a server maintained by a hosting company and had some elementary questions. First,please kindly note that I am wondering where I would place my MySQL databases on localhost to ensure that they are connected with the Web root folder. I'm not sure if I would save them in a specific folder or how to do so, if at all possible. As a matter of fact, I'm not even sure what folder the databases get saved to on my localhost in the first place. Second, I'm wanting to separate my databases as I intend on having multiple Web sites (I have one already and plan on building several more). In what directory could I create a subfolder? How would I transfer this subfolder along with the Web root to the hosting company's server? I know these are probably the most basic questions imaginable to present, and I apologize sincerely for this. Thank you for any and all feedback, my friends, and I wish you all a great day!

You're welcome! Thanks for all the help you've provided to others over the years!

Okay. Good luck and let us know if you need any other help. As a suggestion, what many people do in this situation is create a service on your hosted site that will provide the necessary database access without directly connecting to MySQL remotely.

Originally, the user was using to connect to the DB had only the SELECT and UPDATE privileges, but I temporarily changed it so that the user had all privileges. Even then though, I had no luck in establishing a connection. Also, I appreciate your techie experts advice, but I don't know exactly what they mean by, "make sure that the MySQL server can accept external connections and that it is bound to the correct network interface." Could you possibly provide some more details about how to translate that into actions? Thanks. Also, it should be reiterated that I'm using a MySQL server provided by my hosting company, so I have very limited control over various admin operations.

I get the following error: Warning: mysqli_connect() [function.mysqli-connect]: (28000/1045): Access denied for user 'xxx'@'xxx' (using password: YES) in C:\xampp\htdocs\xxx on line 3 Obviously, I have omitted the sensitive data with xxx, but you get the point. Also, I tried granting all privileges to the user, but I still get the same error.

What error are you getting? Have you tried GRANT ALL PRIVILEGES ON dbName.* TO dbUser @'%' IDENTIFIED BY 'dbPassword';

Ugh. I can see what you mean. There's no way to do this as far as I know. Sorry!

Ah, very clever (and something I never considered). Thanks a lot for that great idea, Larry!

Thanks, Larry. That's probably what I needed. Do you by chance know the name of and/or the location of the connection script in XAMPP? I assume that for my real server, I will have to contact my hosting service about this, yeah? Edit: Larry, nix that comment about XAMPP. I don't know what I was thinking. I wouldn't want to do that, even if I could. More than anything, I just want to be able to access a DB I've created on the server controlled by my hosting service.

Here's what my goto 'techie' expert said "the MySQL server has to be configured to accept external connections, that is bind to the correct network interface. The firewall may also need to be configured to accept incoming connections on the appropriate port". I only had to use the GRANT ALL PRIVILEGES command before issuing the mysqli_connect so I wouldn't know what to do with the above but maybe it will be of some help to you?