PHP MYSQL add, edit, delete and multiple delete example

PHP MYSQL add edit and delete example

In this tutorial, We are going to discuss PHP MYSQL add, edit, delete and multiple delete example. As we already discussed, how to connect to mysql database using php and user session management in Simple php login and logout script using php session and MYSQL database if you are not aware of these concepts please read those articles for better understanding.

Regarding MySQL queries we already have an article on MYSQL Create Read Update and Delete Queries Examples. The same format of MySQL queries is used to add, edit, delete and multiple delete example.

The following files are used for this example to add, edit, delete and multiple records using PHP and mysql in database.

  • dbcon.php – used to connect mysql database using php
  • common.php – used to maintain common php functions like printArray, to get home base url and redirect functions. This file will be used in every php file to make sure to have connection with database and access for common methods.
  • index.php – used to retrieve database entries from tbl_users table and to display records in web page with options like add, edit, delete and multiple delete
  • user-actions.php – which will have function for add record, edit record, edit record and multiple records
  • user-add.php – Tje file has the HTML form code with little php code which is used for both add user and edit user.
  • css/styles.css – Styles for this example
  • js/customValidate.js – Used for form validation using jquery. If you need more information gothrough these links jQuery form validation with plugin or Custom jQuery form validation without jquery pluginBelow is the code snippets for the respective files mentioned above. If want to see the demo and download the code please find the links end of this article.

    dbcon.php

    
    <?php ob_start(); session_start(); // Report simple running errors error_reporting(E_ERROR | E_WARNING | E_PARSE); $servername = "localhost"; $username = "root"; $password = ""; $database = "webapp"; // Create connection $conn = new mysqli($servername, $username, $password,$database); // Check connection if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    
    
    ?>
    
    

    common.php

    In this file we have redirection function, we have added additional folders according to our url structure if you face any problems in redirection please change in that.

    <?php
    
    include("dbcon.php");
    
    function printArray($obj){
    	print "
    <pre>";
    	print_r($obj);
    	print "</pre>
    
    ";
    }
    
    function home_base_url(){   
    
    	// first get http protocol if http or https
    	$base_url = (isset($_SERVER['HTTPS']) &&
    	$_SERVER['HTTPS']!='off') ? 'https://' : 'http://';
    	// get default website root directory
    	$tmpURL = dirname(__FILE__);
    
    	// when use dirname(__FILE__) will return value like this "C:\xampp\htdocs\my_website",
    	//convert value to http url use string replace,
    	// replace any backslashes to slash in this case use chr value "92"
    
    	$tmpURL = str_replace(chr(92),'/',$tmpURL);
    	// now replace any same string in $tmpURL value to null or ''
    	// and will return value like /localhost/my_website/ or just /my_website/
    	$tmpURL = str_replace($_SERVER['DOCUMENT_ROOT'],'',$tmpURL);
    
    	// delete any slash character in first and last of value
    	$tmpURL = ltrim($tmpURL,'/');
    	$tmpURL = rtrim($tmpURL, '/');
    
    	// check again if we find any slash string in value then we can assume its local machine
    	if (strpos($tmpURL,'/')){
    	// explode that value and take only first value
    	$tmpURL = explode('/',$tmpURL);
    	$tmpURL = $tmpURL[0];
    	}
    
    	// now last steps
    	// assign protocol in first value
    	if ($tmpURL !== $_SERVER['HTTP_HOST'])
    	// if protocol its http then like this
    	$base_url .= $_SERVER['HTTP_HOST'].'/'.$tmpURL.'/';
    	else
    	// else if protocol is https
    	$base_url .= $tmpURL.'/';
    	// give return value
    
    	return $base_url;
    }
    
    function redirectTo($newURL){
    	$base_url = home_base_url()."demo/php-add-edit-delete-example/";
    	//print $base_url.$newURL;exit;
    	header('Location: '.$base_url.$newURL);
    }
    
    ?>
    

    index.php

    <?php include("common.php"); $msg = ""; if($_SESSION["successMSG"] != ""){ $msg = $_SESSION["successMSG"]; $success = true; unset($_SESSION["successMSG"]); }elseif($_SESSION["errorMSG"] != ""){ $msg = $_SESSION["errorMSG"]; $success = false; unset($_SESSION["errorMSG"]); } $selSQL = " SELECT * FROM tbl_users"; $result = $conn->query($selSQL);
    	
    	$users = [];
    	if($result->num_rows > 0){
    		while($row = $result->fetch_assoc()) {
    			//printArray($row);
    			$users[] = $row;
    		}
    	}
    	$conn->close();
    	
    ?>
    
    
    <html>
    <head>
    <title>PHP Add, Edit and Delete example</title>
    
    <link href="css/styles.css" rel="stylesheet" type="text/css" />
    
    <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20type%3D%22text%2Fjavascript%22%20src%3D%22https%3A%2F%2Fcdnjs.cloudflare.com%2Fajax%2Flibs%2Fjquery%2F3.2.1%2Fjquery.min.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
    
    
    </head>
    <body>
    
    <div id="container" class="wrapper">
    	
    <form name="usersForm" action="user-actions.php" id="usersForm" method="POST">
    		
    <h1>PHP Add, Edit and Delete example </h1>
    
    		
    <div class="fltRight add-user"><a href="user-add.php">Add user</a></div>
    
    		
    		<?php if($msg){ ?>
    			
    
    "><?php print $msg;?>
    
    		<?php } ?>
    		
    <div class="table">
    			
    			
    <div class="row header">
    				
    <div class="cell"><input type="checkbox" name="chkAll" id="chkAll" /></div>
    <div class="cell">	Name</div>
    <div class="cell">Occupation</div>
    <div class="cell">Location</div>
    <div class="cell">Actions</div>
    
     
    			</div>
    
    			
    			<?php if($users){
    				for($i=0;$i<count($users);$i++){?>
    				
    <div class="row">
    					
    <div class="cell"><input type="checkbox" name="chkUsers[]" value="<?php echo $users[$i]["user_id"];?>" /></div>
    
     
    					
    <div class="cell"><?php print $users[$i]["firstname"]." ".$users[$i]["lastname"];?></div>
    
    	
    					
    <div class="cell"><?php print $users[$i]["occupation"];?>	</div>
    
    					
    <div class="cell"><?php print $users[$i]["location"];?></div>
    
    					
    <div class="cell"><a href="user-add.php?action=edit&uid=<?php print $users[$i]["user_id"];?>">Edit</a> / <a href="user-actions.php?action=delete&uid=<?php print $users[$i]["user_id"];?>">Delete</a></div>
    
    				</div>
    
    				<?php } }else{ ?>
    			
    <div class="row">
    		
    				
    <div class="errorMsg">No records found!</div>
    
    			</div>
    
    				<?php } ?>
    			
    		</div>
    
    		
    <div class="row login buttonLeft">
    				<input type="hidden" name="fAction" id="fAction" value="multidelete" />
    				<input type="button" name="deleteAll" id="deleteAll" value="Delete All" />
    			</div>
    
    	
    			</form>
    
    		
    	
    	
    <div class="clr">&nbsp;</div>
    
    	
    </div>
    
    
    <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20type%3D%22text%2Fjavascript%22%3E%0A%0A%24('%23chkAll').bind('click'%2C%20function()%7B%0A%09var%20chkAll%20%3D%20this.checked%3B%0A%09%24(%22input%5Bname%3D'chkUsers%5B%5D'%5D%22).attr(%22checked%22%2CchkAll)%3B%0A%7D)%3B%0A%0A%24('%23deleteAll').bind('click'%2C%20function()%7B%0A%09var%20count_checked%20%3D%20%24(%22%5Bname%3D'chkUsers%5B%5D'%5D%3Achecked%22).length%3B%0A%09%0A%09if(!count_checked)%7B%0A%09%09alert(%22Select%20atleast%20one%20checkbox%20to%20delete%22)%3B%0A%09%09return%20false%3B%0A%09%7Delse%7B%0A%09%09%24('%23usersForm').submit()%3B%0A%09%09return%20true%3B%0A%09%7D%0A%7D)%3B%0A%0A%0A%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
    
    
    </body>
    </html>
    

    user-actions.php

    <?php include("common.php"); function addUser($userData,$conn){ $sql = 'INSERT INTO tbl_users(firstname,lastname,email,occupation,location) values("'.$userData["firstname"].'","'.$userData["lastname"].'","'.$userData["email"].'","'.$userData["occupation"].'","'.$userData["location"].'")'; if ($conn->query($sql) === TRUE) {
    		$conn->close();
    		$_SESSION["successMSg"] = "User added successfully!";
    		redirectTo("index.php");
    	} else {
    		$_SESSION["errorMsg"] = "An error occured, please try again!";
    		redirectTo("index.php");
    	}	
    }
    
    function updateUser($userData,$conn){
    	
    	$sql = 'UPDATE tbl_users SET firstname = "'.$userData["firstname"].'" ,lastname = "'.$userData["lastname"].'",email="'.$userData["email"].'",occupation="'.$userData["occupation"].'",location = "'.$userData["location"].'" where user_id="'.$userData["uid"].'"';
    		
    	if ($conn->query($sql) === TRUE) {
    		$conn->close();
    		$_SESSION["successMSG"] = "User updated successfully!";
    		redirectTo("index.php");
    	} else {
    		$_SESSION["errorMSG"] = "An error occured, please try again!";
    		redirectTo("index.php");
    	}	
    }
    
    function deleteUser($userData,$conn){
    	
    	$sql = 'DELETE FROM tbl_users where user_id="'.$userData["uid"].'"';
    		
    	if ($conn->query($sql) === TRUE) {
    		$conn->close();
    		$_SESSION["successMSG"] = "User has been deleted successfully!";
    		redirectTo("index.php");
    	} else {
    		$_SESSION["errorMSG"] = "An error occured, please try again!";
    		redirectTo("index.php");
    	}	
    }
    
    function deleteMultipleUsers($userData,$conn){
    	$cheks = implode("','", $_POST['chkUsers']);
    	$sql = "DELETE FROM tbl_users where user_id in ('$cheks')";
    		
    	if ($conn->query($sql) === TRUE) {
    		$conn->close();
    		$_SESSION["successMSG"] = "Selected users are deleted successfully!";
    		redirectTo("index.php");
    	} else {
    		$_SESSION["errorMSG"] = "An error occured, please try again!";
    		redirectTo("index.php");
    	}
    }
    
    if($_POST["fAction"]){
    	$ch = $_POST["fAction"];
    }else if($_GET["action"]){
    	$ch = $_GET["action"];
    }
    
    switch($ch){
    	case "add":
    		addUser($_POST,$conn);
    	break;
    	case "edit":
    		updateUser($_POST,$conn);
    	break;
    	case "delete":
    		deleteUser($_GET,$conn);
    	break;
    	case "multidelete":
    		deleteMultipleUsers($_POST,$conn);
    	break;
    	default:
    		redirectTo("index.php");
    	break;
    }		
    
    ?>
    
    

    user-add.php

    <?php include("common.php"); $fAction = ""; if($_GET["action"] == "edit"){ $fAction = "edit"; $uid = $_GET["uid"]; $selSQL = " SELECT * FROM tbl_users where user_id='".$uid."'"; $result = $conn->query($selSQL);
    	$user = [];
    	if($result->num_rows > 0){
    		while($row = $result->fetch_assoc()) {
    			//printArray($row);
    			$user[] = $row;
    		}
    	}
    	
    }else{
    	$fAction = "add";
    }
    
    ?>
    <!DOCTYPE html>
    <head>
    <title>
    PHP <?php if($fAction == "edit"){ ?> Edit <?php }else{?>Add <?php } ?> user example
    </title>
    <link href="css/styles.css" rel="stylesheet" type="text/css" />
    </head>
    <body>
    
    <div id="container">
    	
    	
    <div class="login-page">
    		
    <form action="user-actions.php" name="registerForm" id="registerForm" autocomplete="off" method="POST" onsubmit="return validateForm();">
     
    			
    <h1><?php if($fAction == "edit"){ ?> Edit <?php }else{?>Add <?php } ?> user example</h1>
    
    			
    
     
    				<label for="firstname" class="uname" data-icon="u" > Firstname </label>
    				<input id="firstname" name="firstname" type="text" placeholder="Firstname" class="required" data-error-msg="Please enter firstname" value="<?php echo $user[0]["firstname"];?>"/>
    			
    
    			
    
     
    				<label for="lastname" class="youpasswd" data-icon="p"> Lastname </label>
    				<input id="lastname" name="lastname" type="text" placeholder="Lastname" class="required" data-error-msg="Please enter lastname" value="<?php echo $user[0]["lastname"];?>" /> 
    			
    
    			
    
     
    				<label for="email" class="uname" data-icon="u" > Email address </label>
    				<input id="email" name="email" type="text" placeholder="Enter address" class="required email" data-error-msg="Please enter email address" data-invalid-error-msg="Please enter valid email address" value="<?php echo $user[0]["email"];?>"/>
    			
    
    			
    
     
    				<label for="occupation" class="uname" data-icon="u" > Occupation </label>
    				<input id="occupation" name="occupation" type="text" placeholder="Occupation" class="required" data-error-msg="Please enter occupation" value="<?php echo $user[0]["occupation"];?>"/>
    			
    
    			
    
     
    				<label for="location" class="uname" data-icon="u" > Location </label>
    				<input id="location" name="location" type="text" placeholder="Location" class="required" data-error-msg="Please enter your location" value="<?php echo $user[0]["location"];?>"/>
    			
    
    			
    			
    
    				<?php if($fAction == "edit"){ ?>
    					<input type="hidden" name="uid" value="<?php print $uid;?>" />
    				<?php } ?>
    				<input type="hidden" name="fAction" value="<?php print $fAction;?>" />
    				<input type="submit" value="<?php if($fAction == "edit"){ ?> Update <?php }else{?>Add <?php } ?>" /> 
    			
    
    			
    		</form>
    
    	</div>
    
    	
    </div>
    
    <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20type%3D%22text%2Fjavascript%22%20src%3D%22js%2Fjquery-3.2.1.min.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
    <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20type%3D%22text%2Fjavascript%22%20src%3D%22js%2FcustomValidate.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
    </body>
    </html>
    

    css/styles.css

    #container{
    	margin:0 auto; width:768px;
    	font-family: 'FranchiseRegular','Arial Narrow',Arial,sans-serif;
    }
    #container h1{
    	font-family: 'BebasNeueRegular', 'Arial Narrow', Arial, sans-serif;
        font-size: 35px;
        line-height: 35px;
        position: relative;
        font-weight: 400;
    	text-align: center;
    }
    #container input[type="text"] {
        width: 95%;
        margin-top: 4px;
        padding: 10px 5px 10px 15px;
        border: 1px solid rgb(178, 178, 178);
        -webkit-appearance: textfield;
        -webkit-box-sizing: content-box;
        -moz-box-sizing: content-box;
        box-sizing: content-box;
        -webkit-border-radius: 3px;
        -moz-border-radius: 3px;
        border-radius: 3px;
        -webkit-box-shadow: 0px 1px 4px 0px rgba(168, 168, 168, 0.6) inset;
        -moz-box-shadow: 0px 1px 4px 0px rgba(168, 168, 168, 0.6) inset;
        box-shadow: 0px 1px 4px 0px rgba(168, 168, 168, 0.6) inset;
    }
    .login-page{ 
    	margin:20px auto;
    	width:400px;
    	border:1px #3d9db3 solid;
    	padding:20px;
    	border-radius: 15px;	
    }
    .login-page h2{
    	margin-top: 0px;
    	padding-bottom:10px;
    	text-align:center;
    	border-bottom:1px #3d9db3 solid;
    }
    .login.button{
    	text-align:right;
    }
    .login.buttonLeft{
    	text-align:left;
    }
    .login.button input,.login.buttonLeft input{
    	min-width: 75px;
        cursor: pointer;
        background: rgb(61, 157, 179);
        padding: 5px;
        font-family: 'BebasNeueRegular','Arial Narrow',Arial,sans-serif;
        color: #fff;
        font-size: 16px;
        border: 1px solid rgb(28, 108, 122);
        margin-bottom: 10px;
        text-shadow: 0 1px 1px rgba(0, 0, 0, 0.5);
    	border-radius:4px;
    }
    .login-page label{
    	color:#405c60;
    	position:relative;
    	display:inline-block;
    }
    
    .errorMsg{color:#FF0000;}
    .successMsg{color:#2cad0e}
    
    
    .wrapper {
        margin: 0 auto;
        padding: 40px;
        max-width: 800px;
    }
    .table {
        margin: 0 0 10px 0;
        width: 100%;
        box-shadow: 0 1px 3px rgba(0, 0, 0, 0.2);
        display: table;
    }
    
    .table .row {
        display: table-row;
        background: #f6f6f6;
    }
    .table .row .cell{
    	padding: 6px 12px;
    	display: table-cell;
    }
    .table .row:nth-of-type(odd) {
        background: #e9e9e9;
    }
    
    .table .row.header {
        font-weight: 900;
        color: #ffffff;
        background: #ea6153;
    }
    
    .row.header.green {
        background: #27ae60;
    }
    
    .row.header.blue {
        background: #2980b9;
    }
    
    @media screen and (max-width: 580px){
    .table {
        display: block;
    }
    .table .row {
        padding: 8px 0;
        display: block;
    }
    .table .row .cell {
        padding: 2px 12px;
        display: block;
    }
    }
    
    .clr{
    	clear:both;
    }
    
    .fltRight{
    	float:right;
    }
    .fltLeft{
    	float:left;
    }
    
    .add-user a{
    	text-align:right;
    }
    
    

    js/customValidate.js

    var requiredFields = $('form .required');
    
    var isEmpty = function(value){
    	if(value == "")
    		return true;
    	else
    		return false;
    }
    
    var validateForm = function(){
    	var vaildForm;
    	
    	if(requiredFields.length){
    		$.each(requiredFields,function(index,fieldObj){
    			vaildForm = validateField(fieldObj);
    		});
    	}
    	return vaildForm;
    }
    var removeErrorMsg = function(fieldObj){
    	var parentElem = $(fieldObj).parents('p');
    	
    	console.log($(parentElem).find('.errorMsg').length);
    	if($(parentElem).find('.errorMsg').length){
    		$(parentElem).find('.errorMsg').remove();
    	}
    }
    
    var displayErrorMessage = function(fieldObj){
    	var parentElem = $(fieldObj).parents('p');
    	
    	if(!$(parentElem).find('.errorMsg').length){
    		$(parentElem).append('
    <div class="errorMsg">'+$(fieldObj).data('errorMsg')+'</div>
    
    ');
    	}
    }
    
    var displayInvalidErrorMessage = function(fieldObj){
    	var parentElem = $(fieldObj).parents('p');
    
    	$(parentElem).find('.errorMsg').remove();
    	if(!$(parentElem).find('.errorMsg').length){
    		
    		$(parentElem).append('
    <div class="errorMsg">'+$(fieldObj).data('invalidErrorMsg')+'</div>
    
    ');
    	}
    }
    
    var validateField = function(fieldObj){
    	var className = $(fieldObj).attr("class");
    
    	var validForm =  true;
    	switch(className){
    		case "required":
    			console.log("required");		
    			if(isEmpty($(fieldObj).val())){				
    				validForm = false;
    				displayErrorMessage(fieldObj);
    			}else{
    				removeErrorMsg(fieldObj);
    			}
    		break;	
    		case "required email":	
    			var emailVal = /^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$/
    			if(isEmpty($(fieldObj).val())){				
    				validForm = false;
    				displayErrorMessage(fieldObj);
    			}else if(!emailVal.test($(fieldObj).val())){
    				validForm = false;
    				displayInvalidErrorMessage(fieldObj);
    			}else{
    				removeErrorMsg(fieldObj);
    			}
    		break;
    		case "required cpass":		
    			if(isEmpty($(fieldObj).val())){				
    				validForm = false;
    				displayErrorMessage(fieldObj);
    			}else if($('#password').val() !== $(fieldObj).val()){
    				validForm = false;
    				displayInvalidErrorMessage(fieldObj);
    			}else{
    				removeErrorMsg(fieldObj);
    			}
    		break;		
    	}
    	
    	return validForm;
    }
    
    var bindFormValidation = function (){
    	if(requiredFields.length){
    		$.each(requiredFields,function(index,fieldObj){
    			$(fieldObj).bind('blur keyup',function(){
    				validateField(fieldObj);
    			})
    		});
    	}
    }
    
    bindFormValidation();
    
    

    Hope this tutorial helps you in your realtime work, If you have any doubts/clarifications please feel free to comment for any help. Below are the links for demo and download.


    Download / Demo

You may also like...