PHP MySQL Database Tutorial

MySQL is the most popular database system that is used within PHP.

Let’s first understand about MySQL.

What is MySQL?

MySQL is open-source Relational Database Management System (RDMS). The name of MySQL is a combination of “My” which is the name of co-founder Michael Widenius’s (main author of the original version of open-source MySQL)  daughter, and “SQL” is abstract from Structured Query Language.

MySQL used by many popular websites like Facebook, Twitter, and YouTube.

MySQL is easy to install. If we install Wamp or Xampp server, then we do not need to install the MySQL database as it is already available in the Wamp or Xampp server.

  • MySQL is a Relational Database Management System (RDMS).
  • MySQL is distributed and developed by Oracle Corp.
  • MySQL is easy to use, fast, powerful, and secure.
  • MySQL can be compiled on several platforms.
  • MySQL is open-source, which means we can freely and easily download and use it.
  • MySQL uses standard SQL.
  • MySQL runs on the server.

MySQL database stores data into tables like another relational database. A table is a collection of data, and it is divided into rows and columns. Databases are useful for storing information sequentially.

A university may have a database with the following tables:

  • Student Name
  • Age
  • Batch Year
  • Field
  • Mobile

Downloading MySQL Database

We can download MySQL free from this link: http://www.mysql.com

Relationship between MySQL and PHP?

PHP is used to create a program and MySQL is used to interact with data sets. While developing a PHP application, we need to create a connection to a database. Then we will able to insert, delete, select, and update data.

There are multiple ways to use MySQL in a PHP script.

Relationship between MySQL and PHP

PHP connect to MySQL

PHP 5 works with MySQL database using:

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

An earlier version of PHP used the MySQL extension.

PHP 5 works with MySQL database using

Should we use MySQLi or PDO?

MySQLi or PDO have their advantages:

  • MySQLi only work with MySQL databases, whereas PDO work on 12 different database systems.
  • PDO makes the work easy.
  • If we need to switch our project to use another database, with the help of PDO, we have to change the connection string and a few queries.
  • With MySQLi, we need to re-write the entire code, including quires. 

MySQLi Installation:

For installation details: http://php.net/manual/en/mysqli.installation.php

PDO Installation:

For installation details: http://php.net/manual/en/pdo.installation.php

Connection to MySQL:

Example (MySQLi Object-Oriented)

Example (MySQLi Procedural)

Example (PDO)

Close the Connection:

The connection can be closed automatically when the script ends. To close the connection before, we can use the following:

Example (MySQLi Object-Oriented)

Example (MySQLi Procedural)

Example (PDO)

PHP Create MySQL Database

Create MySQL Database using MySQLi:

The CREATE DATABASE command is used to create a database in MySQL.

Create a database name with “details”.

  Example (MySQLi Object-Oriented)

Example MySQLi Procedural

Create a database name with “mydetails”.

Example (PDO)

  • PDO has an exception class that is used to handle any problems which may occur in our database queries. If an exception is thrown within the try { } block, the scripts will stop executing and control moves directly to the first catch () {} block.

PHP create MySQL Tables:

A MySQL database table has its unique name, and it consists of rows and columns.

Create a MySQL table using MySQLi:

For creating a table, we use CREATE TABLE command in the MySQL database.

We will create a table with a name of “students”, which consists of five columns, “id”, “first_name”, “last_name”, “age”, and “batch”.

The columns can hold the data which can specify the data types.

 Attributes for each column:

  • NOT NULL: Every row contains a value for the column. A row cannot be null.
  • DEFAULT: We can set any default value when no value set.
  • AUTOINCREMENT: MySQL automatically increases the value of the field by one each time a new record added.
  • PRIMARY KEY: Used to identify the rows in a table uniquely.

The table should have a primary key column (like “id”). The value of the primary key column must be unique.

Example (MySQLi Object-oriented):

Example (MySQLi Procedural):

Example (PDO)

PHP Insert data to MySQL:

Once the database and table created, we can start inserting data into them.

Syntax:

Syntax Rules:

  • The query must be quoted in PHP Language.
  • The string values inside the query must be quoted.
  • The numeric and word value must not be quoted.
  • A column AUTO_INCREMENT (“id”) is no need to specify in the SQL query. The MySQL will automatically add the value.

Example (MySQLi Object-Oriented):

Example (MySQLi Procedural):

Example (PDO):

MySQL Insert Multiple Records:

Multiple MySQL commands can be executed with the mysqli_multi_query().

Example (MySQLi Object-Oriented):

  • Each MySQL command must be separated by a semicolon (;).

Example (MySQLi Procedural):

Example (PDO):

PHP select data from MySQL:

The select command is used to select data from one or more tables.

Syntax:

The above syntax used to select data from a specified column.

In the above syntax, the (*) character is used to select data from all the columns.

Select Data with MySQLi:

Example (MySQLi Object-Oriented):

The above example selects the id, first_name, and last_name columns from the Students table.  In the above code, we have set a MySQL query which selects the id, first_name and last_name columns from the Students table.

The next line runs the query and puts the output data into a variable which is known as a  $result.

The num_row() function is used to check the more than zero rows returned or not.  If the more than zero rows returned, the fetch_assoc() function keeps all the output into an associative array by which we can loop through.

The while() loops with the result set and outputs the data by id, first_name, and last_name columns.

Example(MySQLi Procedural):

 Select Data With PDO:

Example (PDO):

Delete Data from a MySQL:

The DELETE command is used to delete a data/record from a table.

Syntax:

  • column_name: from which column we want to pick a value.
  • value: which value we want to delete from the selected column.

“STUDENTS” Table:

Id First_name Last_name Age Batch
1 Sonoo Jaiswal 23 2015
2 Rahul Jain 31 2019
3 Mehak Malik 26 2014

Example (MySQLi Object-Oriented):

Example (MySQLi Procedural):

Example(PDO):

Lets look at the "students" table after deletion of record:

Id First_name Last_name Age Batch
1 Sonoo Jaiswal 23 2015
2 Rahul Jain 31 2019

PHP Update Data in MySQL:

The UPDATE command is used to update existing records in a table.

"STUDENTS" Table:

Id First_name Last_name Age Batch
1 Sonoo Jaiswal 23 2015
2 Rahul Jain 31 2019

Example (MySQLi Object-Oriented):

Example (MySQLi Procedural):

Example (PDO):

Lets look at the “students” table after updated the record:

Id First_name Last_name Age Batch
1 Sonoo Jain 23 2015
2 Rahul Jain 31 2019

PHP Limit Data from MySQL:

LIMIT clause is used to define the number of records to return.

The LIMIT clause makes the code easy for multi-page results and pagination with MySQL, which is very useful on large tables. Returning a large number of records can impact on performance.

In the above code, we have selected all the records from 1-10 from a table called “Employee”.

When the above query runs, it will return the first 10 records.