CRUD Operation by MySqli procedural way in PHP

• Download CRUD_by_MySQLi-PHP.zip - 1.5 KB

Introduction

Now a days dynamic website cannot imagine without the database storage. Many website are used in MySQL database when using PHP. Here i want to guide you through what you should be doing - using PHP's MySQLi class.

At first introduce earlier, i want to known to you that, MySQLi classes functions allows to access MySQL database server. Espacially i want to say that, the library of MySQLi is designed to work with MySQL version 4.1.13 or newer where the (i) stands for improved the name of MySQLi. MySQLi functions can be used with PHP in the Object Oriented way but it also allows procedural usage as well.

So in this tutorial, I am trying to present Create-Read-Update-Delete Operation (basically it is called CRUD Operation) by MySQLi procedural way in the PHP. Here you no need to create database and table manually from your host.

MySqli Connection

The MySQL server allows using of different transport layers for connections. Connections use TCP/IP, UNIX domain sockets or Windows named pipes. The hostname localhost is bound to the use of UNIX domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.

Before access data in the MySQLi database, we need to be able to connect to the server. Here I have using the username as root with a password blank ("") connecting to the test database on the server localhost

$hostname = "localhost";
$username = "root";
$passowrd = "";
$database = "test";

//Database connection String
$con=mysqli_connect($hostname,$username,$passowrd);

Obviously, the database name is optional and can be omitted. If you omit the database name you must be sure to prefix your tables with the database in all of your queries.

Here we want to check the connection MySQLi, if not connected successfully then and error is thrown or else show successfully connected to MySQLi is shown.

// Check connection
if (mysqli_connect_errno()) {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
       echo "<br>";
}
else{
        echo "Successfully connected to MySQL...<br>";
}

Create Database

Here we need to create database using the sql without go to localhost. I have used the database name test. And according to the sql, a database “test” will be created if it is not available in the localhost or else database ”test” will not be created.

// Assign the database name
$database = "test";

//Create Database query
$sql="CREATE DATABASE IF NOT EXISTS $database";

By the mysqli_query() function perform queries against the database name “test” will be created if this name is available in the localhost or else an error is thrown to error creating database.

Next we need to changes the default database for the connection by the mysqli_select_db() if database “test” is created successfully.

// Check and Create database if not exists
if (mysqli_query($con,$sql)) {
  echo "Database $database connected successfully<br>";
  mysqli_select_db($con,$database);
} else {
  echo "Error connecting database: " . mysqli_error($con);
  echo "<br>";
}

Create Table

Now time to create the table within the “test” database using the query. You can use any table name, but I have used table name “Person”. Make sure there is no table that you select as $tablename.

// Assign table name
$tablename = "Persons";

Before write the query to create the table make sure how many fields Is required and what‘s type of data will be content within each field. Here I have created a table name “Person” and which has three fields FirstName, LastName and Age. FirstName and LastName both are contain string data which character length is 30 and Age field that is contains Integer data.

// Create table query
$sql="CREATE TABLE $tablename(FirstName CHAR(30),LastName CHAR(30),Age INT)";

Here, mysqli_query() perform queries against the database to create table “Person” within the database “test”. If table “Person’ is created successfully then “Table person created successfully” is shown or else an error is thrown that is error to creating table.

// Check and Execute query
if (mysqli_query($con,$sql)) {
  echo "Table persons created successfully<br>";
} else {
  echo "Error creating table: " . mysqli_error($con);
  echo "<br>";
}

Inset Operation

After a database and a table have been created, we can start adding data in them. To insert data into the table, I have assigned an associative array which array index is table column and value is data for the table column.

Here are some syntax rules to follow for the value of associative array:

1. String values must be quoted
2. Numeric values must not be quoted
3. The word NULL must not be quoted

As FirstName and LastName fields are declare as string so its corresponding value must be quoted and Age field is declare as integer so its corresponding value must not be quoted.

// Table Column Name and Value to be inserting into the table
$ColumnVal = array("FirstName"=>"MD","LastName"=>'Jamal',"Age"=>30);

Now time to call insert function which accept three argument; first is connection string($con), second is table name($tablename) and third is table column value’s associative array($ColumnVal) that you declare in previous.

// Insert Data from Table
insert($con,$tablename, $ColumnVal);

The definition of the insert function is a following,

// Insert Data within table by accepting TableName and Table column => Data as associative array
	function insert($connection, $tblname, array $val_cols){

		$keysString = implode(", ", array_keys($val_cols));

		// print key and value for the array
		$i=0;
		foreach($val_cols as $key=>$value) {
			$StValue[$i] = "'".$value."'";
		    $i++;
		}

		$StValues = implode(", ",$StValue);
		
		if (mysqli_connect_errno()) {
		  echo "Failed to connect to MySQL: " . mysqli_connect_error();
		}

		if(mysqli_query($connection,"INSERT INTO $tblname ($keysString) VALUES ($StValues)"))
		{
			echo "Successfully Inserted data<br>";
		}
		else{
			echo "Data not Inserted";
		}
	}

Here, if happen failed to connect MySQLi then an error code is thrown by mysqli_connect_errno() and display an error message.

Delete Operation

To delete the table data row your can assign an associative array that is to be which table row you want to delete. For example here I’m using an associative array which LastName is Cake and Age is 32; its means delete the data row which LastName is Cake and Age is 32. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

//Table Column Name and Value to be delete form the Table
$ColumnVal = array("LastName"=>"Cake","Age"=>32);

Now time to call delete function to delete the data row in the table. In this function is accepted three arguments is that, one is connection string ($con), second is table name ($tablename) on which table this operation will be performed and third is column value ($ColumnVal) that you are declare earlier.

//Delete Data from Table
delete($con, $tablename, $ColumnVal);

Following is the definitions of the delete function which can’t return anything only perform the delete operation.

//Delete data form table; Accepting Table Name and Keys=>Values as associative array
function delete($connection, $tblname, array $val_cols){

    $i=0;
    foreach($val_cols as $key=>$value) {
        $exp[$i] = $key." = '".$value."'";
        $i++;
    }

    $Stexp = implode(" AND ",$exp);

    if(mysqli_query($connection,"DELETE FROM $tblname WHERE $Stexp")){
        if(mysqli_affected_rows($connection)){
            echo "Data has been deleted successfully<br>";
        }
        else{
            echo "The data you want to delete is no loger exists<br>";
        }
    }
    else{
        echo "Error deleting data: " . mysqli_error($connection);
    }
}

N.B: In this delete function has restriction is that sql query WHERE clause only contains AND expression.

Update Operation

To update table data row you need to declare two associative arrays one is $set array that accepted set operation value against columns fields and other is $condition array that accepted conditional operation value against the column fields. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

//Set and Condition to be update row in the table
$set = array("LastName"=>'Cake',"Age"=>'27');
$condition = array("FirstName"=>'MD',"Age"=>'34');

Now time to call update function which accepted four arguments. First is connection string variable ($con), Second is table name ($tablename) on this which table you want to perform the operation, Third is $set associative array that you declare previous and Fourth is $condition associative array that also declare previous.

//Update function for the table row
 update($con, $tablename, $set, $condition);

Following is the definition of update function; this function can’t perform anything only perform the update operation.

//Update data within table; Accepting Table Name and Keys=>Values as associative array
	function update($connection, $tblname, array $set_val_cols, array $cod_val_cols){
		
		$i=0;
		foreach($set_val_cols as $key=>$value) {
			$set[$i] = $key." = '".$value."'";
		    $i++;
		}

		$Stset = implode(", ",$set);

		$i=0;
		foreach($cod_val_cols as $key=>$value) {
			$cod[$i] = $key." = '".$value."'";
		    $i++;
		}

		$Stcod = implode(" AND ",$cod);

		if(mysqli_query($connection,"UPDATE $tblname SET $Stset WHERE $Stcod")){
			if(mysqli_affected_rows($connection)){
				echo "Data updated successfully<br>";
			}
			else{
				echo "The data you want to updated is no loger exists<br>";
			}
		}
		else{
			echo "Error updating record: " . mysqli_error($conn);
		}
	}

N.B: In this delete function has restriction is that sql query WHERE clause only contains AND expression.

Fetch Operation

To fetch the data from table you can call fetch function which accept three argument one is connection string variable ($con), second is table name ($tablename) on which table you want to perform this operation, third is an array which contains the name of columns which value you want to fetch.

// Call to fetch table column value
$show = fetch($con ,$tablename, array("FirstName","LastName","Age"));

Following is the definition o f fetch function which fetches all rows and returns the result-set as an associative array

//Fetch data by accepting table name and columns(1 dimentional array) name
function fetch($connection, $table, array $columns){
    $columns = implode(",",$columns);
    $result = mysqli_query($connection, "SELECT $columns FROM $table");

    if(mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

    //return tow dimentional array as required columns result
    return mysqli_fetch_all($result,MYSQLI_ASSOC);
}

After perform the fetch function an associative array is return and you can shown under the print_r() function.

// Display data that is fetch 
echo "<pre>";
print_r($show);
echo "</pre>";

Close Connection

Although PHP automatically closes your mysqli connection upon script termination, Instead of you want to close the connection before your script is complete, you can do so by just invoking the close function. This is done by doing the mysqli_close() function closes a previously opened database connection.

//Close connection
mysqli_close($con);

Conclusion

MySQLi is the improved version for MySQL database server. In this version of MySQL many security features and function has been added and you can use it with PHP for the better web storage database. So let’s start and hope for better coding….:)