How to PHP Scripting- MySQL CRUD in PHP

The CRUD cycle describes the elemental functions of a persistent database. CRUD stands for Create, Read, Update, and Delete. Alternate words are sometimes used when defining the four basic functions of CRUD, retrieve instead of read, modify instead of update, or destroy instead of delete.

In this tutorial, we will go through steps of PHP CRUD grid. We want to demonstrate how PHP as a server side language, communicates with backend MySQL and renders front-end HTML.

 

Inserting Data Into a MySQL Database Table

You’ve understood how to create database and tables in MySQL from the¬†last tutorial. Let’s insert data to our table. Make a SQL query using the INSERT INTO statement with appropriate values, after that we will execute this SQL query through passing it to the mysqli_query() function to insert data into the table.

 

Example
	<?php
	  server with default setting (user 'root' with no password) */
	  $link = mysqli_connect("localhost", "root", "", "demo");
	  if($link === false){
		die("ERROR: Could not connect. " . mysqli_connect_error());
	  }
	 
	  // Attempt insert query execution
	  $sql = "INSERT INTO Employee (first_name, last_name, email_address) 
	  VALUES ('Anwar', 'Muhammed', 'anwar@lauyou.com')";
	  if(mysqli_query($link, $sql)){
		echo "Records added successfully.";
	  } else{
		echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
	  }
	 
	  mysqli_close($link);
	?>

 

Select Data From MySQL

MySQL SELECT statement is used quite heavily in PHP applications since most of them are database driven and one of their main functionalities is retrieving, and displaying data.

Example
	<?php
	  $servername = "localhost";
	  $username = "username";
	  $password = "password";
	  $dbname = "MyDatabase";
	  $conn = new mysql($servername, $username, $password, $dbname);

	  if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	  } 
	  $sql = "SELECT id, first_name, last_name FROM Employee";
	$result = $conn->query($sql);

	  if ($result->num_rows > 0) {
		// output data of each row
		while($row = $result->fetch_assoc()) {
			echo "id: " . $row["id"]. " - Name: " . $row["first_name"]. " " . $row["last_name"]. "<br>";
		}
	  } else {
		echo "0 results";
	  }
	  $conn->close();
	?>

Above example selects the id, first_name and lastn_ame columns from the Employee table and displays it on the page

 

Output
     Anwar Muhammed

 

Updating Database Table Data

Here, we learn how to replace the existing data of a MySQL table with freshly supplied up-to-date data using the UPDATE MySQL query. The UPDATE statement is used to change or modify the existing records in a database table. It is typically used in conjugation with the WHERE clause to apply the changes to only those records that match specific criteria.

 

Example
	<?php
	 // Connect to MySQL

	 // Updates Anwar's record from the "Employee" table
	 $result = mysql_query("UPDATE Employee SET email='anwarmuhammed@lauyou.com' WHERE first_name='Anwar' AND last_name='Muhammed'") 
	 or die(mysql_error());  

	 $result = mysql_query("SELECT * FROM Employee WHERE first_name='Anwar'") 
	 or die(mysql_error());  

	 // get the first (and hopefully only) entry from the result
	 $row = mysql_fetch_array( $result );
	 echo $row['first_name']." ".$row['last_name']." - ".$row['email']. "<br />";
	?>

 

Delete Data From a MySQL Table

The following examples delete the record with id=1 in the “Employee” table:

 

Example
	<?php
	  $servername = "localhost";
	  $username = "username";
	  $password = "password";
	  $dbname = "myDB";

	  $conn = new mysqli($servername, $username, $password, $dbname);
	  if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	  } 

	  // delete a record
	  $sql = "DELETE FROM Employee WHERE id=1";

	  if ($conn->query($sql) === TRUE) {
		echo "Record deleted successfully";
	  } else {
		echo "Error deleting record: " . $conn->error;
	  }

	  $conn->close();
	?>

 

Muhsin Yakkiparamban

Muhsin Yakkiparamban is the co-founder of Lauyou Learning. Muhsin holds Bachelor Degree in Civil Engineering from KMCT Calicut.

You may also like...