Methods MySQL database in PHP
30/05/2019 . 5 minutes, 28 seconds to read . Posted by Admin#php #phpdatabase #mysql #ViewsinCodeigniter #ModelsinCodeigniter #phpmyadmin #MSSQLServer #PHPCodigniter
To work with MySQL database, it is important to understand how to connect from your custom PHP program to MySQL database.
This article explains the following three methods along with appropriate example PHP program, which will explain how to connect from your PHP to MySQL database.
- Connect using the mysqli extension (Recommended)
- Connect using PDO (Recommended)
- Connect using traditional legacy mysql_ functions (Deprecated)
For this, you should install xampp or wamp local server.
If you don't know how to install a local server follow the instruction given in the link.
Once it is installed, the phpinfo page will display the mysql module as shown below:
For all the examples below, we’ll be connecting to a MySQL database that already exists. If you are new to MySQL, this is a good place to start: MySQL Beginners Guide
Note: Everything that is explained here will also work with MariaDB, as it is the same as MySQL.
1. Connect from PHP Using mysqli Extension
mysqli stands for MySQL Improved.
Create the following mysqli.php file under the Apache DocumentRoot or in the public directory of the local server.
<?php
$conn = new mysqli("localhost", "root", "", "test_codernaks");
if ($conn->connect_error) {
die("ERROR: Unable to connect: " . $conn->connect_error);
}
echo 'Connected to the database.<br>';
$result = $conn->query("SELECT name FROM users");
echo "Number of rows: $result->num_rows"; $result->close(); $conn->close();
?>In the above:
- mysqli – This function will initiate a new connection using the mysqli extension. This function will take these four arguments
- hostname where the MySQL database is running
- MySQL username to connect
- Password for the MySQL user
- MySQL database name to connect.
- query method – is used to specify your MySQL query. In this example, we are selecting the name column from test_coderanks database.
- Finally, we are displaying the number of rows selected using the num_rows variable in the result. We are also closing both the result and connection variable as shown above.
When you call the above mysqli.php from your browser, you’ll see the following output, which indicates that PHP was able to connect to the MySQL database and fetch data.
Connected to the database. Number of rows: 6
Note: If you are trying to connect to a remote MySQL database, then you may have to do this to avoid host not allowed error message: How to Allow MySQL Client to Connect to Remote MySQL server
2. Connect from PHP MySQL PDO Extension
PDO stands for PHP Data Objects.
PDO_MYSQL uses the PDO interface provided by PHP to connect from your PHP program to MySQL database.
The PHP-PDO is already part of the PHP-MySQL. So, you don’t have to search and look for the PHP-PDO package.
Note: To Use PDO extension you need PHP version 5.0 or higher.
Create the following MySQL-pdo.php file under your Apache DocumentRoot:
<?php
try
{
$conn = new PDO("mysql:host=localhost;dbname=test_coderanks", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo 'Connected to the database.<br>';
$sql = 'SELECT name FROM users';
print "User Name:<br>";
foreach ($conn->query($sql) as $row) {
print $row['name'] . "<br>";
}
$conn = null;
}
catch(PDOException $err)
{
echo "ERROR: Unable to connect: " . $err->getMessage();
}
?>In the above:
- new PDO – This will create a new PDO object which will take the following three arguments:
- MySQL connection string: This has format includes “mysql:host=$hostname;dbname=$dbname”. In the above example, the DB is running on localhost and we are connecting to test_coderanks database.
- MySQL username to connect
- Password for the MySQL user
- $sql variable – Create the SQL query that you want to execute. In this example, we are selecting the name column from the users table.
- query($sql) – Here we are executing the SQL query that we just created.
- foreach – Here, we are looping through the output from the above query command and storing it in the $row variable, and then we are displaying it using the print command.
- to close the connection, simply set the $conn variable to the null value.
When you call the above mysqli.php from your browser, you’ll see the following output, which indicates that PHP was able to connect to the MySQL database and fetch data.
Connected to the database. Users Name: Admin Zain Abid Hasnain Awais Umer
3. Connect from PHP Using mysql_ Functions
Use this method only if you are using an older version of PHP and cannot upgrade it to the new version for some reason.
It is recommended that you use the Method#2 and Method#3 shown above instead of this method. I’ve included this method only for reference, and not as a recommendation to use.
This particular extension was deprecated as of PHP 5.5 version. But starting from PHP 7.0 version, this will not even work, as it was removed.
Starting from PHP 5.5 version, when you use these functions, it will generate E_DEPRECATED error.
Create the following mysql-legacy.php file under the Apache DocumentRoot:
<?php
$conn = mysql_connect('localhost', 'root', '');
mysql_select_db("test_coderanks");
if (!$conn)
{
die('ERROR: Unable to connect: ' . mysql_error());
}
echo 'Connected to the database.<br>';
$result = mysql_query('SELECT name FROM users');
$row = mysql_fetch_row($result); echo "User 1: ", $row[0], "<br>\n";
mysql_close($conn);
?>In the above:
- mysql_connect function takes three arguments: 1) hostname where the MySQL database is running 2) MySQL username to connect 3) Password for the mysql user. Here it is connecting to the MySQL database that is running on the local server using username root and its password.
- mysql_select_db function – As the name suggests, this will pick the database that you want to connect to. This is equivalent to the “use” command. In this example, we are connecting to the database test_coderanks.
- mysql_query method – is used to specify your MySQL query. In this example, we are selecting the name column from the users table.
- mysql_fetch_row – Use this function to fetch the rows from the SQL query that we just created.
- mysql_close() method is used to close the connection.
When you call the above myself-legacy.php from your browser, you’ll see the following output, which indicates that PHP was able to connect to the MySQL database and fetch data.
Connected to the database. User 1: Admin