PHP Connection to MySQL
1.0Introduction
2.0Opening connection to a MySQL database
3.0Closing connection to a MySQL database
4.0Creating your own PDO class
1
PHP Connection to MySQL v2 Prepared by Ivailo Chakarov
PHP Connection to MySQL
1.0Introduction
A PHP connection can be created to a MySQL database using either MySQLi extension (i stands for improved) or PDO (PHP Data objects) (W3Schools.com, March 2015).
PDO will work on different database systems while MySQLi will only work with MySQL database. With this in mind, if you are likely to change where you store your database you can choose PDO as it makes the process of transferring your database easier.
Both MySQLi and PDO support the use of prepared statements, which are used to protect your database from SQL injections.
The process of connecting to a MySQL database has been illustrated well in this diagram by Phillips and Davies:
Source: Learning PHP & MySQL, 2nd Edition by Jon A. Phillips, Michele E. Davis
2.0Opening connection to a MySQL database
In order to use the data in your database, first of all you need to establish a connection to it from a PHP web page. As part of the process of setting up the PDO connection, you will need to specify the name of the server, username and passwordthat you requireto access the server as well as thedatabase name which you need to use to query your database tables after you have connected to the server. For example:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully to " . $servername;
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
In the PHP script above, you will need to substitute the values of the variables for your own servername, username and password as well as the name of your database. For instance:
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employeeproject";
The rest of the PHP script creates an object using the PDO class and stores it as a variable called $conn. ( It also prints out a message confirming that the connection to the database has been successful or reports a connection error (if problems arise while connection to the database).
3.0Closing connection to a MySQL database
The connection will be closed automatically when the script ends. To close the connection to the database before, you will have to use the following:
$conn =null;
4.0Creating your own PDO class
A way of improving the way you connect to your database would be to place the settings in a separate .ini file. This can be named mysettings.ini, for instance and you can set the file access properties to ensure that trusted users only have access to the file. Your settings have to be modified accordingly.
[database]
driver = mysql
host = localhost
port = 3306
schema = database_name
username = your_username
password = your_password
You can create your own .php file which references the settings file and creates a class of your own which extends the predefined PDO class. This file can be called database_connection.php, for instance.
1
PHP Connection to MySQL v2 Prepared by Ivailo Chakarov
<?php
class MyPDO extends PDO
{
public function __construct($file = 'mysettings.ini')
{
$settings;
if (!$this->settings = parse_ini_file($file, TRUE)) throw new exception('Unable to open ' . $file . '.');
$dns = $this->settings['database']['driver'] .
':host=' . $this->settings['database']['host'] .
((!empty($this->settings['database']['port'])) ? (';port=' . $this->settings['database']['port']) : '') .
';dbname=' . $this->settings['database']['schema'];
parent::__construct($dns, $this->settings['database']['username'], $this->settings['database']['password']);
}
}
try {
$conn = new MyPDO();
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully to database <b>" . $conn->settings['database']['schema'] . "</b> on server <b>"
. $conn->settings['database']['host'] . "</b>; port number: <b>" . $conn->settings['database']['port'] ."</b>" ;
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
1
PHP Connection to MySQL v2 Prepared by Ivailo Chakarov