PHP with MySQL

PHP is a scripting language which is mostly used to develop web applications, where we have to stire user data in databases. The most popular open source database available is MySQL which is one of the most widely used database with PHP.

In this tutorial we will learn to connect remotely with the database and then execute SQL queries on tables.

If you do not know how to write SQL queries you can learn checkout our SQL tutorial.

We can either use mysqli or PDO for using PHP with MySQL. In this tutorial we will cover PDO as its supported with 12 languages and also support prepared statements which prevents SQL injection.

PDO Installation

Follow the following instructions: http://php.net/manual/en/pdo.installation.php

Connect to MySQL Server

Before we can execute any query we first need to connect to the MySQL server. Following is the code to connect to MySQL server using PDO:

<?php
// server details
$servername = "localhost";
$username = "username";
$password = "password";

try 
{
    $conn = new PDO("mysql:host=$servername; dbname=myDB", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

?>

In the code above, you need to change the username and password as per your server details.

Closing the Connection

It is good practice to close the connection to MySQL server once we are done with it.

It's very simple, we just have to set our connection object to null, as follows:

<?php

// disconnect MySQL server connection
$conn = null;

?>

Create a Database

A database contains one or more tables in which data is stored.

We can use PHP to create database in MySQL using PDO statement to execute the CREATE SQL query.

Below is the code:

<?php

// connect to MySQL server
$servername = "localhost";
$username = "username";
$password = "password";

try 
{
    $conn = new PDO("mysql:host=$servername", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // sql query to create database
    $sql = "CREATE DATABASE myDBPDO";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Database created successfully <br/>";
}
catch(PDOException $e)
{
    echo $sql . "<br/>" . $e->getMessage();
}

// disconnecting from MySQL server
$conn = null;

?>

PDO provides exception handling, hence its better than mysqli as there are high chances that exception may occur while executing SQL queries on database.

Just like the code above, we can provide any SQL query in the variable $sql and use the exec() function to execute the query.

Create a Table

Just like creating a database, we can create a table too, all we have to do is change the SQL query in the variable $sql in the code to the query to create table.

Here is a sample query to create a table User, with columns userid, email, password and fullname.

// sql query to create table
$sql = "CREATE TABLE User (
        userid INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
        email VARCHAR(50) NOT NULL,
        password VARCHAR(50) NOT NULL,
        fullname VARCHAR(50)
        )";

Here is the complete code for your reference,

<?php

// connect to MySQL server
$servername = "localhost";
$username = "username";
$password = "password";

try 
{
    $conn = new PDO("mysql:host=$servername", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // sql query to create table
    $sql = "CREATE TABLE User (
            userid INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
            email VARCHAR(50) NOT NULL,
            password VARCHAR(50) NOT NULL,
            fullname VARCHAR(50)
        )";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Table created successfully <br/>";
}
catch(PDOException $e)
{
    echo $sql . "<br/>" . $e->getMessage();
}

// disconnecting from MySQL server
$conn = null;

?>

Insert data into Table

Once a table is created the next step is to enter data into table. TO enter data into table we have to follow certain rules:

• If the value to be inserted is string, we must enclose it in quotes.
• Numeric value must not be enclosed in quotes.
• NULL must not be enclosed in quotes.
• If a column in a table is AUTO_INCREMENT then we do not have to insert data in that column, it is automatically inserted in the table.

SQL query will be,

// sql query to insert data into table
$sql = "INSERT INTO User (email, password, fullname)
        VALUES ('john@studytonight.com', 'IKilledBoogieMan', 'John Wick')";    

All we have to do is change the value of variable $sql to the above query and the remaining code will remain same, just like creating a table and creating a database.

Delete data from Table

Just like we executed the insert SQL statement, we can execute delete SQL statement to delete data from the table.

// sql query to delete data into table
$sql = "DELETE FROM User WHERE userid=1";    

Remaining complete code will remain the same.

Select data from Table

Selecting data from the table is a bit different from the other operations. If we have to select multiple rows of data from the table, then we have to execute the fetch() method in a loop.

Let's see an example where we have to return all the data from the table User. Below is the SQL execution part, considering we already have setup MySQL connection and the same is available in the $conn connection object.

// sql query to select data from User table
$stmt = $conn->prepare("SELECT userid, email, fullname FROM User"); 
// declaring an array to store the data
$data = array();
// execute the sql statement
$stmt->execute();
// loop to fetch all the rows
while($row = $stmt->fetch(PDO::FETCH_OBJ)) 
{
    $data[] = $row;
}

// close the connection
$conn = null;
// return the data
return $data;

Conclusion

This is not the end of the PHP tutorial, but the beginning, we will keep adding more concepts, examples, code snippets and tutorials to help you learn this amazing scripting language, so stay tuned!

For any queries or doubts, post you question in our Q & A Forum.