Writing functions to insert, update, search, delete from SQL tables in PHP

Beginners who have started learning any programming language, some time find difficulties searching good material to start with. In today's post we will write some basic functions in PHP to insert, update, search and delete from SQL tables. Writing functions are important because once you have function created you don't have to write queries again and again only call the function.

First connect to your server

$host = "Your host name";
$user = "User name for host";
$pass = "Password for user";
$database = "Name of database";

$connect= mysqli_connect($host,$user,$pass);
mysqli_select_db($database ,$connect);

Once database is connected we can write functions.

Function to insert

Basic query to insert SQL table is

mysqli_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire',33)");

We will write function to insert values in SQL table

function InsertInTable($table,&$fields){
    $col = "insert into $table (`".implode("` , `",array_keys($fields))."`)";
    $val = " values('";		

    foreach($fields as $key => $value) { 
        $fields[$key] = mysqli_escape_string($value);
    }

    $val .= implode("' , '",array_values($fields))."');";		
	
    $fields = array();
    return;
}

Now where you want values to insert in table call the function and add values in array like below

$field['FirstName'] = 'Glenn';
$field['LastName'] = 'Quagmire';
$field['Age'] = '33';

InsertInTable("Persons",$field);

Function to Update

Basic query to update SQL table with PHP is

mysqli_query("UPDATE Persons SET Age=36
WHERE FirstName='Glenn' AND LastName='Quagmire'");

We will create function to update SQL table will be like this

function UpdateTable($table,&$fields,$condition) {
    
    $sql = "update $table set ";
    foreach($fields as $key => $value) { 
        $fields[$key] = " `$key` = '".mysqli_escape_string($value)."' ";
    }
    $sql .= implode(" , ",array_values($fields))." where ".$condition.";";	
    $fields = array();
}

Where you want to update values in table call function like this

$field['Age'] = '36';
UpdateTable("Persons",$field," FirstName= 'Glenn'");

Function to Select

Basic query to select record from table is

$result = mysqli_query("SELECT * FROM Persons");
while($row = mysqli_fetch_array($result)){

  echo $row['FirstName'] . " " . $row['LastName'];
  echo "
";
}

Function to select record will be like this

function SelectTableRecords($query){   
$result = mysqli_query ($query);
$count = 0;
   $data = array();
   while ( $row = mysqli_fetch_array($result)){
       $data[$count] = $row;
	$count++;
   }
   return $data;	
}

To use this function you will write like this

SelectTableRecords("SELECT * FROM Persons"); //for condition put WHERE clause after table name

Function to Delete

Basic query to delete is

mysqli_query("DELETE FROM Persons WHERE LastName='Griffin'");

Function to delete any record will be like this

function DeleteRecord($query){
    $result = mysqli_query ($query)
    return true;	
}

Use this function like below

DeleteRecord("DELETE FROM Persons WHERE LastName = 'Griffin'");

These are the few functions to get start with PHP as you start learning it deeply you can add functions to your collection and modify them according to your requirement. If you have any question you can ask in the comments below.