PHP + MySQL CRUD examples

By /

In this post, let’s use PHP and MySQL to exercise some CRUD operations.

What is CRUD?

First of all, what is CRUD? CRUD came from following 4 operations with database.

  • Create
  • Read
  • Update
  • Delete

Create

Create is an operation where you adding a new record to the database.

Read

Read is an action where you query the database and read the queried record from the database.

Update

Update is an operation to update the information of existing record in the database.

Delete

Delete is an action to delete the selected record from the database.

Setting Up the Environment

Before jump into the exercise, you need to have the development environment setup. Please check the following posts before proceeding to the exercise.

Setting Apache, MySQL, PHP in your local Mac machine – You need to have a local server stack environment setup.

3 things you need to start scripting – You also need to setup your development environment such as IDE.

Setting up a MySQL user other than 'root'

You need to create a new user to be able to access to the MySQL database server from PHP script.

You can use MySQL Workbench to add an additional user easily.

Launch MySQL Workbench and go to Administration tab and select Users and Privileges.

Click Add Account

Enter the following information.

Login Name: newuser (arbitrary)
Authentication Type: Standard
Limit to Host Matching: localhost
Password: your_password

Next, go to Administrative Roles and check on DBA (this should automatically check the rest of the access privileges.

Then click Apply.

Database Environment

Make sure you have the database setup. This post shows some example of setting up the database using MySQL Workbench.

Create Record Example

Let’s start from creating a record. For this exercise, I will be using the database that has been setup in this post. Which is using the database (schema) of test_db and the table name called cars. We will add a new record to this table.

Initialize the connection to the server

$server_name = "localhost:3306";
$username = "yourusername";
$password = "yourpassword";
$dbname = "test_db";

$sql_connection = new mysqli($server_name, $username, $password, $dbname);

if($sql_connection->connrct_error){
    die("Connection failed: " . $sql_connection->connect_error);
}

Above codes are pretty standard lines of code you need in each action you do in any of the CRUD operations.

Next is constructing the SQL statement and execute it using mysqli_query()

See sample code below:

$sql = "INSERT INTO test_db.cars(car_model, car_brand) VALUES ('camry', 'toyota')";

if (mysqli_query($sql_connection, $sql)) 
{
    echo "Record added successfully<br>";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($sql_connection);
}

create_record.php

<?php
/*********** 
This is an example of creating a record using PHP and MySQL

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/11/2019
***********/

// server info
$server_name = "localhost:3306";
$username = "yourusername";
$password = "yourpassword";
$dbname = "test_db";

$sql_connection = new mysqli($server_name, $username, $password, $dbname);

if($sql_connection->connrct_error){
    die("Connection failed: " . $sql_connection->connect_error);
}

$sql = "INSERT INTO test_db.cars(car_model, car_brand) VALUES ('camry', 'toyota')";

if (mysqli_query($sql_connection, $sql)) 
{
    echo "Record added successfully<br>";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($sql_connection);
}	

?>

In the create_record.php, it will add a new record to the cars table with the values of car_model = camry and car_brand = toyota.

Update Record Example

Next is, let’s try to update the information of existing record. Below is the entire code.

update_record.php

<?php
/*********** 
This is an example of updating a record using PHP and MySQL

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/11/2019
***********/

// server info
$server_name = "localhost:3306";
$username = "yourusername";
$password = "yourpassword";
$dbname = "test_db";

$sql_connection = new mysqli($server_name, $username, $password, $dbname);

if($sql_connection->connrct_error){
    die("Connection failed: " . $sql_connection->connect_error);
}

$sql = "UPDATE test_db.cars SET car_model = 'accord', car_brand = 'honda' WHERE id = '3'";

if(mysqli_query($sql_connection, $sql)){
    echo "Record updated successfully<br>";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($sql_connection);    
}

?>

This time, if you see the SQL statement, it is using "UPDATE test_db.cars SET car_model = 'accord', car_brand = 'honda' WHERE id = '3'";

This means, you are trying to update the existing record id 3’s car_model and car_brand values to car_model = accord and car_brand = honda.

Read Records Example

In this example, let’s read all the records in the table. Below is the sample code:

read_record.php

<?php

/*********** 
This is an example of reading the records using PHP and MySQL

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/11/2019
***********/


// server info
$server_name = "localhost:3306";
$username = "yourusername";
$password = "yourpassword";
$dbname = "test_db";

$sql_connection = new mysqli($server_name, $username, $password, $dbname);

if($sql_connection->connrct_error){
    die("Connection failed: " . $sql_connection->connect_error);
}

$sql = "SELECT * FROM test_db.cars";

$result = $sql_connection->query($sql);

if($result->num_rows > 0){
    while($row = $result->fetch_assoc()){
        
        echo($row["car_model"] . " - " . $row["car_brand"] . "<br>");
    
    }
}

?>

SQL statement is simply "SELECT * FROM test_db.cars";

This means, select all records (* – asterisk) form the cars table.

Delete Record Example

Below is the sample code of deleting a record from the table.

<?php
/*********** 
This is an example of deleting a record using PHP and MySQL

github: https://github.com/yfujieda
twitter: https://twitter.com/yfujieda_
10/11/2019
***********/


// server info
$server_name = "localhost:3306";
$username = "yourusername";
$password = "yourpassword";
$dbname = "test_db";

$sql_connection = new mysqli($server_name, $username, $password, $dbname);

if($sql_connection->connrct_error){
    die("Connection failed: " . $sql_connection->connect_error);
}

$sql = "DELETE FROM test_db.cars WHERE id = '3'";

$result = $sql_connection->query($sql);

if(mysqli_query($sql_connection, $sql)){
    echo "Record deleted successfully<br>";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($sql_connection);    
}

?>

SQL statement is "DELETE FROM test_db.cars WHERE id = '3'";

This means, it will look for an id = 3 and delete it from the table.

Please note that, when you delete the record with id = 3 and when you create a new record, it will auto increment and assign an id value of 4.

Even though id = 3 was “deleted” from the table, that record id will never be re-used.

Sample Codes Download

You can download the CRUD example codes here.

Feel free to share this post!

Scroll to Top