PHP CRUD Tutorial with MySQLi extension | Coding Cage

PHP CRUD Tutorial with MySQLi extension

On By
In this post i want to explain, how you can use an improved version of  MySQL called MySQLi, because after deprecation of MySQL nowadays most of PHP Programmers use MySQLi and PHP Data Objects(PDO), so  we are going to create again CRUD operations with PHP using MySQLi extension. Basically there are three ways of MySQLi to interact with database, simple procedural the oldest way, using Object Oriented method and third one is using statements so I'm going to use in this post Object and statement method.

PHP CRUD Tutorial with MySQLi extension


data.sql
Create a database named 'dbtest' and Import the following sql code in your phpmyadmin.

CREATE TABLE `dbtest`.`data` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fn` VARCHAR( 25 ) NOT NULL ,
`ln` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM ;

db.php

MySQLi connection with error checking using the object method. 

<?php
     define('_HOST_NAME','localhost');
     define('_DATABASE_NAME','dbtest');
     define('_DATABASE_USER_NAME','root');
     define('_DATABASE_PASSWORD','');
 
     $MySQLiconn = new MySQLi(_HOST_NAME,_DATABASE_USER_NAME,_DATABASE_PASSWORD,_DATABASE_NAME);
  
     if($MySQLiconn->connect_errno)
     {
       die("ERROR : -> ".$MySQLiconn->connect_error);
     }

crud.php

This file contains code for data insert, update and delete with the object method. 

<?php

include_once 'db.php';

/* code for data insert */
if(isset($_POST['save']))
{

     $fn = $MySQLiconn->real_escape_string($_POST['fn']);
     $ln = $MySQLiconn->real_escape_string($_POST['ln']);
 
  $SQL = $MySQLiconn->query("INSERT INTO data(fn,ln) VALUES('$fn','$ln')");
  
  if(!$SQL)
  {
   echo $MySQLiconn->error;
  } 
}
/* code for data insert */


/* code for data delete */
if(isset($_GET['del']))
{
 $SQL = $MySQLiconn->query("DELETE FROM data WHERE id=".$_GET['del']);
 header("Location: index.php");
}
/* code for data delete */



/* code for data update */
if(isset($_GET['edit']))
{
 $SQL = $MySQLiconn->query("SELECT * FROM data WHERE id=".$_GET['edit']);
 $getROW = $SQL->fetch_array();
}

if(isset($_POST['update']))
{
 $SQL = $MySQLiconn->query("UPDATE data SET fn='".$_POST['fn']."', ln='".$_POST['ln']."' WHERE id=".$_GET['edit']);
 header("Location: index.php");
}
/* code for data update */

?>

index.php

contains data insert form and data select code with object method. 

<?php
include_once 'crud.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>

<body>
<center>
<div id="header">
 <label>By : <a href="https://codingcage.com/">cleartuts - programming blog</a></label>
</div>
<br />
<div id="form">
<form method="post">
<table width="100%" border="1" cellpadding="15">
<tr>
<td><input type="text" name="fn" placeholder="First Name" value="<?php if(isset($_GET['edit'])) echo $getROW['fn'];  ?>" /></td>
</tr>
<tr>
<td><input type="text" name="ln" placeholder="Last Name" value="<?php if(isset($_GET['edit'])) echo $getROW['ln'];  ?>" /></td>
</tr>
<tr>
<td>
<?php
if(isset($_GET['edit']))
{
 ?>
 <button type="submit" name="update">update</button>
 <?php
}
else
{
 ?>
 <button type="submit" name="save">save</button>
 <?php
}
?>
</td>
</tr>
</table>
</form>

<br /><br />

<table width="100%" border="1" cellpadding="15" align="center">
<?php
$res = $MySQLiconn->query("SELECT * FROM data");
while($row=$res->fetch_array())
{
 ?>
    <tr>
    <td><?php echo $row['id']; ?></td>
    <td><?php echo $row['fn']; ?></td>
    <td><?php echo $row['ln']; ?></td>
    <td><a href="?edit=<?php echo $row['id']; ?>" onclick="return confirm('sure to edit !'); " >edit</a></td>
    <td><a href="?del=<?php echo $row['id']; ?>" onclick="return confirm('sure to delete !'); " >delete</a></td>
    </tr>
    <?php
}
?>
</table>
</div>
</center>
</body>
</html>

style.css


/* CSS Document */

#form
{
 width:500px;
 
 margin:0px auto;
 text-align:center;
 
}
#form form input
{
 width:100%;
 height:35px;
}
form button
{
 width:50%;
 height:35px;
}
table,td
{
 border:solid #e9e9e9 1px;
}

Using Statements : prepare(), bind_param() and execute().

index.php
this file contains code for inserts new records, select, update and delete all CRUD operations are done in this single file using MySQLi statements. 

<?php

 /* code for connection and database selection */
 $server = "localhost";
 $user = "root";
 $pass = "";
 $dbname = "dbtest";

 $dbcon = new MySQLi("$server","$user","$pass","$dbname");
 
 if($dbcon->connect_error)
 {
  echo "ERROR -> ".$dbcon->connect_error;
 }
 /* code for connection and database selection */


/* code for data insert */
if(isset($_POST['save']))
{

     $fn = $dbcon->real_escape_string($_POST['fn']);
     $ln = $dbcon->real_escape_string($_POST['ln']);
 
  $SQL = $dbcon->prepare("INSERT INTO data(fn,ln) VALUES(?,?)");
  $SQL->bind_param("ss",$fn,$ln);
  $SQL->execute();
  
  if(!$SQL)
  {
   echo $MySQLiconn->error;
  } 
}
/* code for data insert */


/* code for data delete */
if(isset($_GET['del']))
{
 $SQL = $dbcon->prepare("DELETE FROM data WHERE id=".$_GET['del']);
 $SQL->bind_param("i",$_GET['del']);
 $SQL->execute();
 header("Location: index.php");
}
/* code for data delete */



/* code for data update */
if(isset($_GET['edit']))
{
 $SQL = $dbcon->query("SELECT * FROM data WHERE id=".$_GET['edit']);
 $getROW = $SQL->fetch_array();
}

if(isset($_POST['update']))
{
 $SQL = $dbcon->prepare("UPDATE data SET fn=?, ln=? WHERE id=?");
 $SQL->bind_param("ssi",$_POST['fn'],$_POST['ln'],$_GET['edit']);
 $SQL->execute();
 header("Location: index.php");
}
/* code for data update */

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP CRUD Tutorial with MySQLi extension</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>

<body>
<center>
<div id="header">
 <label>By : <a href="https://codingcage.com/">cleartuts - programming blog</a></label>
</div>
<br />
<a href="http://cleartuts.blogspot.com/2015/03/php-crud-tutorial-with-mysqli-extension.html" title="Tutorial link" ><h1>PHP CRUD Tutorial with MySQLi extension(Statements)</h1></a>
<br />
<div id="form">
<form method="post">
<table width="100%" border="1" cellpadding="15">
<tr>
<td><input type="text" name="fn" placeholder="First Name" value="<?php if(isset($_GET['edit'])) echo $getROW['fn'];  ?>" /></td>
</tr>
<tr>
<td><input type="text" name="ln" placeholder="Last Name" value="<?php if(isset($_GET['edit'])) echo $getROW['ln'];  ?>" /></td>
</tr>
<tr>
<td>
<?php
if(isset($_GET['edit']))
{
 ?>
 <button type="submit" name="update">update</button>
 <?php
}
else
{
 ?>
 <button type="submit" name="save">save</button>
 <?php
}
?>
</td>
</tr>
</table>
</form>

<br /><br />

<table width="100%" border="1" cellpadding="15" align="center">
<?php
$res = $dbcon->query("SELECT * FROM data");

while($row=$res->fetch_array())
{
 ?>
    <tr>
    <td><?php echo $row['id']; ?></td>
    <td><?php echo $row['fn']; ?></td>
    <td><?php echo $row['ln']; ?></td>
    <td><a href="?edit=<?php echo $row['id']; ?>" onclick="return confirm('sure to edit !'); " >edit</a></td>
    <td><a href="?del=<?php echo $row['id']; ?>" onclick="return confirm('sure to delete !'); " >delete</a></td>
    </tr>
    <?php
}

?>
</table>
</div>
</center>
</body>
</html>




Like This Post? Please share!
Subscribe through email and get Free updates

You May Also Like...

17 comments:

  1. Hello Team,

    Is it possible to have single page crude rather creating multiple pages to execute CRUD transaction for several commands ?? please help understand the same.

    ReplyDelete
    Replies

    1. yes it is possible to perform CRUD operations in single page , in above code there is single page crud using MySQLi statements.

      Delete

  2. Thanks, I got it,

    it was under "Using Statements : prepare(), bind_param() and execute()." heading which I overlooked...

    Thanks again, I'll check with this code and will try complete my CRUD page. :)

    ReplyDelete

  3. thanks for this it helped me understand some basic concept on msqli. you just did it the right way i think and its simple to understand

    ReplyDelete
    Replies

    1. you're welcome eze, I'm glad that, it helps you :)

      Delete

  4. I've been using MySql for a number of years and have been dreading the switch to MySqli but thanks to your Excellent tutorial I am now beginning to understand it. Just did my first conversion to MySqli and it went very well. Thanks for all your efforts.

    ReplyDelete

  5. Download button does not work

    ReplyDelete

  7. This comment has been removed by the author.

    ReplyDelete

  8. Thank you for posting such a useful, impressive.your blog is so beautiful. you have give me great news.

    IELTS Coaching in Adelaide
    IELTS Training in Adelaide

    ReplyDelete

  9. it doesnt edit or delete...

    ReplyDelete

  10. why do you have two index.php files

    ReplyDelete

  11. Nice informative ideas throught this reference links and php coding.But its not enough to get more ideas. i have a thought like can u give attachments with the video presentations which is most useful for me.


    Php Training in Chennai

    ReplyDelete

  12. what's this error, sir?

    Fatal error: Call to a member function fetch_array() on a non-object in C:\xampp2\htdocs\PHP\SAMPLE\samplehome.php on line 53

    ReplyDelete

  13. suggestion : i think you want a sanitise the data before the update query

    ReplyDelete

  14. como le puedo implementar un buscador?

    ReplyDelete