CommonLounge Archive

MySQL with PHP

September 19, 2018

So far you have seen how PHP dynamically produces HTML (front-end). Now we will look at how PHP interacts with MySQL (back-end). You will learn how to connect to a database and execute data manipulation statements like INSERT, UPDATE and DELETE using PHP.

phpMyAdmin

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. It provides a user interface through which frequently used operations can be performed, while you still have the ability to directly execute any SQL statement. phpMyAdmin comes preinstalled with XAMPP.

When you installed XAMPP, you would have seen that the database that came along with it was called MariaDB and not MySQL. Both MariaDB and MySQL are very similar and can be used interchangeably. In fact, MariaDB is a fork of the MySQL open source project. This allows you to switch from MySQL to MariaDB (or vice versa) without having to alter your applications.

So let’s open up phpMyAdmin. Open a browser and go to http://localhost/phpmyadmin/. You will see something like this:

Screenshot of phpMyAdmin

Creating Database and Table

Now we want to create a database and call it test. Go to the left hand side column where it shows a list of default databases and click on the New button.

To create a database, type in the database name as test and click Create.

Creating a database on phpMyAdmin

You will see that the test database is now added to the list of available databases on the left. Click on test and you will be able to create a table here. Let’s call it student and set the number of columns to 3 and click on Go.

Creating a table on phpMyAdmin

On this next page, you will be asked to specify the attributes for each of the 3 columns you want to create. Let’s see what these attributes are (we will focus on the important ones for now):

  • Name: The name of the column.
  • Type: Data type of the column. Popular types are INT (integer), VARCHAR (character string), DATE etc.
  • Length: The maximum length of the record in that column. It is generally used if the type is VARCHAR. For INT type, we don’t need to specify any length.
  • Index: If set to PRIMARY, the corresponding column becomes the primary key for the table.
  • A_I: Auto Increment. By default, the starting value for A_I is 1, and it will increment by 1 for each new record. Often, we would like the primary key field to be created automatically every time a new record is inserted.

Now enter these details for each column of the student table:

  1. For the first column, use the values Name = id, Type = INT, Index = PRIMARY. Check the box under A_I (auto increment).
  2. For the second column, Name = name, Type = VARCHAR, Length = 20.
  3. For the third column, Name = marks, Type = INT.

Defining the columns of a table

Click on the Save button.

Great! You just created a test database and created a student table with three columns: id, name and marks using phpMyAdmin!

Insert

phpMyAdmin also allows us to execute SQL statements. Let’s write a query to insert data into the student table that we just created.

First, click on the student table under test on the left hand side. Then, on the top menu, choose SQL. Here we will write our query:

INSERT INTO student (name, marks) VALUES ("Ana", 80)

Inserting values into a table using phpMyAdmin

Click on Go. The row will be inserted and a message will be displayed:

1 row inserted.
Inserted row id: 1 (Query took 0.0591 seconds.)

Select

Now you can switch to the Browse tab on the top menu to look at our first entry inside the student table:

Selecting values from a table using phpMyAdmin

Update

We can easily update any entry in the table by simply double clicking on the field we want to update.

Updating values in a table using phpMyAdmin

Delete

We can delete a row by clicking on the Delete button present in that row. Let’s delete the entry we just added. Click on the Delete button and you will see this popup message:

Deleting values from a table using phpMyAdmin

Click on OK and your row will be deleted!

Note: We can select, update or delete rows of a table using the SQL query too (from the SQL tab in the top menu).

Now try messing around with the student table by inserting, updating and deleting more rows and get comfortable with the phpMyAdmin tool!

Connecting to the database using PHP Data Objects (PDO)

So far, you saw how to execute basic SQL operations, and also how to perform these actions directly using the web interface of phpMyAdmin. Since phpMyAdmin is itself written in PHP, we can execute SQL operations using PHP directly as well!

First, we need to connect to the database. Create a file connection.php with the following code:

<!-- connection.php -->
<?php 
    $dsn  = 'mysql:host=localhost; dbname=test';   // database name
    $user = 'root';
    $pass = '';
    try {
        $pdo = new PDO($dsn, $user, $pass);
    } catch(PDOException $e) {
        echo 'Connection error! '. $e->getMessage();
    }
?>

$dsn refers to the database name - it includes two parameters, host and dbname. host is set to localhost while dbname is set to the database we want to access (in this case test).

The default username in MySQL is root and the password is empty (if you installed it using XAMPP). We store these values in $user and $pass variables.

MySQLi vs PHP Data Objects

There are two popular methods work with a database in PHP:

  • MySQLi extension (the “i” stands for improved)
  • PDO (PHP Data Objects)

We will be using PDO (PHP Data Objects) in this course. PDO works on 12 different database systems, whereas MySQLi only works with MySQL databases.

Hence, if you need to switch your project to use another database, PDO makes the process easier. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.

Okay, so let’s see what’s going on in our code.

To connect to the database using a PDO, we create an instance (object) of the PDO class (which is predefined in PHP) and pass the three arguments - $dsn, $user and $pass.

$pdo = new PDO($dsn, $user, $pass);

If any of these values are incorrect - for example, if the database doesn’t exists, or the username password credentials are invalid - then an exception is thrown. Hence, we write this code in a try-catch block.

If an exception is thrown within the try block, the script stops executing and flows directly to the catch() block.

Go to your browser and open http://localhost/advanced/connection.php. If the database connection was made and the $pdo object got initialized, you will see a blank page.

Try changing the dbname parameter to something which doesn’t exist:

$dsn  = 'mysql:host=localhost; dbname=test1';   // database name

Now refresh the page and you will see an exception like this:

Connection error! SQLSTATE[HY000] [1049] Unknown database 'test1'

Awesome! You just learnt how to connect to the database using just PHP!

Data Manipulation using PHP Data Objects (PDO)

Finally, let’s look at how can we insert, select, update and delete values.

PHP Class corresponding to Database Table

For each table, we need to create a class which will have all methods corresponding to that table. For example, for the student table, we will create a class called Student which will have methods to insert, select, update and delete rows.

Create a file student.php:

<!-- student.php -->
<?php
    class Student {
        protected $pdo;
        function __construct($pdo) {
            $this->pdo = $pdo;
        }
    }
?>

Here, we have declared a variable $pdo with visibility set to protected. We will pass the $pdo database object to the constructor during initialization.

Before inserting new rows into the table using PHP, you might want to start fresh by deleting all records of the table student and setting the autoincrement value to 1 (it is not necessary though). This can be done running the query: `DELETE FROM student; ALTER TABLE student AUTOINCREMENT=1;`

Insert - Query Strings, bindParam() and execute()

To insert data into the student table, let’s create a function insertStudent() which accepts parameters $name and $marks.

<!-- student.php -->
<?php
    class Student {
        protected $pdo;
        function __construct($pdo) {
            $this->pdo = $pdo;
        }
        public function insertStudent($name, $marks) {
            $stmt = $this->pdo->prepare("INSERT INTO student (name, marks) VALUES (:name, :marks)");
            $stmt->bindParam(":name", $name, PDO::PARAM_STR);
            $stmt->bindParam(":marks", $marks, PDO::PARAM_INT);
            var_dump($stmt); // $stmt->execute();
       }
    }
?>

Here, $pdo->prepare() function takes in the SQL statement and returns a Query String object. We don’t want to concatenate the values to the string using the . operator as it will be vulnerable to SQL Injection. Instead, we will use a placeholder : to bind the parameters.

This is done using the bindParam() method, which takes three arguments: the placeholder name, the variable which we want to bind to the placeholder and the type of the data (PARAM_STR refers to string and PARAM_INT refers to integer).

Finally, we display the $stmt object using var_dump(). Later, we will replace this with $stmt->execute() which will execute the SQL statement.

To call the function, let’s create another file and call it testsql.php. This is the file we will be opening in our browser.

<!-- testsql.php -->
<?php  
    include "connection.php";
    include "student.php";
    global $pdo;
    $student = new Student($pdo);
    $student->insertStudent("Bob", 75);
?>

First, we include connection.php and student.php. Then, we declare a global variable $pdo using the global keyword. The $pdo variable which was included via connection.php file now has a global scope.

Then create an instance of the Student class and call the insertStudent() function with the parameters Bob and 75.

Now, run the testsql.php file and you should see the following output:

["queryString"]=> string(55) "INSERT INTO student (name, marks) VALUES (:name, :marks)"

Once we replace the var_dump() statement with $stmt->execute(), PHP binds the placeholders with the variables and the statement will be executed.

public function insertStudent($name, $marks) {
    $stmt = $this->pdo->prepare("INSERT INTO student (name, marks) VALUES (:name, :marks)");
    $stmt->bindParam(":name", $name, PDO::PARAM_STR);
    $stmt->bindParam(":marks", $marks, PDO::PARAM_INT);
    $stmt->execute();
    echo "The statement has been executed!<br>";
}

Save the file and refresh the browser. You will see:

The statement has been executed!

Now open up phpMyAdmin and click on the student table under the test database. You will see the new row which just got inserted:

You just inserted a row to a table present in the MySQL database using only PHP! Isn’t this awesome?

Select - fetchAll(), fetch() and rowCount()

Let’s insert a couple more rows into the student table. Replace $student->insertStudent("Bob", 75) in testsql.php with these lines:

$student->insertStudent("Cathy", 90);
$student->insertStudent("Dave", 65);

Save and refresh the browser:

The statement has been executed!
The statement has been executed!

Check your phpMyAdmin to confirm that the 2 rows got inserted.

Say we want to display the names and marks of all the students. Let’s write a function displayAll():

public function displayAll() {
    $stmt = $this->pdo->prepare("SELECT name, marks FROM student");
    $stmt->execute();
    $students = $stmt->fetchAll(PDO::FETCH_OBJ);
    foreach ($students as $student) {
      echo '<h3>
           '.$student->name.' '.$student->marks.'
        </h3>';
    }
}

Here, the fetchAll() method returns an array of objects and each object has the columns that the SELECT statement returned. The foreach loop traverses each student object and displays $student->name and $student->marks.

Finally, call the function from testsql.php.

<!-- testsql.php -->
<?php  
    include "connection.php";
    include "student.php";
    global $pdo;
    $student = new Student($pdo);
    $student->displayAll();
?>

Output:

Bob 75
Cathy 90
Dave 65

And that’s how you execute a SELECT statement using PHP and display the output!

You can use the fetch() method to return only a single object instead of an array of objects.

Let’s write a function to fetch the marks of a student. We will pass the student name as an argument to the function.

public function getMarks($name) {
    $stmt = $this->pdo->prepare("SELECT name, marks FROM student WHERE name = :name");
    $stmt->bindParam(":name", $name, PDO::PARAM_STR);
    $stmt->execute();
    $student = $stmt->fetch(PDO::FETCH_OBJ);
    echo $student->name . " scored " . $student->marks . " marks!<br>";
}

Call this function.

$student->getMarks("Dave");

Output:

Dave scored 65 marks!

You can also use the fetch() method to do a count query, like so:

public function countStudents() {
    $stmt = $this->pdo->prepare("SELECT COUNT(id) AS totalStudents FROM student");
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_OBJ);
    echo $result->totalStudents;
}

Call this function.

$student->countStudent();

Output:

3

We can also call the rowCount() method which returns the number of rows returned by the SELECT statement. Let’s write a function to check if the student table is empty or not.

public function isEmpty() {
    $stmt = $this->pdo->prepare("SELECT id FROM student");
    $stmt->execute();
    $count = $stmt->rowCount();
    if ($count > 0) {
        echo 'Some students found!';
    } else {
        echo 'No students found!'; 
    }
}

Call the function.

$student->isEmpty();

Output:

Some students found!

Update and Delete

UPDATE and DELETE statements work similar to the INSERT statement. Let’s create updateStudent() and deleteStudent() functions.

public function updateStudent($name, $marks) {
    $stmt = $this->pdo->prepare("UPDATE student SET marks = :marks WHERE name = :name");
    $stmt->bindParam(":name", $name, PDO::PARAM_STR);
    $stmt->bindParam(":marks", $marks, PDO::PARAM_INT);
    $stmt->execute();
}
public function deleteStudent($name) {
    $stmt = $this->pdo->prepare("DELETE FROM student WHERE name = :name");
    $stmt->bindParam(":name", $name, PDO::PARAM_STR);
    $stmt->execute();
}

Call the functions.

$student = new Student($pdo);
$student->displayAll();
$student->updateStudent("Bob", 70);
$student->deleteStudent("Cathy");
echo 'After updating and deleting:<br>';
$student->displayAll();

Output:

Bob 75
Cathy 90
Dave 65
After updating and deleting:
Bob 70
Dave 65

Great! Now you can do everything directly from PHP that you could do with SQL!

Generic Insert, Update and Delete

In the previous section, you saw how to write PHP functions to insert, update and delete rows of a table. In general, instead of writing these functions for each table we have, we can write generic insert, update and delete functions.

It is a good practice to have one class representing each table in the database. Hence, we will write the insert, update and delete functions in a Base class, and then have our table classes inherit from it. The generic functions will accept a table name and an associative array containing the key-value pairs that we want to insert or delete. For update queries, the generic function will also accept the id of the row which needs to be updated.

Here’s the code for base.php:

<!-- base.php -->
<?php 
  class Base {
      protected $pdo;
      function __construct($pdo) {
        $this->pdo = $pdo;
      }
      public function create($table, $fields = array()) {
        // inserts a row into database table
        $columns = implode(', ', array_keys($fields));
        $values = ':' . implode(', :', array_keys($fields));
        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$values})";
        if ($stmt = $this->pdo->prepare($sql)) {
          foreach ($fields as $key => $data) {
            $stmt->bindValue(':'.$key, $data);
          }
          $stmt->execute();
          return $this->pdo->lastInsertId();
        }
      }
      public function update($table, $user_id, $fields = array()) {
        // updates the row where id = $userid
        $columns = '';
        $i = 1;
        foreach ($fields as $name => $value) {
          $columns .= "{$name} = :{$name}";
          if ($i < count($fields)) {
            $columns .= ", ";
          }
          $i++;
        }
        $sql = "UPDATE {$table} SET {$columns} WHERE id = {$user_id}";
        if ($stmt = $this->pdo->prepare("$sql")) {
          foreach ($fields as $key => $value) {
            $stmt->bindValue(':'.$key, $value);
          }
          $stmt->execute();
        }
      }
      public function delete($table, $array) {
        // deletes a row from database table
        $sql = "DELETE FROM {$table}";
        $where = " WHERE";
        foreach ($array as $name => $value) {
          $sql .= "{$where} {$name} = :{$name}";
          $where = " AND ";
        }
        if ($stmt = $this->pdo->prepare($sql)) {
          foreach ($array as $name => $value) {
            $stmt->bindValue(':'.$name, $value);
          }
        }
        $stmt->execute();
      }
    }
  ?>

In each of the three functions, we create the SQL query from the arguments!

There are only a couple of new things in the code above that you have not seen before.

  1. The implode() function returns a string from the elements of an array. For example, implode('-', ['one', 'two', 'three']) is 'one-two-three'.
  2. In the insert function, the id of the last inserted row is returned using the lastInsertId() method.

Using this generic code, accessing the database becomes even easier. We highly recommend saving this code for future use in projects.

Let’s see what the Student class would look like given the Base class.

Since we are declaring the $pdo variable in the Base class, we can remove it from the Student class which now extends the Base class:

<!-- student.php -->
<?php
    class Student extends Base {
        function __construct($pdo) {
            $this->pdo = $pdo;
        }
        // other functions go here...
    }
?>

We need to include base.php to our testsql.php file:

<!-- testsql.php -->
<?php  
    include "connection.php";
    include "base.php";
    include "student.php";
    global $pdo;
    $student = new Student($pdo);
    $student->create('student', array('name' => "Alice", 'marks' => 94));
    $student->displayAll();
?>

Output:

Bob 70
Dave 65
Alice 94

Similarly, we can use the update and delete methods in this way:

$student->displayAll();
$student->update('student', 3, array('marks' => 88));
$student->delete('student', array('name' => 'Alice'));
echo 'After updating and deleting:<br>';
$student->displayAll();

Output:

Bob 70
Dave 65
Alice 94
After updating and deleting:
Bob 70
Dave 88

Easy, right? Now we don’t need the createStudent(), updateStudent() and deleteStudent() methods in the Student class.

Note: We did not create a generic fetch() function for SELECT statement as there are a lot of parameters for it like COUNT(), ORDER BY etc. However, if you see yourself repeating similar code again and again, you should think about writing a generic fetch() function as well.

Summary

Okay, so in this tutorial you have learned about:

  • phpMyAdmin: to perform operations in SQL using both the User Interface and SQL queries.
  • PHP Data Objects: how to connect to the database using PHP, and how to perform fetch, insert, update and delete operations
  • Generic insert, update and delete: Simple and handy PHP methods to perform SQL operations which can be reused for different database tables.

You made it this far! This is the most important tutorial in the course. Now you know how PHP works with both front-end as well as back-end.

You are now ready to create really cool websites with PHP! :)

© 2016-2022. All rights reserved.