PHP MySQL Database

In this PHP tutorial, you will learn all about the MySQL database connectivity in PHP. We will also discuss how to connect databases, create databases, create tables, insert into tables, fetch data from databases, update data, delete data, and close connections.

What is MySQL?

MySQL is a Relational Database Management System (RDBMS). It is the most widely used database system with PHP. Oracle Corporation creates, distributes, and supports MySQL. MySQL is one of the most widely used relational database systems on the web. It is freely downloadable and simple to set up.
MySQL, like other relational databases, stores data in tables format. A table is a collection of related data that is organized into rows and columns. Each row in a table represents a data record that is inherently linked to one another, such as information on a single person, and each column represents a specific field, such as id, first name, last name, email, and so on.

What are the advantages of MySQL?

  •     MySQL is simple to use while yet being incredibly powerful, quick, secure, and scalable.
  •     MySQL is compatible with a variety of operating systems, including UNIX or Linux, Microsoft Windows, Apple Mac OS X, and others.
  •     MySQL supports conventional SQL (Structured Query Language).
  •     MySQL is an excellent database management system for both small and big applications.
  •     MySQL has data security layers in place to keep sensitive data safe and secure.
     

How to connect PHP and MySQL?

To store or access data in a MySQL database, you must first connect to the MySQL database server. PHP provides two methods for connecting to a MySQL server: MySQLi (Improved MySQL) and PDO (PHP Data Objects). While the PDO extension is more portable and supports over a dozen other databases, the MySQLi extension, as the name implies, only supports the MySQL database. The MySQLi extension, on the other hand, makes it easy to connect to and run queries on a MySQL database server. Both PDO and MySQLi provide an object-oriented API, but MySQLi also provides a procedural API that is very simple for newcomers to understand.

What is the difference between MySQLi and PDO?

  •     PDO supports 12 other database systems, whereas MySQLi only supports MySQL databases.
  •     PDO and MySQLi are both object-oriented databases, however, MySQLi additionally has a procedural API.
  •     If during the development period, the user or development team wants to change the database, it is much easier to do so with PDO than in MySQLi because PDO supports 12 different database systems.
     

Connecting to MySQL Database Server

The mysqli_connect() function in PHP makes this simple. This connection is used for all communication between PHP and the MySQL database server. The following are the fundamental syntaxes for connecting to MySQL through the MySQLi and PDO extensions:

Syntax: using MySQLi procedural


$c = mysqli_connect("hostname", "username", "password", "database");

Syntax: using MySQLi object-oriented


$mysqli = new mysqli("hostname", "username", "password", "database");

Syntax: using PDO


$c = new PDO("mysql:host=hostname; dbname=database", "username", "password");

In the above syntax, the hostname parameter specifies the hostname (e.g. localhost) or IP address of the MySQL server, whereas the username and password parameters specify the credentials to access the MySQL server, and the database parameter, if provided, specifies the default MySQL database to be used when performing queries.

Example: using MySQLi procedural


<?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $c = mysqli_connect($host, $username, $password);             
    if (!$c) {
        die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected successfully";
    ?>

Example: using MySQLi object-oriented


<?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $mysqli = new mysqli($host, $username, $password);
    if ($mysqli === false) {
        die("ERROR: Could not connect. " . $mysqli->connect_error);
    }
    echo "Connect Successfully. Host info: " . $mysqli->host_info;
    ?>

Example: using PDO


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    try {  
        $c = new PDO("mysql:host=$host", $username, $password);                     
        $c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected successfully";
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    ?>

How to close a database connection in PHP MySQL?

After we connect to a MySQL database from a PHP script, we should also detach or close the connection when our work is finished. The syntax to close the MySQL connection in all three methods is mentioned below.

 

Syntax: using MySQLi procedural


mysqli_close($c);

Syntax: using MySQLi object-oriented


$c->close();

Syntax: using PDO


 $c = null;

How to create a database in PHP MySQL?

Now that you've learned how to connect to the MySQL database server. Now we will look to run a SQL query to build a database. We must first establish a database before we can save or access the data. In MySQL, the Establish DATABASE command is used to create a new database. Let's create a SQL query using the CREATE DATABASE statement, then run it by sending it to the PHP mysqli_query() function to eventually create our database. 

For example: using MySQLi procedural we are creating a database with the name db_pro


    <?php
    $host = "localhost";
    $username = "root";
    $password = ""; 
    $c = mysqli_connect($host, $username, $password);         
    if (!$c) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $sql_query = "CREATE DATABASE db_pro";
    if (mysqli_query($c, $sql_query)) {
        echo "Database created successfully";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
    }
    ?>

Example: using MySQLi object-oriented we are creating a database with the name db_obj


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";   
    $c = new mysqli($host, $username, $password);             
    if ($c === false) {
        die("ERROR: Could not connect. " . $c->connect_error);
    }
    $sql_query = "CREATE DATABASE db_obj";
    if ($c->query($sql_query) === true) {
        echo "Database created successfully";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . $c->error;
    }
    ?>

For example: using PDO we are creating a database with the name db_pdo


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    try {   
        $c = new PDO("mysql:host=$host", $username, $password);               
        $c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    try {
        $sql_query = "CREATE DATABASE de_pdo";
        $c->exec($sql_query);
        echo "Database created successfully";
    } catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
    }
    ?>

How to create tables in PHP MySQL?

We learned how to construct a database on the MySQL server. It's now time to create tables in the database where we will really store the data. A table arranges data into rows and columns. To build a table in a database, use the SQL CREATE TABLE command. Let's write a SQL query using the CREATE TABLE statement, then run it by passing it to the PHP mysqli_query() function to eventually create our table.

Example: using MySQLi procedural we are creating table student in database db_pro


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pro";
    $c = mysqli_connect($host, $username, $password, $database);          
    if (!$c) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $sql_query = "CREATE TABLE student(
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        first_name VARCHAR(30) NOT NULL,
        last_name VARCHAR(30) NOT NULL,
        email VARCHAR(70) NOT NULL UNIQUE
    )";
    if (mysqli_query($c, $sql_query)) {
        echo "Table created successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
    }
    ?>

Example: using MySQLi object-oriented we are creating table student in database db_obj


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $c = new mysqli($host, $username, $password, $database);     
        if ($c === false) {
        die("ERROR: Could not connect. " . $c->connect_error);
    }
    $sql_query = "CREATE TABLE student(
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        first_name VARCHAR(30) NOT NULL,
        last_name VARCHAR(30) NOT NULL,
        email VARCHAR(70) NOT NULL UNIQUE
    )";
    if ($c->query($sql_query) === true) {
        echo "Table created successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . $c->error;
    }
    ?>

Example: using PDO we are creating table student in database db_pdo


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pdo";
    try {  
        $c = new PDO("mysql:host=$host;dbname=$database", $username, $password);               
        $c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    try {
        $sql_query = "CREATE TABLE student(
            id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            first_name VARCHAR(30) NOT NULL,
            last_name VARCHAR(30) NOT NULL,
            email VARCHAR(70) NOT NULL UNIQUE
        )";
        $c->exec($sql_query);
        echo "Table created successfully.";
    } catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
    }
    ?>

How to insert values into tables in PHP MySQL?

You now understand how to create a database and tables in MySQL. Now we will see how to run a SQL query to put records into a table. To insert new entries into a database table, use the INSERT INTO command. Let's create a SQL query with acceptable values using the INSERT INTO statement, and then we'll run it by sending it to the PHP mysqli_query() function to insert data into the table. Here's an example of inserting a new entry into the person's database by providing values for the first name, last name, and email fields. 

For example: using MySQLi procedural we are inserting values into table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pro";          
    $c = mysqli_connect($host, $username, $password, $database);
    if (!$c) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', 'johndoe@mail.com')";
    if (mysqli_query($c, $sql_query)) {
        echo "Records inserted successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
    }
    ?>

For example: using MySQLi object-oriented we are inserting values into table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_obj";  
    $c = new mysqli($host, $username, $password, $database);      
       if ($c === false) {
        die("ERROR: Could not connect. " . $c->connect_error);
    }
    $sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', 'johndoe@mail.com')";
    if ($c->query($sql_query) === true) {
        echo "Records inserted successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . $c->error;
    }
    ?>

For example: using PDO we are inserting values into table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pdo";
    try { 
        $c = new PDO("mysql:host=$host;dbname=$database", $username, $password);             
        $c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    try {
        $sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', 'johndoe@mail.com')";
        $c->exec($sql_query);
        echo "Records inserted successfully.";
    } catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
    }
    ?>

How to update values in the table in PHP MySQL?

The UPDATE statement is used to alter or change existing records in a database table. This statement is generally used in conjunction with the WHERE clause to limit the modifications to records that meet particular criteria. Let's create a SQL query with the UPDATE statement and WHERE clause, then execute it by providing it to the PHP mysqli_query() function to update the table's entries. 

Example: using MySQLi procedural we are updating values into table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pro";
    $c = mysqli_connect($host, $username, $password, $database);          
    if (!$c) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $sql_query = "UPDATE student SET email='jd@mail.com' WHERE id=1";
    if (mysqli_query($c, $sql_query)) {
        echo "Records were updated successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
    }
    ?>


Example: using MySQLi object-oriented we are updating values into table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_obj";   
    $c = new mysqli($host, $username, $password, $database);   
        if ($c === false) {
        die("ERROR: Could not connect. " . $c--->connect_error);
    }
    $sql_query = "UPDATE student SET email='jd@mail.com' WHERE id=1";
    if ($c->query($sql_query) === true) {
        echo "Records were updated successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . $c->error;
    }
    ?>

For example: using PDO we are updating values into table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pdo";
    try {    
        $c = new PDO("mysql:host=$host;dbname=$database", $username, $password);             
        $c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    try {
        $sql_query = "UPDATE student SET email='jd@mail.com' WHERE id=1";
        $c->exec($sql_query);
        echo "Records were updated successfully.";
    } catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
    }
    ?>

How to delete values in a table in PHP MySQL?

The SQL DELETE command is used to delete records from a table in the same way that it is used to insert records into tables. It is commonly used in conjunction with the WHERE clause to remove only entries that meet specified criteria or conditions. Let's create a SQL query with the DELETE statement and WHERE clause, then execute it by providing it to the PHP mysqli_query() function to delete the table's records. 

For example: using MySQLi procedural we are deleting values from table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pro"; 
    $c = mysqli_connect($host, $username, $password, $database);     
    if (!$c) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $sql_query = "DELETE FROM student WHERE first_name='Roy'";
    if (mysqli_query($c, $sql_query)) {
        echo "Records were deleted successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
    }
    ?>

For example: using MySQLi object-oriented we are deleting values from table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_obj";     $c mysqli($host, $username, $password, $database);          
    if ($c ===  false) {
        die("ERROR: Could not connect. " . $c->connect_error);
    }
    $sql_query = "DELETE FROM student WHERE first_name='Roy'";
    if ($c->query($sql_query) === true) {
        echo "Records were deleted successfully.";
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . $c->error;
    }
    ?>

For example: using PDO we are deleting values from table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pdo";
    try {  
        $c = new PDO("mysql:host=$host;dbname=$database", $username, $password);                
        $c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    try {
        $sql_query = "DELETE FROM student WHERE first_name='Roy'";
        $c->exec($sql_query);
        echo "Records were deleted successfully.";
    } catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
    }
    ?>

How to select values in a table in PHP MySQL?

So far, you've learned how to construct a database and a table, as well as how to input data. It is now time to recover the data that was inserted into the table in the last tutorial. To fetch records from database tables, using the SQL SELECT command. Let's create a SQL query using the SELECT statement, then execute it by providing it to the PHP mysqli_query() function to obtain the table data. 

For example: using MySQLi procedural we are fetching values from table student


<?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pro"; 
    $c = mysqli_connect($host, $username, $password, $database);         
    if (!$c) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $sql_query = "SELECT * FROM student";
    if ($result = mysqli_query($c, $sql_query)) {
        if (mysqli_num_rows($result) > 0) {
            echo "<table>";
            echo "<tr>";
            echo "<th>id</th>";
            echo "<th>First Name</th>";
            echo "<th>Last Name</th>";
            echo "<th>Email</th>";
            echo "</tr>";
            while ($row = mysqli_fetch_array($result)) {
                echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['first_name'] . "</td>";
                echo "<td>" . $row['last_name'] . "</td>";
                echo "<td>" . $row['email'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
            mysqli_free_result($result);
        } else {
            echo "No records matching your query were found.";
        }
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
    }
    ?>

For example: using MySQLi object-oriented we are fetching values from table student


<?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_obj";  
    $c = new mysqli($host, $username, $password, $database);    
        if ($c === false) {
        die("ERROR: Could not connect. " . $c->connect_error);
    }
    $sql_query = "SELECT * FROM student";
    if ($result = $c->query($sql_query)) {
        if ($result->num_rows > 0) {
            echo "<table>";
            echo "<tr>";
            echo "<th>id</th>";
            echo "<th>First Name</th>";
            echo "<th>Last Name</th>";
            echo "<th>Email</th>";
            echo "</tr>";
            while ($row = $result->fetch_array()) {
                echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['first_name'] . "</td>";
                echo "<td>" . $row['last_name'] . "</td>";
                echo "<td>" . $row['email'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
            $result->free();
        } else {
            echo "No records matching your query were found.";
        }
    } else {
        echo "ERROR: Could not able to execute $sql_query. " . $c->error;
    }
    ?>

For example: using PDO we are fetching values from table student


    <?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "db_pdo";
    try {  
        $c = new PDO("mysql:host=$host;dbname=$database", $username, $password);                
        $c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
    try {
        $sql_query = "SELECT * FROM student";
        $result = $c->query($sql_query);
        if ($result->rowCount() > 0) {
            echo "<table>";
            echo "<tr>";
            echo "<th>id</th>";
            echo "<th>First Name</th>";
            echo "<th>Last Name</th>";
            echo "<th>Email</th>";
            echo "</tr>";
            while ($row = $result->fetch()) {
                echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['first_name'] . "</td>";
                echo "<td>" . $row['last_name'] . "</td>";
                echo "<td>" . $row['email'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
            unset($result);
        } else {
            echo "No records matching your query were found.";
        }
    } catch (PDOException $e) {
        die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
    }
    ?>