How to Connect to a MySQL Database from PHP
To build a MySQL database-driven web application in PHP, the very first thing you need to do is connect to a MySQL database. With only 6 lines of code, you can easily kick start your dynamic website development.
Code snippet 1: northwind-categories.php
As you see above, the code is extremely simple.
Display categories table
Next, we are going to select some data from our Northwind database and display the data on a web page.
Code snippet 2: northwind-categories.php
Now create a new PHP page and name it as northwind-categories.php. Copy and paste the above two code snippets into it. View the page in your web browser. If you can see the categories table displayed as shown below, you have successfully connected to MySQL and selected data from the table.
northwind-categories.php displayed as:
Two common problems when connecting to MySQL Server
Problem #1: Only a blank page shows
When you view the page from web browser and a blank page is displayed, this is because there are errors that are caused by either PHP or MySQL but your PHP setup does not allow errors to be displayed. To display all errors, add the following line to the top of your PHP page.
ini_set is a PHP function that re-sets a PHP configuration option on the fly. The page will only keep this new configuration value during the page's execution, and will be restored to the default value when the page is finished loading. That means, it does not affect any other PHP pages on your site.
Here the ini_set forces this PHP page to display all errors and warnings. You should pay attention to errors and ignore any warnings.
Problem #2: MySQL error - Client does not support authentication protocol
If you see the error such as below, it is an incompatibility problem for old MySQL client.
Warning: mysql_connect() [function.mysql-connect]: Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:\test\char-set.php on line 30 Could not connect to database server
MySQL documentation says:
MySQL 4.1+ uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. If you upgrade the server from 4.0, attempts to connect to it with an older client may fail with the following message:
The easiest way to fix this problem is reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement with the OLD_PASSWORD() function.
Run the following SQL in SQLyog:
Change your_password to your MySQL password for user root. After you run this command, view your PHP page again, the error should be gone.
Four steps to fine-tune your PHP code to connect to MySQL
We can define constants, functions, classes, and use include files to increase code reusability and portability. Here are 4 steps to fine-tune our code.
Step #1: Use PHP named constants for MySQL connection configuration data.
The first code snippet shown above can be recoded by using PHP named constants. Note that, it's a good idea to use upper-cases for PHP constants so that they can be easily identified when we work thru our code.
Step #2: Create an include file (e.g. config.php) and add the named constants to it.
In the include file, add the named constants.
Step #3: Create a function to connect to MySQL and put this function in an include file (e.g. functions.php).
Step #4: Modify the code in northwind-categories.php. Your code should look like this:
Now, view northwind-categories.php again in your web browser and you should see the exact categories data as that
displayed by our old northwind-categories.php page. Our new code is much more modularized and easily maintainable,
even though no PHP classes are used so far.
Copyright © 2017 GeeksEngine.com. All Rights Reserved.
This website is hosted by HostGator.
No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.