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.
data.sql
Create a database named 'dbtest' and Import the following sql code in your phpmyadmin.
this file contains code for inserts new records, select, update and delete all CRUD operations are done in this single file using MySQLi statements.
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.phpthis 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>
Hello Team,
ReplyDeleteIs it possible to have single page crude rather creating multiple pages to execute CRUD transaction for several commands ?? please help understand the same.
yes it is possible to perform CRUD operations in single page , in above code there is single page crud using MySQLi statements.
DeleteThanks, I got it,
ReplyDeleteit 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. :)
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
ReplyDeleteyou're welcome eze, I'm glad that, it helps you :)
DeleteI'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.
ReplyDeleteyou're welcome, you may like : PHP Data Object (PDO)
DeleteDownload button does not work
ReplyDeletethank you v much gan...
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThank you for posting such a useful, impressive.your blog is so beautiful. you have give me great news.
ReplyDeleteIELTS Coaching in Adelaide
IELTS Training in Adelaide
it doesnt edit or delete...
ReplyDeletewhy do you have two index.php files
ReplyDeleteNice 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.
ReplyDeletePhp Training in Chennai
what's this error, sir?
ReplyDeleteFatal error: Call to a member function fetch_array() on a non-object in C:\xampp2\htdocs\PHP\SAMPLE\samplehome.php on line 53
suggestion : i think you want a sanitise the data before the update query
ReplyDeletecomo le puedo implementar un buscador?
ReplyDelete