Real-world examples of CRUD applications with PHP & MySQL

CRUD operations are the foundation of almost every dynamic web application. Whether you are building a student management system, blog, inventory software, hospital portal, or admin dashboard, you will frequently need to Create, Read, Update, and Delete data from a database.

In this tutorial, you will learn how to perform complete CRUD operations using PHP and MySQL the right way - with clean code structure, secure database queries, and beginner-friendly examples.

We will cover everything step-by-step, including database connection, inserting records, fetching data, updating records, deleting records, form handling, validation, and protection against common security issues like SQL Injection using prepared statements.

By the end of this tutorial, you will be able to build a fully functional CRUD application using PHP & MySQL that follows modern coding practices and can be used in real-world projects.

A practical, no-fluff tutorial on setting up a prepared database connection, writing clean user-defined functions for Insert, Update, Delete, and handling batch inserts - all without the messy spaghetti code.

Prerequisites & project setup

Before we dive into functions and queries, let's make sure your environment is ready. You'll need a working PHP environment (PHP 7.4 or higher recommended), a MySQL database server, and the MySQLi extension enabled -which it is by default in most setups.

Create a project folder with a clean structure. At minimum you'll want a connection.php for your connection, and a functions.php for your reusable CRUD functions. Keeping these separate is the difference between code you can maintain and code you'll regret.

Preparing the database connection

Your database connection belongs in one place. Not scattered across files, not copy-pasted three times, not hardcoded in the middle of a function. One file, one connection, required everywhere it's needed.

The connection setup does two important things: it establishes the MySQLi connection with your credentials and it sets the character encoding to utf8mb4 - which handles everything from accented characters to emojis without breaking a sweat.

Your connection.php file

<?php
session_start();
global $conn;

define('DB_HOST', 'localhost');
define('DB_NAME', 'YOUR-DB-NAME');
define('DB_USER', 'DB-USER NAME'); //root for localhost
define('DB_PASS', 'DB-PASSWOR'); // Blank Password for localhost
define('TIMEZONE', 'Asia/Kolkata');
define('DB_CHARSET', 'utf8mb4');     // Supports full Unicode including emojis

function db_connect(){
    static $conn;
    $servername = DB_HOST;
    $username 	= DB_USER;
    $password 	= DB_PASS;
    $dbname 	= DB_NAME;
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);

    // Check connection
    if (mysqli_connect_errno()) {
        die("Connection failed: " . mysqli_connect_error());
    }
    return $conn;
}
$connect = db_connect();

function db_query($query){
    $connection = db_connect();
    $result = mysqli_prepare($connection, $query);
    return $result;
}

function db_query_last_id($stmt){
    $last_id = mysqli_stmt_insert_id($stmt);
    return $last_id;
}

function db_error($stmt){
    $connection = db_connect();
    return mysqli_stmt_error(mysqli_prepare($connection, $stmt));
}

function db_close($stmt){
    $connection = db_connect();
    mysqli_stmt_close(mysqli_prepare($connection, $stmt));
	
}
//  Set Character Encoding
if (!$connection ->set_charset(DB_CHARSET)) {
    error_log('Failed to set charset: ' . $conn->error);
    die(json_encode([
        'success' => false,
        'message' => 'Database configuration error.'
    ]));
}

// Set timezone
date_default_timezone_set(TIMEZONE);
?>

The Insert function

Our Insert function will accept the table name and an associative array of column-value pairs and the data type of each feil. This keeps it flexible - the same function works for any table in your database without writing a new query every time.

Under the hood, it dynamically builds a prepared statement from the array keys (column names) and binds the values using bind_param. The type string (the "ssi" part you've seen in examples) is also built automatically based on the data types of your values.

//INSERT DATA INTO TABLE
function insert_data($table_name=null, $field_values=null, $data_types=null){
	// Create comma-separated list of field names
        $field_names = array_keys($field_values);
        $field_names_list = implode(",", $field_names);

        // Create placeholders for values
        $value_placeholders = implode(",", array_fill(0, count($field_names), "?"));

        // Prepare statement
        $stmt = db_query("INSERT INTO $table_name ($field_names_list) VALUES ($value_placeholders)");

        // Bind parameters
        $stmt_bind_params = array_merge(array($stmt, $data_types), array_values($field_values));
        call_user_func_array("mysqli_stmt_bind_param", $stmt_bind_params);

        // Execute statement
        if(mysqli_stmt_execute($stmt)){
            $last_id = mysqli_stmt_insert_id($stmt);
			$result = $last_id;
            //$result = "Data insert Sucessfully.";
        }
        else{
            $result = "ERROR!! ".db_error();
        }
        
        // Close statement and connection
        mysqli_stmt_close($stmt);
        return $result;
}

The function returns the new record's ID / last insert id  on success, or Error message on failure.

How to call this function 

Let's take a simple example to insert data in to databse

<?php
$table_name = "mng_students";
$created_on = date("Y-m-d H:i:s");

$name   = $_POST['name'];
$age    = $_POST['age'];
$gender = $_POST['gender'];

$field_values = array(
"student_name" => $name ,
"age" => $age,
"gender" => $gender,
"created_on"=>$created_on
)
$data_types = "siss";
$last_insertid = insert_data($table_name, $field_values, $data_types);
if($last_insertid){ 
     echo "Success".$last_insertid;
}else{
     echo "Error!!";
}
?>

In the above example, the $field_values array keys are the MySQL column names and the values are the data you want to insert - typically coming from your HTML form via $_POST.

A quick note on SQL Injection: You may have noticed we haven't used mysqli_real_escape_string() or any other manual sanitization here — and that's intentional. Our functions use prepared statements with bound parameters, which means the database driver handles escaping automatically. This is actually safer than manual sanitization. We will, however, cover input validation and a full security layer in detail in the next article.

Supported Type Characters for bind_param()

Batch insert: multiple rows at once

Sometimes you don't need to insert one record - you need to insert fifty. Or five hundred. Looping your single-row insert function works, but it's slow: every call makes a round trip to the database. A batch insert wraps all the rows into a single query and executes once.

/* INSERT MULTIPLE */
function insert_data_batch($tbl, $data){
    $sql = 'INSERT INTO ' . $tbl . ' ';
    $var1 = '';
    $var2 = '';
    $data_o = $data[0];

    foreach ($data_o as $k => $v) {
        $var1 .= '`' . $k . '`, ';
    }
    $kk_f = rtrim($var1, ', ');
    $sql .= '(' . $kk_f . ')';
    $sql .= 'VALUES ';

    $vsql = '';
    foreach ($data as $datam) {
        $var2 = '';
        foreach ($datam as $kk => $vv) {
            $var2 .= '"' . $vv . '", ';
        }
        $vv_f = rtrim($var2, ', ');
        $vsql .= '(' . $vv_f . '), ';
    }

    $vsql_f = rtrim($vsql, ', ');

    $sql .= $vsql_f;

    $stmt = db_query($sql);
    mysqli_stmt_execute($stmt);
    $lastid = mysqli_stmt_insert_id($stmt);
    $affectrow = mysqli_stmt_affected_rows($stmt);

    if ($lastid) {
        $result['status'] = 1;
        $result['affectrow'] = $affectrow;
        $result['insertid'] = $lastid;
        $result['message'] = "Record added successfully!";
    } else {
        $result['status'] = 0;
        $result['affectrow'] = 0;
        $result['insertid'] = 0;
        $result['message'] = "ERROR!! " . db_error($stmt);
    }
    //db_close();
    return $result;
}

How to call this function 

$i = 0;
foreach($marks as $mark){	
	$data[$i]['english'] = $_POST['english'][$i];
	$data[$i]['physics'] = $_POST['physics'][$i];
	$data[$i]['chemistry'] = $_POST['chemistry'][$i];
        $i++;
}
$result = insert_data_batch($table_name,$data);	

In this tutorial, we covered how to set up a database connection, write a clean Insert function, and handle Batch Inserts efficiently in a single query. In the next part, we'll look at how to read data from the database and display it neatly in a table using both foreach and while loops — with real examples you can drop straight into your project.