Connecting PHP with MYSQL Here, you will learn how PHP Script communicates with MySql. At the time of writing, PHP provides two main ways to connect to MySQL databases:
i)mysqli (MySQL improved) — This extension is specifically attached to MySQL, and provides the most complete access to MySQL from PHP. It gives both procedural -oriented and object – oriented interfaces. As it has a large set of functions and classes, it can seem very strong if a user is not used to work with databases. However, if a user know they are going to work with MySQL, and they want to push the most out of MySQL’s power from PHP scripts, then they should go for mysqli .
ii)PDO (PHP Data Objects) — This is an object – oriented extension that sits between the MySQL server and the PHP engine. It gives a nice, simple, clean set of classes and methods that can be used to work with MySQL databases. It can also be used to connect to lots of other database systems.
Making a Connection
To make a connection to a MySQL database in the PHP script, a user needs to create a new PDO object. When a user creates the object, then pass three arguments:
- The DSN – which describes the database to connect
- The username of the user you want to connect
- The user’s password
The returned PDO object serves as the script’s connection to the database:
$conn = new PDO( $dsn, $username, $password );
A DSN (Database Source Name) is simply a string that describes attributes of the connection such as the type of database system, the location of the database, and the database name.
For example, the following DSN can be used to connect to a MySQL database called mydatabase running on the same machine as the PHP engine:
$dsn = “mysql:host=localhost;dbname=mydatabase”;
If host is not specified, localhost is assumed. So, putting it all together, a user could connect to your mydatabase database.
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;
$conn = new PDO( $dsn, $username, $password );
If a user has finished with the connection, a user should close it so that it is freed up for other scripts to use. Although the PHP engine usually closes connections when a script finishes, it is a good idea to close the connection explicitly to be on the safe side.
To close the connection, just assign null to your connection variable. This effectively destroys the PDO object, and therefore the connection:
$conn = null;