CRUD applications - The Update function

Updating data follows the same philosophy as inserting - we pass in the table name, an array of fields to change, and a condition that tells the database which rows to update. That last part is critical. An update without a proper WHERE clause will happily overwrite every row in your table.

Our function takes a $where parameter as an associative array too, keeping everything consistent and making it easy to chain conditions without wrestling with string concatenation.

/* UPDATE DATA */
function update_data($table_name=null, $field_values=null, $data_types=null, $where_clause=null){ 
	// Create comma-separated list of field names and values
	$field_value_pairs = array();
	foreach ($field_values as $field_name => $field_value) {
		$field_value_pairs[] = "$field_name = ?";
	}
	// Prepare statement
	if($where_clause==null){
		$field_value_pairs_list = implode(",", $field_value_pairs);
		$update_query = "UPDATE $table_name SET $field_value_pairs_list";
	}
	else{
		array_pop($field_value_pairs); 
		$field_value_pairs_list = implode(",", $field_value_pairs);
		$update_query = "UPDATE $table_name SET $field_value_pairs_list WHERE $where_clause";
	}
	$stmt = db_query($update_query);
	$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)){
		$result = "Record updated successfully!";
	} else {
		$result = "Nothing to Update!";
	}
	// Close statement and connection
	mysqli_stmt_close($stmt);
	return $result;
}

The function returns true on success and false if something went wrong. It's also worth checking affected_rows after an update if you need to confirm that a row actually matched your condition — a query can succeed but affect zero rows if the WHERE clause didn't match anything.

How to call this function 

<?php
$table_name = "mng_students";
$updated_on = date("Y-m-d H:i:s");
$name   = $_POST['name'];
$age    = $_POST['age'];
$gender = $_POST['gender'];
$edit_id = $_POST['edit_id'];
$field_values = array(
"student_name" => $name ,
"age" => $age,
"gender" => $gender,
"updated_on"=>$updated_on
"editid" => $edit_id
)
$data_types = "sissi";
$where_clause = "id = ? ";
$result = update_data($table_name, $field_values, $data_types, $where_clause);
if($result){ 
     echo "Success";
}else{
     echo "Error!!";
}
?>

The Delete function

Deleting data is the operation that deserves the most respect. It's easy to write, and easy to misuse. Our delete function follows the same pattern as update - a table name and a WHERE condition. We never let a delete run without a condition.

Think of the $where requirement not as a restriction but as a feature. It forces you to be explicit about exactly which records are going away. That intentionality will save you - and your data - more than once.

/* DELETE DATA FROM TABLE */
function delete_data($table_name, $field_name, $field_value){
// Connect to database
    $conn = db_connect();
// Prepare statement
    $stmt = db_query("DELETE FROM $table_name WHERE $field_name = ?");
// Bind parameters
    mysqli_stmt_bind_param($stmt, "s", $field_value);
// Execute statement
    mysqli_stmt_execute($stmt);
// Check for affected rows
    $affected_rows = mysqli_stmt_affected_rows($stmt);
// Close statement and connection
    mysqli_stmt_close($stmt);
    mysqli_close($conn);
// Return number of affected rows
    return $affected_rows;
}

How to call this function 

$table_name = "mng_students";
$dlt_id = $_GET['dlt'];
// Define table and field names
$field_name = "id";
$field_value = $dlt_id;
// Call function to delete data
$result = delete_data($table_name, $field_name, $field_value);

Reading rows from any table, safely

Just like our insert and update functions, our fetch function will be generic enough to work with any table. It accepts the connection, a table name, an optional array of conditions (for your WHERE clause), and an optional column list so you can select only the fields you actually need.

The function returns an array of associative arrays - each inner array being one row from the database, with the column names as keys. This makes it easy to work with in foreach loops.

function query_data($query, $type = 'obj', $len = 'M')
{
// Connect to the database
    $connection = db_connect();
// Prepare the statement
    $stmt = mysqli_prepare($connection, $query);
// Execute the query
    mysqli_stmt_execute($stmt);
// Fetch the results
    $result = mysqli_stmt_get_result($stmt);
    $res = [];
    if ($result) {
        if ($type == 'array') {
            while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
                $res[] = $row;
            }
        } else {
            while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
                $res[] = (object) $row;
            }
        }
    }
// Return the results
    if (!empty($res)) {
        if ($len == 'S') {
            return $res[0];
        } else {
            return $res;
        }
    } else {
        return [];
    }
$connection->close();
mysqli_stmt_close($stmt);
db_close($stmt);
}

How to call this function 

$result = query_data("Select id, name, class from `mng_students` order by id ");
foreach($result as $data){
   echo $data->name."
";
}