Step 1. Create Database Class
Filename: class.db.php
Function: This
class will establish the database connection in the system that will be
created.
Code:
<?php
class DBconn{
public $dbconn;
public function __construct(){
$server = 'localhost';
$user ='root';
$pass = '';
$this->dbconn =
mysqli_connect($server,$user,$pass,$dbname);
if (mysqli_connect_errno()){
echo 'Error: Could not connect to database.';
exit;
}else{
//echo 'Connected';
}
}
}
$test = new dbconn();
?>
Discussion:
By default all function in class are automatically assigned as
public. Variable $dbconn will be used as the database connection variable in every class that will be called.
Step 2.
Create Class for Database Functions
Filename: class.dbfunctions.php
Function: This
class will extend the class DBconn
and allow the user to store, retrieve and update data from database.
This will inherit the functionality in class DBconn including the database connection.
Code:
<?php
include 'class.db.php';
class DBfunctions extends DBconn
{
public
function testing(){
echo
"testing";
}
}
$dbopt = new DBfunctions();
$test = $dbopt->testing();
print_r($test);
?>
Discussion:
The public function testing() will test the database connection
commands written in class.db.php
The code $dbopt = new DBfunctions(); will create an object of a new instance of the class DBfunctions.
The code $test = $dbopt->testing(); will store the returned value from the method is called testing in class DBfunctions.
The print_r($test); command will display the value of the variable $test.
Now we are all set with our class dbfunctions and class dbconn connections. We are now ready for the UI page.
Step 3.
Create an index page for the CRUD
Filename: index.php
Function: This
page will serve as the dashboard of the system. This was intentionally created without using bootstrap or UI design to focus on the concept of learning PHP script.
Please create an index.php file from your editor and copy the following commands into your index.
Code:
<?php
include 'class.functions.php';
$db = new DBfunctions();
echo $db->testing();
?>
<!DOCTYPE html>
<html>
<head>
<title>SQLI
OOP::Demo System</title>
</head>
<body>
<!--Save
Data Form-->
<form
action='<?php echo $_SERVER['PHP_SELF'] ?>' method='post'>
<table
border="1" align="center" cellpadding="0">
<th
colspan="2">Userlevel Maintenance</th>
<tr>
<td>Userlevel::</td>
<td><input
type="text" name="ul_name" placeholder="User level
here" required></td>
</tr>
<tr>
<td>Description::</td>
<td><input
type="text" name="ul_desc" placeholder="Description
here" required></td>
</tr>
<tr>
<td
colspan="2" align="center"><input
type="submit" name="submit"
value="Submit"></td>
</tr>
</table>
</form>
</body>
</html>
Discussion:
The first three lines of PHP at the top established the
connection of the different class from class DBconn and class DBfunctions.
$db = new
DBfunctions(); creates a new instance of the class DBfunctions
include
'class.functions.php'; this will make the class.functions.php
become part of the actual index.php codes
echo
$db->testing(); This will print the data in the function testing( ) under
class.functions.php
The rest of the HTML codes, establish the web form elements that
will be used in the different database operation. Web form elements should be
named properly.
<form
action='<?php echo $_SERVER['PHP_SELF'] ?>' method='post'>
The code tell us the submitted data will be processed by itself
using method post. The post method is recommended to use when submitting data
using web forms.
Step 4.
Treat the submitted data in index.php
To capture the submitted data using Web
Forms, you need to insert the following PHP codes after the <body> tag in
index.php.
<?php
if (empty($_POST)===FALSE){
print_r($_POST);
$ul_name = mysql_real_escape_string($_POST['ul_name']);
$ul_desc = mysql_real_escape_string($_POST['ul_desc']);
if (empty($ul_name) || (empty($ul_desc))){
$errors[] = "Please fill in required data to
process.";
}else{
//save data commands
$save = $db->save_userlevel($ul_name,$ul_desc);
if ($save==TRUE){
$success[]="Data successfully saved to
database";
}else{
$errors[] = 'Unable to process data.';
}
}
}
?>
We use
mysql_real_escape_string functions to treat future mysql injection issues.
The code $save =
$db->save_userlevel($ul_name,$ul_desc); will call the class save_userlevel in class.dbfunctions.php with arguments user level
name and user level description.
Do not refresh yet your page as this will prompt an error since we have not created yet the
function save_userlevel under class.dbfunctions.php document.
Step 5.
Create a function that will process the data to database
Insert the following codes in the class.dbfunctions.php after
function testing() to process the submitted data to database.
public function save_userlevel($name,$desc){
if($result =
$this->dbconn->query("SELECT ulevel_name FROM tbl_userlevel WHERE
ulevel_name ='$name'")){
$row_cnt
= $result->num_rows;
if($row_cnt==0){
$sql = mysqli_query($this->dbconn,
"INSERT INTO tbl_userlevel (ulevel_name, ulevel_desc) VALUES
('$name','$desc')") or die (mysqli_connect_error());
return
$sql;
}else{
return FALSE;
}
}
}
To notify
user for the status of database operation, add the code after the <table>
tag.
<tr>
<?php
if (!empty($errors)): ?>
<strong>Oops!</strong><?php
echo implode('', $errors); ?>
<?php endif; ?>
<?php if (!empty($success)): ?><strong>Good!</strong><?php echo implode('', $success); ?><?php endif; ?>
</td>
</tr>
This will prompt the user on the execution of the save_userlevel method of the previous
method being called in class.dbfunctions.php
Step 6.
Display data from database in index.php
Insert the following codes after the </form> tag in
index.php or below the data entry form.
<!--display data from
database-->
<table
align="center">
<?php
$query_userlevel="SELECT * FROM tbl_userlevel";
$resource_userlevel=mysqli_query($db->dbconn,
$query_userlevel);
Print "<th>ID</th>";
Print "<th>Userlevel Name</th>";
Print "<th>Description</th>";
while($result_userlevel=$resource_userlevel->fetch_assoc())
{
Print "<tr>";
$ulevel_id = $result_userlevel['ulevel_id'];
Print "<td>".
$result_userlevel['ulevel_id']. "</td>";
Print "<td>". $result_userlevel['ulevel_name'].
"</td>";
Print "<td>".
$result_userlevel['ulevel_desc']. "</td>";
Print "<td>"."<a href
='index.php?edit=$ulevel_id'>"."Edit"."</a>"."</td>";
Print "</tr>";
}
?>
Discussion:
$resource_userlevel=mysqli_query($db->dbconn,
$query_userlevel); This command will store the data from the submitted SQL query in the variable $resource_userlevel.
while($result_userlevel=$resource_userlevel->fetch_assoc()) This
code will pass the associative array to variable $result_userlevel that will be
display in a table format.
The table will allow the user to easily edit the record by
clicking on Edit link.
Step 7.
Create an “Edit Controls and Web Form Elements”
Rewrite the codes in between the body tag from Step 3 into this.
Do not include the script on submitted $_POST() scripts. eg.if (empty($_POST)===FALSE)
<?php
if (isset($_REQUEST['edit'])){
$ul_id = $_REQUEST['edit'];
$edit
= $db->display_selected_userlevel($ul_id);
?>
<!--Edit
Data Form-->
<form
action="<?php echo $_SERVER['PHP_SELF'];?>?edit=<?php echo
$_GET['edit']; ?>" method="post">
<table align="center">
<tr>
<td
colspan="2">
<?php
if (!empty($errors)): ?>
<strong>Oops!</strong><?php
echo implode('', $errors); ?>
<?php
endif; ?>
</td>
</tr>
<tr>
<td
colspan="2">
<?php
if (!empty($success)): ?>
<strong>Good!</strong><?php
echo implode('', $success); ?>
<?php
endif; ?>
</td>
</tr>
<tr>
<td
colspan="2"><input type="text"
name="ulevel_name" value="<?php echo
$edit['ulevel_name'];?>"></td>
</tr>
<tr>
<td
colspan="2"><input type="text" name="ulevel_desc"
value="<?php echo $edit['ulevel_desc'];?>"></td>
</tr>
<tr>
<td><input
type="submit" name="edit_data" value="Update
Data"></td>
<td><a
href="<?php echo $_SERVER['PHP_SELF']; ?>">Return to
List</a></td>
</tr>
</table>
</form>
<?php
else{
?>
<!--Save
Data Form-->
<form action="index.php"
method="post">
<table
align="center">
<tr>
<td
colspan="2"><?php
if(!empty($errors)){
echo
"<strong>Oops!</strong>";
echo
implode('', $errors);
}
if (!empty($success)){
echo
"<strong>Good!</strong>";
echo
implode('', $success);
}
?>
</td>
</tr>
<tr>
<td>Username:</td>
<td><input
type="text" name="ulevel_name" placeholder="Username
here"></td>
</tr>
<tr>
<td>Password:</td>
<td><input
type="text" name="ulevel_desc"
placeholder="Description here"></td>
</tr>
<tr>
<td><input
type="submit" name="submit"
value="Submit"></td>
</tr>
</table>
</form>
<?php
}
?>
Add the following codes to class.functions.php
to capture the data from database for the Edit Web Form Elements.
public function display_selected_userlevel($ul_id){
$sql=mysqli_query($this->dbconn,"SELECT
* FROM tbl_userlevel WHERE ulevel_id='$ul_id'");
//$row =
mysqli_fetch_array($sql,MYSQLI_ASSOC);
$row =
$sql->fetch_assoc();
return
$row;
}
Discussion: The code has changed to cater the edit
functionality.
if (isset($_REQUEST['edit'])){ This
was set to be the default condition to check if the page is in editing or
on data entry operation. The web form element for saving data was placed under
else statement since the page will load first with no interaction from the
user.
//$row = mysqli_fetch_array($sql,MYSQLI_ASSOC);
$row = $sql->fetch_assoc();
The above-mentioned codes perform the same operation of
retrieving data from database, it only differ on the use of format from OOP and
procedural.
Step 8.
Updating data from Database
Rewrite the codes from the submitted $_POST scripts in index.php from Step 4 into the following
codes below.
<?php
if(empty($_POST)===FALSE){
$ulevel_name
= trim($_POST['ulevel_name']);
$ulevel_desc
= trim($_POST['ulevel_desc']);
if(empty($ulevel_name)
|| empty($ulevel_desc)){
$errors[]="Please
fill in required data to process";
}else{
if
(isset($_GET['edit'])){
$edit
= $db->edit_userlevel($_GET['edit'],$ulevel_name,$ulevel_desc);
if ($edit==TRUE){
$success[]="Successfully
update data in database.";
}else{
$errors[]="Unable
to update data in database.";
}
}else{
$save
= $db->insert_userlevel($ulevel_name,$ulevel_desc);
if($save==TRUE){
$success[]='Successfully
added to database.';//print_r($success);
}else{
$errors[]='Unable
to process database.'; }
}
}
}
?>
Add the following codes to class.functions.php to update the
data in the database from Edit Web Form elements that was submitted by the
user.
public function
edit_userlevel($ul_id,$ul_name,$ul_desc){
$sql=mysqli_query($this->dbconn,"UPDATE
tbl_userlevel SET ulevel_name='$ul_name', ulevel_desc='$ul_desc' WHERE
ulevel_id='$ul_id'") or die (mysqli_connect_error());
if
($sql){
return
$sql;
$sql->close();
}else{
return
FALSE;
}
}
Discussion: This
will now process the data either from Edit Data or Save Data in the system.
This simple system was created for academic purposes of students
taking up IT Elective PHP Class. This is a modified version from MYSQL to MYSQLI CRUD Implementation.
Students: Please leave a comment if you consider this article has been helpful to you. Thank you.
Klase Notes,MYSQLI CRUD Series,PHP,Programming Notes
No comments
Klase Notes,MYSQLI CRUD Series,PHP,Programming Notes
No comments