PHP MySQL Complete Guide: Create, Connect & Manage DB records

MySQL database is the most popular and convenient way to store your Data if you are working with PHP.

Using PHP MySQL you can easily Connect and create a database.

Learning MySQL is almost necessary as learning core PHP if you wish to develop websites or web applications.

What is MySQL Database

MySQL database is the most popular Database system which is used with PHP.

Using a Database we can store and retrieve information for our website whenever we want.

A database is one of the most important websites for creating a website since data stored in the database makes a website truly dynamic.

Without the database, we have to enter data on our web pages manually and that is a truly exhausting task.

Consider the database as the memory of our website where we can store information and retrieve it at our convenience.

A Database is an organized collection of structured information and MySQL is a DBMS that we use to create and manipulate data.

How to Install MySQL database

Most of the local servers like Wamp, Xamp, or Mamp come with a MySQL database that we can use.

If you are using any of those local servers then you don’t need to install MySQL separately, since all of these applications have MySQL prepackaged.

As for Live Web hosting, every hosting company offers you MySQL with its hosting package.

Those who are not using WAMP, XAMP, or MAMP, can download MySQL DBMS from – https://www.mysql.com/.

How to Connect to MySQL

We can connect to the MySQL database using MySQLi.

The ‘i’ in MySQLi stands for improved.

This is an extension that is installed with PHP Automatically, however, if it is not installed you can install it from – http://php.net/manual/en/mysqli.installation.php.

Using the following code below we can open a connection to our MySQL server from PHP File-

<?php
    
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?> 

Using this code we will make the connection to the MySQL server installed on our system.

If there is an error during establishing a connection connect_error will inform us about it.

When the script is closed connection will be closed automatically.

Just in case you want to close the connection before you have to place this in your PHP code-

 $conn->close();

How to Create MySQL Database

To work on MySQL database, obviousely we need to create a database first.

We can create a Database manually on localhost by heading over to localhost/phpmyadmin/.

But if we are working with PHP it is more ideal to create database using our PHP script.

Here is the code for create a database named mySite-

// To Create MySQL database
$sql = "CREATE DATABASE mySchool";
if ($conn->query($sql) === TRUE) {
  echo "Database created successfully";
} else {
  echo "Error creating database: " . $conn->error;
}

So in the end, after creating a connection, checking for errors, creating a database and closing the connection after it; Our Script would look something like this-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE mySchool";
if ($conn->query($sql) === TRUE) {
  echo "Database created successfully";
} else {
  echo "Error creating database: " . $conn->error;
}
$conn->close();
?> 

How to Create MySQL Table in PHP?

Using PHP we can create tables in our selected MySQL Database.

If we already created the database we have to mention that as well while establishing a connection to our server.

As for the table we use the following command to create a table in MySQL-

CREATE TABLE MyStudents()

This will create an empty table without any rows or columns.

But of course, when we are creating a table we also need to create its columns otherwise it will be useless.

To create a column we put first the column’s name and then its datatype, there are a few optional attributes we can add in columns if we need it.

SQL attributes

Here are those optional attributes and what they do-

• NOT NULL – This will ensure that each will contain a value in that column. Null values are not allowed there.
• DEFAULT value – When no other value is passed, the default value will be added automatically.
• UNSIGNED – This is for Number data types only. This limits data stored in rows to zero and positive numbers only.
• AUTO INCREMENT – This will automatically increase the value by 1 when a new record is added.
• PRIMARY KEY – This is added to give the unique key to each row in the table. This is often used to assign an ID number to record, This almost all the time goes with AUTO_INCREMENT.

Create a table & its Columns in MySQL

So remembering all of the rules above here is what MySQL command to create a table and its rows will look like-

CREATE TABLE MyStudents(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    admission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

So in the code above-

• We have created a table named MyStudents using CREATE TABLE command.
• In our Table, we have created four columns with name id, firstname, lastname, email, and admission_date.
• The columns ‘id’ has an UNSIGNED attribute which means it could only have a positive number. It auto increases when a new record is added and it’s a primary key.
• Columns firstname and lastname could have varchar datatype and should not be left empty.
• Columns ’email’ is optional and could be left empty.
• The last column ‘admission_date’ is the attached date of creation of the record and if we update it will update itself with the date of update.

Create a table in MySQL database using PHP

That was how we can create a table using SQL commands, but we are using PHP here, and of course, we want to create a script that would create the same kind of table.

Here is how we can add a table in an already created database using PHP-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Create table and its columns
$sql = "CREATE TABLE MyStudents(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    admission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
// Check if tables created successfully
if ($conn->query($sql) === TRUE) {
  echo "Table MyStudents created successfully";
} else {
  echo "Error creating table: " . $conn->error;
}
// Close the connection
    
$conn->close();
?>

How Insert Data in MySQL Table in PHP?

Once we created a SQL database, table, and all required columns; our next step should be entering data in those empty fields.

Using phpmyadmin we can put data in our fields manually but we shouldn’t do that.

Especially when we can enter records programmatically with PHP and MySQL commands.

SQL Commands for Inserting records

Before writing a SQL Query to enter records, here are a few syntax rules that we have to remember while writing code to enter new records in a MySQL Table-

• In PHP when we assign SQL queries they must be “Quoted”.
• If we used double quotes to quote SQL query we must quote string values using a single quote.
• Each value must be separated by a comma.
• NULL and Numbers must not be quoted as we do with strings.

and here is the syntax that we use to add a new record to our MySQL Table-

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...) 

Here is the explanation for the syntax above-

• INSERT INTO SQL command uses to insert data. We have to put the name of our table after that command.
• As you can we put the names of columns in the first bracket separated by commas.
• We put values in the second bracket and they are also separated by commas.
• We do put values for each column respectively.
• Using this code we can put one row at a time.

Insert Data in MySQL Tables using PHP

So now we know the SQL syntax of inserting data into a table, creating a PHP script to insert data would be easier.

Previously we already created a database, a table, and all its columns.

So now we just have to write code to put data into those columns.

And do just here is PHP code that we can write-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Code to insert data in our table
$sql = "INSERT INTO MyStudents (firstname, lastname, email)
VALUES ('Rahul', 'Bodana', '[email protected]')";
// Check if this record added succesfully
if ($conn->query($sql) === TRUE) {
  $last_id = $conn->insert_id;
  echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close the connection
$conn->close();
?>

Here is an explanation of this code-

• As we have created columns id, firstname, lastname, email, and admissions_date previously. here we insert data in those fields.
• You may have noticed that I did not add any data for “id” and “admission_date” and there is a reason for that.
• Both of those fields’ data are automatically assigned when a new record is added, and that’s why we don’t have to add records for those columns.
• To confirm that our last record is added successfully, using $last_id we retrieve the id of the last record, and display a message if it is added successfully. Otherwise, it would just throw an error.

How to Insert Multiple Records in PHP MySQL Table?

Now we know how we can record in our SQL table, but what if want to add multiple records at the same time?

Well, it’s possible, but a little bit different than adding a single row of data.

Multiple SQL statements must be executed using mysqli_multi_query()

and also after each $sql we have to add .= after the first one.

What this will do is append the next SQL query to the first one and all of the records would be added successfully.

So here would be the complete code, if add multiple records in a MySQL table-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Adding multiple records
$sql = "INSERT INTO MyStudents(firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyStudents(firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyStudents(firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";
// check if multiple sql records added succefully
if ($conn->multi_query($sql) === TRUE) {
  echo "New records created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>

PHP MySQL Prepared Statements

A prepared statement is a feature that is used to execute the same or similar kind of SQL statement s again and again.

There are two stages of prepared statements: Prepare and Execute.

In a prepared state, a statement is sent to the database server to make it ready for the data that we are going to send.

Here we sent a template (Table, Columns, etc) where we want to insert the data that we are going to send.

The server checks the syntax and initializes the server’s internal resources for use.

And after that in the execution stage values are sent to the server.

Using both template and values server creates a statement template and executes it.

In MySQLi it supports an anonymous positional placeholder, which means we can place ‘?’ instead of real values while we sent statements during the preparation stage.

Compared to Multiple SQL statements, Prepared statements-

• Reduce the parsing time since here preparation on SQL query is done only once.
• Using this statement only one parameter is sent each time and that way it reduces bandwidth.
• The PHP-prepared statement is really very useful against SQL injections.

Here is how we can use the Prepared statement in PHP MySQL-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Prepare and bind
$stmt = $conn->prepare("INSERT INTO MyStudents(firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// Set parameters and execute
$firstname = "Aliya";
$lastname = "Khan";
$email = "[email protected]";
$stmt->execute();
$firstname = "Vijay";
$lastname = "Das";
$email = "[email protected]";
$stmt->execute();
$firstname = "Arjun";
$lastname = "Yadav";
$email = "[email protected]";
$stmt->execute();
echo "New records are inserted  successfully";
$stmt->close();
$conn->close();
?>

PHP MySQL Select Data

Once we create a database table, we also need to learn how to get data from a particular column and from a particular table in our database.

To Select data from one or more tables, we use SQL Query Select.

Here is the syntax to select one column from a table-

SELECT column_name(s) FROM table_name

And in case you want to select the whole table we use * to indicate that we to get all records from that table-

SELECT * FROM table_name

That is how we select data in SQL and use it with MySQLi in PHP-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Select records from a table
$sql = "SELECT id, firstname, lastname FROM MyStudents";
$result = $conn->query($sql);
// Display selected records
if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
// Close the connection
$conn->close();
?>

MySQL Where

We use WHERE clause to filter through records.

This is used to extract only those records which met conditions that we specified after it.

Here is the Syntax-

SELECT column_name(s) FROM table_name WHERE column_name operator value  

Here is an example of how we can use this in PHP-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
//Using select with Where
$sql = "SELECT id, firstname, lastname FROM MyStudents WHERE lastname='Yadav'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

MySQL Order By

Using the ORDER BY clause we can sort the result in ascending or descending order.

By default, all the records are in ascending order and if we want to sort records in descending order we have to use DESC keywords.

For ascending order keyword is ASC.

Here is the syntax for it-

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC  

To use ORDER BY in PHP-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Sort by lastname
$sql = "SELECT id, firstname, lastname FROM MyStudents ORDER BY lastname";
$result = $conn->query($sql);
// Check for results
if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

Delete Data in PHP MySQL

Just like we have the option to insert data, it would make sense to have the option to delete data.

Using the DELETE keyword in MySQL we can delete specific records.

Here is the syntax for DELETE in SQL-

DELETE FROM table_name WHERE some_column = some_value

As you may have noticed that ‘WHERE’ in this statement specifies which records we are trying to delete.

For example, let’s assume this is our ‘MyStudent’ table in our database ‘school’

Here we want to delete the record of Vijay Sharma whose id is ‘3’.

So applying the rules of SQL syntax our SQL command will be:

DELETE FROM MyStudents WHERE id=3

You see we use id here.

We can also use another column like firstname, lastname, or even admission date.

But considering another student might have the same firstname, lastname, or even admission date it would be better to use an id that is unique for every student no matter what.

Using PHP MySQL here is how we can delete that record-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// SQL to delete record of vijay sharma
// sql to delete a record
$sql = "DELETE FROM MyStudents WHERE id=3";
// Check if record is deleted succefully
if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}
//close the connection
$conn->close();
?>

Update data using PHP MySQL

So far, we have seen How to create, select, delete or sort data.

But what about updating already existing records?

To update already existing records we use UPDATE statement in SQL.

Here is the syntax for updating a record in a SQL table-

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

After the WHERE we specified which record we want to delete.

For example, this is our MySQL table-

And here we want to update id 1 where we accidentally write Aliya instead of Alia in firstname and we want to correct it.

To do so this would the SQL command-

UPDATE MyStudents SET firstname='Alia' where id=1

Here is how to update record using PHP MySQL-

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mySchool";
// Establish connection to server
$conn = new mysqli($servername, $username, $password, $dbname);
// Check for any connection errors
 if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// SQL to update record of id 1
$sql = "UPDATE MyStudents SET firstname='Alia' where id=1";
//Check if record is updated succefully
if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}
// Close the connection
$conn->close();
?>

How to Limit data in PHP MySQL

Using SELECT, we can select records from a single column or whole table.

By using * we can select to get all records from a particular table.

But using it alone without limiting data will put too much load on our server, especially if we are working with a large database.

To fix this problem we use LIMIT clause when we are selecting a large number of records.

What limit does is, limit records to given numbers.

Here is the syntax-

$sql = "SELECT * FROM Orders LIMIT 30"; 

Here once we select record, it will display only the first 30 records only.

If we want to return only few records from that limit we can also use OFFSET–

$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";

This code will return 10 records, starting from number 16.

We could also use a shorter syntax, which would display the same result-

$sql = "SELECT * FROM Orders LIMIT 15, 10";