CRUD is the acronym for CREATE, READ, UPDATE and DELETE. These terms describe the four essential operations for creating and managing persistent data elements, mainly in relational and NoSQL databases. It refers to the four basic operations a software application should be able to perform – Create, Read, Update, and Delete. Here are examples of PHP functions to handle CRUD operations using the mysqli extension:
Create (INSERT) record:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
function create_record($conn, $table_name, $data) { $keys = array_keys($data); $values = array_values($data); $sql = "INSERT INTO $table_name (" . implode(",", $keys) . ") VALUES ('" . implode("','", $values) . "')"; if ($conn->query($sql) === TRUE) { return true; } else { return false; } } |
This function takes a MySQLi connection object, a table name, and an associative array of data to be inserted into the table. It constructs an INSERT statement using the keys and values of the data array, and executes the statement using the query method of the connection object. It returns true if the statement was successful, and false otherwise.
Read (SELECT) record:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
function read_record($conn, $table_name, $id) { $sql = "SELECT * FROM $table_name WHERE id = $id"; $result = $conn->query($sql); if ($result->num_rows > 0) { return $result->fetch_assoc(); } else { return false; } } |
This function takes a MySQLi connection object, a table name, and an ID value to be used in the WHERE clause of the SELECT statement. It executes the statement using the query method of the connection object, and returns the first row of the result set as an associative array, or false if there were no results.
Update record:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
function update_record($conn, $table_name, $id, $data) { $updates = array(); foreach ($data as $key => $value) { $updates[] = "$key = '$value'"; } $sql = "UPDATE $table_name SET " . implode(",", $updates) . " WHERE id = $id"; if ($conn->query($sql) === TRUE) { return true; } else { return false; } } |
This function takes a MySQLi connection object, a table name, an ID value to be used in the WHERE clause of the UPDATE statement, and an associative array of data to be updated. It constructs an UPDATE statement using the keys and values of the data array, and executes the statement using the query method of the connection object. It returns true if the statement was successful, and false otherwise.
Delete record:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function delete_record($conn, $table_name, $id) { $sql = "DELETE FROM $table_name WHERE id = $id"; if ($conn->query($sql) === TRUE) { return true; } else { return false; } } |
This function takes a MySQLi connection object, a table name, and an ID value to be used in the WHERE clause of the DELETE statement. It executes the statement using the query method of the connection object. It returns true if the statement was successful, and false otherwise.
Note that these functions assume that the id column of the table is an integer, and may need to be modified if the table has a different primary key column. Additionally, they do not provide any input validation or error handling beyond returning a boolean success value. It is recommended to add appropriate error handling and validation to these functions as needed.
