I am fairly new to using PHP and MySQL. As I mentioned in some prior posts, I first found how to connect to MySQL to my hosted MySQL database via dot net and also using connections tested by Microsoft’s data link wizard.When I proved I could connect using the credentials set up with my hosting provider, I started trying out PhpMyAdmin and MySQLWorkbench both of which, after some figuring, I was able to connect with. Personally I find myself enjoying workbench the most, however it is good to know that I can use one or the other.
Early on, when I was just getting use to working with MySQL I was relying on my database connection with the MySQL database hosted on the web server of my hosting company. Then for some reason, I lost connection. It appeared to be related to the cable service but I cannot be sure of the cause. In any case, when I finally got service back, I mad a database creation script and used it to create a local mySQL database to continue development in case of any further issues.
In terms of general workflow, it is best, in my opinion to create and test the routines locally first, before uploading them section by section onto the web server. It tends to slow you down but you receive the benefit of having more robust code and a ready-made backup of your code stored locally.
Recently encountered PHP MySQL connection errors
I’ve encountered some different connection issues that perhaps some others have also encountered that I wish to share. I recently learned that one must establish the database connection using something like the following php syntaxt:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die (‘Could not connect to MySQL: ‘ . mysqli_connect_error() );
However when I have tried this in my own situation I found that I encountered and error like this
Could not connect to MySQL: Access denied for user ‘username’@’host’ to database ‘databasename’.
Although the correct way to establish a connection includes using a DB_NAME parameter, for some reason in my own testing I was able to connect using just the following parameters which did not include databasename:
@mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD)
However if you change it and you get another error it could be because of another potential issue related to the value of the DB_HOST value. You may receive this error after you begin testing your php connection script that worked locally but now it produces an error when you load it on the web host’s server.
Could not connect to MySQL: Access denied for user ‘username’@’someserver.com’ (using password: YES)
In my own situation the error seems to be generated by using values like this:
DEFINE (‘DB_HOST’, ‘someserver.com’);
DEFINE (‘DB_HOST’, ‘some ip address’);
What fixed the error for me was to instead change the db host to “localhost“, as in:
DEFINE (‘DB_HOST’, ‘localhost’);
Are you still having issues connecting?
Your web hosting company may need to add your ip address to a ‘white list’ to allow you to access the mySQL database. Frequently in your ‘control panel’ of your web hosting account you can set this on your own. Additionally, try using an ip address in your specification of the hosting server for your connection details.
If you are having trouble connecting via a particular client, try another client to see if you can connect via some other client connection method. When I first had issues I confirmed that the hosting company had allowed connections by using dot net to connect, then I was able to debug it from there. However, If you cannot connect with another client , then go and check with your hosting provider.