[printfriendly]
CRUD with PHP and MySQLi using OOP Syntax
Some of my PHP ZEND Students, friends wanted a PHP/MySQLi CRUD tutorial. The MySQLi Extensions provides improved performance and take advantage of PHP’s updated functions. In this tutorial, we’re going to focus on how to CRUD records with MySQLi using its object oriented syntax (yes, not the procedural syntax).
PHP/MySQLi: Connect to Database
This is how to connect PHP to MySQL database using MySQLi library. We’re going to name this file db_connect.php.
<?php
//set connection variables
$host = "localhost";
$username = "root";
$password = "";
$db_name = "phpmysqlicrud"; //database name
//connect to mysql server
$mysqli = new mysqli($host, $username, $password, $db_name);
//check if any connection error was encountered
if(mysqli_connect_errno()) {
echo "Error: Could not connect to database.";
exit;
}
?>
Just to explain the above code…
$dbhost = “localhost”;
-$dbhost is the MySQL server that you are using. If you’re using your computer as your testing server, its value is usually “localhost”.
$dbuser = “root”;
$dbpass = “”;
-These are the MySQL username and password that you have created or configured. The “root” and blank (“”) password are the usual settings when you haven’t configured it in your localhost. But you can changed them. For example, you are using PHPMyAdmin to manage your database, just find the privileges link.
$dbname = “phpmysqlicrud “;
-It is the name of your database.
$mysqli = new mysqli($host, $username, $password, $db_name);
-It is the syntax for creating database connection object. new mysqli($host, $username, $password, $db_name);
Sample table and data, you can run this script in your phpmysqlicrud database:
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `firstname` varchar(32) NOT NULL, `lastname` varchar(32) NOT NULL, `username` varchar(32) NOT NULL, `password` varchar(32) NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ; INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `password`, `modified`) VALUES (28, 'John', 'Dalisay', 'john', 'john123', '2011-09-23 14:36:19'), (39, 'aaa', 'aaa', 'aaa', 'aaa', '2011-09-23 15:44:04'), (40, 'bbb', 'bbb', 'bbb', 'bbb', '2011-09-23 15:44:13'), (41, 'ccc', 'ccc', 'ccc', 'ccc', '2011-09-23 15:44:32'), (46, 'XXXDDD', '', '', '', '2011-09-23 17:13:15'), (47, 'www', 'www', 'www', 'www', '2011-09-23 17:21:38'), (48, 'HEHEHEHEHE', '', '', '', '2011-09-23 18:50:10'), (49, 'www', 'www', 'www', 'www', '2011-09-23 19:28:24'), (50, 'EEEE', 'EEEE', 'EEEE', 'EEEE', '2011-09-24 05:01:36'), (51, 'asdf', 'asdf', 'asdf', 'asdf', '2011-10-04 18:44:19');
PHP/MySQLi: Create Record
Here’s the script on how to create a record with PHP and MySQLi. We’re going to the following code for our add.php
</strong>
<html>
<head>
<title>MySQLi Create Record</title>
</head>
<body>
<?php
//if there's any user action
$action = isset($_POST['action']) ? $_POST['action'] : "";
if($action=='create'){ //the the user submitted the form
//include database connection
include 'db_connect.php';
//our insert query query
//$mysqli->real_escape_string() function helps us prevent attacks such as SQL injection
$query = "insert into users
set
firstname = '".$mysqli->real_escape_string($_POST['firstname'])."',
lastname = '".$mysqli->real_escape_string($_POST['lastname'])."',
username = '".$mysqli->real_escape_string($_POST['username'])."',
password = '".$mysqli->real_escape_string($_POST['password'])."'";
//execute the query
if( $mysqli->query($query) ) {
//if saving success
echo "User was created.";
}else{
//if unable to create new record
echo "Database Error: Unable to create record.";
}
//close database connection
$mysqli->close();
}
?>
<!--we have our html form here where user information will be entered-->
<form action='#' method='post' border='0'>
<table>
<tr>
<td>Firstname</td>
<td><input type='text' name='firstname' /></td>
</tr>
<tr>
<td>Lastname</td>
<td><input type='text' name='lastname' /></td>
</tr>
<tr>
<td>Username</td>
<td><input type='text' name='username' /></td>
</tr>
<tr>
<td>Password</td>
<td><input type='password' name='password' /></td>
<tr>
<td></td>
<td>
<input type='hidden' name='action' value='create' />
<input type='submit' value='Save' />
<a href='index.php'>Back to index</a>
</td>
</tr>
</table>
</form>
</body>
</html>
When the form is filled up and submitted, it will look like this:
PHP/MySQLi: Read Records
Now if we’re able to create database record, we have to see it on our page. Here’s the script to view the records in an HTML table, we’ll call it display.php
</strong>
<html>
<head>
<title>MySQLi Read Records</title>
</head>
<body>
<?php
//include database connection
include 'db_connect.php';
//query all records from the database
$query = "select * from users";
//execute the query
$result = $mysqli->query( $query );
//get number of rows returned
$num_results = $result->num_rows;
//this will link us to our add.php to create new record
echo "<div><a href='add.php'>Create New Record</a></div>";
if( $num_results > 0){ //it means there's already a database record
echo "<table border='1'>";//start table
//creating our table heading
echo "<tr>";
echo "<th>Firstname</th>";
echo "<th>Lastname</th>";
echo "<th>Username</th>";
echo "<th>Action</th>";
echo "</tr>";
//loop to show each records
while( $row = $result->fetch_assoc() ){
//extract row
//this will make $row['firstname'] to
//just $firstname only
extract($row);
//creating new table row per record
echo "<tr>";
echo "<td>{$firstname}</td>";
echo "<td>{$lastname}</td>";
echo "<td>{$username}</td>";
echo "<td>";
//just preparing the edit link to edit the record
echo "<a href='edit.php?id={$id}'>Edit</a>";
echo " / ";
//just preparing the delete link to delete the record
echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";//end table
}else{
//if database table is empty
echo "No records found.";
}
//disconnect from database
$result->free();
$mysqli->close();
?>
</body>
</html>
Now our display.php file looks like this:
PHP/MySQLi: Update Record
Now here’s our update script. Edit link from our display.php can be used after doing this script. We’ll call it edit.php
</strong>
<?php
//include database connection
include 'db_connect.php';
//check any user action
$action = isset( $_POST['action'] ) ? $_POST['action'] : "";
if($action == "update"){ //if the user hit the submit button
//write our update query
//$mysqli->real_escape_string() function helps us prevent attacks such as SQL injection
$query = "update users
set
firstname = '".$mysqli->real_escape_string($_POST['firstname'])."',
lastname = '".$mysqli->real_escape_string($_POST['lastname'])."',
username = '".$mysqli->real_escape_string($_POST['username'])."',
password = '".$mysqli->real_escape_string($_POST['password'])."'
where id='".$mysqli->real_escape_string($_REQUEST['id'])."'";
//execute the query
if( $mysqli->query($query) ) {
//if updating the record was successful
echo "User was updated.";
}else{
//if unable to update new record
echo "Database Error: Unable to update record.";
}
}
//select the specific database record to update
$query = "select id, firstname, lastname, username, password
from users
where id='".$mysqli->real_escape_string($_REQUEST['id'])."'
limit 0,1";
//execute the query
$result = $mysqli->query( $query );
//get the result
$row = $result->fetch_assoc();
//assign the result to certain variable so our html form will be filled up with values
$id = $row['id'];
$firstname = $row['firstname'];
$lastname = $row['lastname'];
$username = $row['username'];
$password = $row['password'];
?>
<!--we have our html form here where new user information will be entered-->
<form action='#' method='post' border='0'>
<table>
<tr>
<td>Firstname</td>
<td><input type='text' name='firstname' value='<?php echo $firstname; ?>' /></td>
</tr>
<tr>
<td>Lastname</td>
<td><input type='text' name='lastname' value='<?php echo $lastname; ?>' /></td>
</tr>
<tr>
<td>Username</td>
<td><input type='text' name='username' value='<?php echo $username; ?>' /></td>
</tr>
<tr>
<td>Password</td>
<td><input type='password' name='password' value='<?php echo $password; ?>' /></td>
<tr>
<td></td>
<td>
<!-- so that we could identify what record is to be updated -->
<input type='hidden' name='id' value='<?php echo $id ?>' />
<!-- we will set the action to update -->
<input type='hidden' name='action' value='update' />
<input type='submit' value='Edit' />
<a href='display.php'>Back to display page</a>
</td>
</tr>
</table>
</form>
Before hitting the edit button the form should look like this:
After hitting the edit button the form should look like this:
PHP/MySQLi: Delete Record
So at last, our delete script. It uses JavaScript to prompt/ask the user if he really wants to delete a certain database record.
<html>
<head>
<title>MySQLi Delete Record</title>
</head>
<body>
<?php
//include database connection
include 'db_connect.php';
$action = isset($_GET['action']) ? $_GET['action'] : "";
if($action=='delete'){ //if the user clicked ok, run our delete query
//$mysqli->real_escape_string() function helps us prevent attacks such as SQL injection
$query = "DELETE FROM users WHERE id = ".$mysqli->real_escape_string($_GET['id'])."";
//execute query
if( $mysqli->query($query) ){
//if successful deletion
echo "User was deleted.";
}else{
//if there's a database problem
echo "Database Error: Unable to delete record.";
}
}
$query = "select * from users";
$result = $mysqli->query( $query );
$num_results = $result->num_rows;
echo "<div><a href='add.php'>Create New Record</a></div>";
if( $num_results ){
echo "<table border='1'>";
echo "<tr>";
echo "<th>Firstname</th>";
echo "<th>Lastname</th>";
echo "<th>Username</th>";
echo "<th>Action</th>";
echo "</tr>";
//loop to show each records
while( $row = $result->fetch_assoc() ){
extract($row);
echo "<tr>";
echo "<td>{$firstname}</td>";
echo "<td>{$lastname}</td>";
echo "<td>{$username}</td>";
echo "<td>";
echo "<a href='edit.php?id={$id}'>Edit</a>";
echo " / ";
//on click of our delete link, a pop up will appear.
//it means it run our delete JavaScript
echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
}else{
echo "No records found.";
}
$result->free();
$mysqli->close();
?>
<script type='text/javascript'>
function delete_user( id ){
//prompt the user
var answer = confirm('Are you sure?');
if ( answer ){ //if user clicked ok
//redirect to url with action as delete and id of the record to be deleted
window.location = 'delete.php?action=delete&id=' + id;
}
}
</script>
</body>
</html>
Delete page look like this:
CRUD with PHP and MySQLi using OOP Syntax
That’s it! Happy coding! 🙂
Hi, My name is Masud Alam, love to work with Open Source Technologies, living in Dhaka, Bangladesh. I’m a Certified Engineer on ZEND PHP 5.3, I served my first Fifteen years a number of leadership positions at AmarBebsha Ltd as a CTO, Winux Soft Ltd, SSL Wireless Ltd, Canadian International Development Agency (CIDA), World Vision, Care Bangladesh, Helen Keller, US AID and MAX Group where I worked on ERP software and web development., but now I’m a founder and CEO of TechBeeo Software Company Ltd. I’m also a Course Instructor of ZCPE PHP 7 Certification and professional web development course at w3programmers Training Institute – a leading Training Institute in the country.
Great, could you please give me hint about where to implemenmt validattion with a sticky form like this form that i’ve just used 🙂
hi im having a problem with delete.
: Undefined index: id in C:\wamp\www\project\try\delete.php on line 23
sorry im a newbie cant fix it
Hi Justin
window.location = ‘delete.php?action=delete&id=’ + id;
should be…
window.location = ‘delete.php?action=delete&id=’ + id;
Sorry I should have said
window.location = ‘delete.php?action=delete&id=’ + id;
should be…
window.location = ‘delete.php?action=delete&id=’ + id;
delete.php?id=49# (line 25, col 31)
5
ReferenceError: delete_user is not defined
where is index.php file in above code
Hi there are some bugs I cannot fix.
You call
delete_user( {$id} );
in display.php, but this function does not exist
in fact – you just need 3 files
delete.php shall become the name “index.php”
change any “delete.php” call to “index.php”
edit.php
and
add.php
that’s it.
Sir, Your delete.php is not working. How to resolve that problem?
Sir, Your edit, delete.php is not working.
Fatal error: Uncaught Error: Call to undefined function select id, name, class, email from student where id='() in C:\xampp\htdocs\rahul\oops\edit.php:49 Stack trace: #0 {main} thrown in C:\xampp\htdocs\rahul\oops\edit.php on line 49