আগামী ১৫ ডিসেম্বর -২০১৭ তারিখ থেকে শুরু হচ্ছে পাঁচ মাস ব্যাপী Professional Web Design and Development with HTML, CSS PHP,MySQL,JavaScript, AJAX, JQUERY, Bootstrap and Live Project কোর্সের ৮৭ তম ব্যাচ এবং ২৬ ডিসেম্বর-২০১৭ তারিখ থেকে শুরু হচ্ছে চার মাস ব্যাপী Zend PHP-7 Certified PHP Engineering (Advance PHP) কোর্সের ৩৫ তম ব্যাচ। প্রত্যেকটি কোর্স এর ফী নির্ধারণ করা হয়েছে ৩০,০০০/= আগ্রহীদেরকে অতিসত্বর মাসুদ আলম স্যার এর সাথে যোগাযোগ করতে অনুরোধ করা যাচ্ছে। স্যার এর মোবাইল: 01722 81 75 91

CRUD with PHP and MySQLi using OOP Syntax

[printfriendly]

CRUD with PHP and MySQLi using OOP Syntax

mysqli

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 CRUD Insert Form

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 Data Display Form

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:

PHP MySQLi Update Form

After hitting the edit button the form should look like this:

mysqli crud After Edit

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&amp;id=' + id;

}

}

</script>

</body>

</html>

Delete page look like this:

mysqli crud deleteCRUD 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 five years a number of leadership positions at 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.

6 comments on “CRUD with PHP and MySQLi using OOP Syntax
  1. Great, could you please give me hint about where to implemenmt validattion with a sticky form like this form that i’ve just used 🙂

  2. 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

  3. Hi Justin

    window.location = ‘delete.php?action=delete&id=’ + id;

    should be…

    window.location = ‘delete.php?action=delete&id=’ + id;

Leave a Reply

Your email address will not be published. Required fields are marked *