This part of the tutorial will cover how to use MySQL with PHP. It will use some very basic SQL commands to demonstrate results. It will not however, go into much detail about MySQL. The MySQL tutorial is a better place to start for this.
This article assumes that a user name and login for a server with MySQL are both available.
This article also uses the newer MySQLi driver.
Connecting
The first thing that is necessary for PHP to be able to use MySQL with PHP is
to connect to the MySQL system. With MySQLi and PHP, this is done using the mysqli_connect
function.
<?php //Use the following command to connect $myConnection = mysqli_connect($dbName, $dbUsername, $dbPassword); ?>
It is important with MySQLi that all connections are saved into variables as they are required with nearly every MySQLi command.
It may also be a good idea to check that the connection succeeded:
<?php //Use the following command to connect $myConnection = mysqli_connect($dbName, $dbUsername, $dbPassword); //If there are any errors producing an error number if(mysqli_connect_errno()) { echo "Failed to connect!"; exit(); } ?>
Selecting the default database
Whilst this step is not entirely necessary, if PHP only needs to access one database it may be
a good idea to select the default database. This means that queries do not need the name of
the database included in them. This is achieved with the mysqli_select_db
function.
<?php //Select the database stored in $myDB mysqli_select_db($myConnection, $myDB); ?>
Note that the $myConnection
variable has been given as the first parameter. PHP requires that this
is given to any MySQLi functions.
Running queries
Of course the most important thing about MySQL is being able query the database. PHP allows this through the
mysqli_query
function:
<?php //Query the database $result = mysqli_query($myConnection, $query); ?>
The query should also be tested to make sure that it worked:
<?php //Query the database $result = mysqli_query($myConnection, $query); if($result) echo "Success!"; else echo "Failure!"; ?>
Closing the connection
It is very important that the connection is closed once it is finished with. PHP keeps the connection open whilst that
script is still running to support connection pooling but this wastes resources if only
one query is required. PHP uses the mysqli_close
function to close a connection:
<?php mysqli_close($myConnection); ?>