The use of computers today is very
important compared than before every transactions being performed is being done
using a computer. If we are very
observant enough when we to a super
market and purchase some foods when we pay for the foods he have selected the
cashier uses barcode scanner and a computer with a Point of Sale program
installed on it. It makes the transaction more efficient , accurate and more
faster because all the information for each item be purchase is being stored in
database system that can be easily retrieve and performs a series of
computation needed for that particular transaction. On the part of the supermarket the use of this
can of application program Point of Sale for example is also beneficial because
they can monitor the products that are saleable to the consumer and they can
also perform inventory of their products more effectively this approach can
save big amount of time, manpower and money to the part of the owner of the
super market. It can also help them maximize losses because they can check each
products they sold accurately compared to the use of manual cash register
method that is prone for errors and time consuming also if they will conduct
inventory of their stocks day to day.
In this article I will show you how to
build a database application using PHP and MySQL but this time we will using
object oriented approach in creating our database application we will using the
mysqli connection in PHP and MySQL. When
I started programming in PHP and MySQL way back in 2006 most of my work in
database development is using the procedural approach of programming. I will
start from the top to bottom until I finish writing the application. Using this
approach I was able to creating a bunch of different database applications for
different customers and clients that I have. However this approach is not good
if you are working with other programmers that uses object oriented approach in
programming why because your code is not easy to maintain and you can’t use
code libraries that is written in object oriented approach. Basically if you ask me what are the benefits
of using object oriented approach in programming basically in my own experience
in Java application development the biggest advantage of OOP approach is code
usability it means when we write or use the code written by other programmers
it is very easy to use. Another advantage of using OOP as an approach in
programming is that it is easier for us to modify and maintain the existing
code that we have compared to its counterpart procedural programming. For your
information object oriented programming started with the programming language
called Simula a very popular programming language in Europe. To give you some
idea working with object oriented programming we are dealing with objects. An
object is the things that we can see in our environment it can be a thing,
living things such as car, person, tools.
Every object has an attribute which refers to the physical
characteristic of the a particular object for an example a car the color of the
car is red, it has size, weight this is called attributes. On the other hand
methods are the actions that is being performed by an object for a car is can
move forward, backward and stop it means the method is an action performed by
the object that is the car for instance.
The program that I will represent in this
article is Students Information System using Object Oriented Approach. Before
that I would like to mention that some portion of the code that I use in my program
is based on the work
of my Mr.
Mike Dalisay in his article about CRUD application using PHP and MySQLi
here is his website http://www.codeofaninja.com he is also a fellow Filipino
software developer like me. You can
visit his website to learn more about web programming. Student Information System is a application
program that I wrote in order for the school to store and manage the records of
their students enrolled on their different program in their school. The program will accept the first name, last
name, course, birthday, year and section
where the student belongs and the email address that can be used by the school
to contact the student. I also include an SQL dumb file for easy creation of
database, table and a copy of sample records. There are still many things that
you can improve this program you can all print function and login to secure
your application against intruders.
The first thing that we do is to create a
database connection below is a the code that will performed such operation.
db_connect.php
<?php
$db_host
= "localhost";
$db_username
= "root";
$db_password
= "";
$db_name
= "info";
$mysqli
= new mysqli($db_host, $db_username, $db_password, $db_name);
if(mysqli_connect_errno())
{
echo "Error: Could not
connect to database.";
exit;
}
?>
In
this code we use already an object oriented approach in creating an object with this command $mysqli = new mysqli($db_host, $db_username, $db_password, $db_name); by using the keyword new we care creating a
connection of mysqli. The term mysqli mean I means improved version of
mysql_connect() function in mysql. The next portion of our code is the
index.php in this portion we will fetch the records from our database info and
the table students to be displayed in the screen as a list of records. The user
in this portion of our code has the ability to update our records or delete our
records from the database.
Index.php
<html>
<head>
<title>
Students Information System
</title>
<style stype="text/css">
body {
font-size : 1.5em;
font-family: Arial, Helvetica, sans-serif;
color
: black;
background-image:url("sky.jpg");
}
</style>
<body>
<br>
<hr>
<font color="red">
<h2 align="center">
Students Information System </h2>
</font>
<hr>
<?php
include 'db_connect.php';
$action = isset($_GET['action']) ?
$_GET['action'] : "";
if($action=='delete'){
$sql
= "DELETE FROM students WHERE id = ?";
if($stmt
= $mysqli->prepare($sql)){
$stmt->bind_param("i",
$_GET['id']);
if($stmt->execute()){
echo "<br> <center>";
echo
"Record has been deleted. </center>";
$stmt->close();
}else{
die("Unable
to delete.");
}
}
}
$query = "select * from
students";
$result = $mysqli->query( $query );
$num_results = $result->num_rows;
if( $num_results ){
echo
"<br><br>";
echo
"<table border='2' align='center'>";
echo
"<tr>";
echo
"<th> <font color='red'>
Firstname</th></font>";
echo
"<th><font color='red'> Lastname</th</font>";
echo
"<th><font color='red'> Course</th</font>";
echo
"<th><font color='red'>
Birthday</th></font>";
echo
"<th><font
color='red'>Year/Section</th></font>";
echo
"<th><font color='red'>Email</th></font>";
echo
"<th><font color='red'>Record Options</th>
</font>";
echo
"</tr>";
while(
$row = $result->fetch_assoc() ){
extract($row);
echo
"<tr>";
echo
"<td> <center> {$firstname} </center></td>";
echo
"<td> <center> {$lastname} </center></td>";
echo
"<td> <center> {$course} </center></td>";
echo
"<td> <center> {$bday} </center></td>";
echo
"<td> <center>{$year_section}
</center></td>";
echo
"<td> <center>{$email} </center></td>";
echo
"<td>";
echo
"<a href='edit.php?id={$id}'>Update Record</a>";
echo
" / ";
echo
"<a href='#' onclick='delete_user( {$id} );'>Delete
Record</a>";
echo
"</td>";
echo
"</tr>";
}
echo
"</table>";
}
else{
echo "<br><br> <center>";
echo
"No More Record(s) Found in the Database.";
echo
"</center>";
}
echo "<p
align='center'><br><br>";
echo "<a href='add.php' title='Click
here to add a record in the database'>
ADD NEW RECORD</a> </p>";
$result->free();
$mysqli->close();
?>
<script type='text/javascript'>
function delete_user( id ){
var
answer = confirm('Are you sure?');
if
( answer ){
window.location
= 'index.php?action=delete&id=' + id;
}
}
</script>
</body>
</html>
The third portion of our code is to add a
record in our table students by the end
user of our program. Kindly see the listing below there are some important
comments to be read because you can understand how the mysqli works in our
program.
add.php
<html>
<head>
<title>
Students Information System
</title>
<style stype="text/css">
body {
font-size : 1.5em;
font-family: Arial, Helvetica, sans-serif;
color
: black;
background-image:url("sky.jpg");
}
</style>
<?php
error_reporting(0);
if($_POST){
include
'db_connect.php';
$sql
= "INSERT INTO
students
(firstname, lastname, course, bday,year_section,email)
VALUES
(?,
?, ?, ?, ?, ?)";
if($stmt
= $mysqli->prepare($sql) ){
/*
* Binding of the values,
* "sssssss" means 6 strings were
being binded,
* aside from s for string, you can also use:
* i for integer
* d for decimal
* b for blob
*/
$stmt->bind_param(
"ssssss",
$_POST['firstname'],
$_POST['lastname'],
$_POST['course'],
$_POST['bday'],
$_POST['year_section'],
$_POST['email']
);
if($stmt->execute()){
$message =
"<br><br> <font color='red'> <center>
Student Record has been save in the database. </center>";
$stmt->close();
}else{
die("
Sorry unable to save record in the database. Please contact your
Programmer.");
}
}else{
die("Unable
to prepare statement.");
}
$mysqli->close();
}
?>
<center>
<br>
<hr> <font
color="red">
<h2> <center> ADD STUDENT
RECORDS </center> </h2>
<hr>
<body>
<form action='add.php' method='post'
border='0'>
<table
border="2" align="center">
<tr>
<td>Firstname</td>
<td><input
type='text' name='firstname'size="50" autofocus /></td>
</tr>
<tr>
<td>Lastname</td>
<td><input
type='text' name='lastname' size="50" /></td>
</tr>
<tr>
<td>Course</td>
<td><input type='text' name='course' size="50" /></td>
</tr>
<tr>
<td>Birthday</td>
<td><input type='text' name='bday' /></td>
</tr>
<tr>
<td>Year and Section</td>
<td><input type='text' name='year_section' /></td>
</tr>
<tr>
<td>Email</td>
<td><input type='text' name='email' /></td>
</tr>
<td></td>
<td>
<input
type='submit' value='Save Record' title="Click here to save record in the
database." />
<a
href='index.php' title="Click here to return to the main
page">Return to Main Page</a>
</td>
</table>
<?php echo $message; ?>
</form>
</center>
</body>
</html>
One of the things that I have learned in
writing this program your variable name that can be found in your ><input type='text' name='course'
size="50" /> just like this
one must be the same in terms of variable name declaration in your table which
referring to your fields. If you are not following the rules you will be
surprised to find out your code is not working properly. That is one of the
things to be remembered in writing an object oriented approach in programming
you must be precise all the time.
The last part of our code is the edit.php
which performs changing of data values from our table. After the user make some
changes he or she can return to the main page of our program to see the changes
that has being made. Below is the complete program listing of our edit.php.
edit.php
<html>
<title>
College Students Information System
</title>
<style stype="text/css">
body {
font-size : 1.5em;
font-family: Arial, Helvetica, sans-serif;
color
: black;
background-image:url("sky.jpg");
}
</style>
<body>
<?php
include 'db_connect.php';
if($_POST){
$sql
= "UPDATE
students
SET
firstname
= ?,
lastname
= ?,
course
= ?,
bday = ?,
year_section
= ?,
email
= ?
WHERE
id=
?";
$stmt
= $mysqli->prepare($sql);
$stmt->bind_param(
'ssssssi',
$_POST['firstname'],
$_POST['lastname'],
$_POST['course'],
$_POST['bday'],
$_POST['year_section'],
$_POST['email'],
$_POST['id']
);
if($stmt->execute()){
echo
"Student record is updated in the database ";
$stmt->close();
}else{
die("Sorry
unable to update the record in the database");
}
}
$sql = "SELECT
id,
firstname, lastname, course, bday, year_section, email
FROM
students
WHERE
id
= \"" . $mysqli->real_escape_string($_GET['id']) . "\"
LIMIT
0,1";
$result = $mysqli->query( $sql );
$row = $result->fetch_assoc();
extract($row);
$result->free();
$mysqli->close();
?>
<br>
<font color="red">
<hr>
<h2> <center> UPDATE STUDENT
RECORDS </center> </h2>
<hr>
<br>
</font>
<form action='edit.php?id=<?php echo
$id; ?>' method='post' border='0'>
<table border="2" align="center">
<tr>
<td>Firstname</td>
<td><input type='text' name='firstname'
size="50" autofocus
value='<?php echo $firstname; ?>'
/></td>
</tr>
<tr>
<td>Lastname</td>
<td><input type='text' name='lastname' size="50"
value='<?php echo $lastname; ?>'
/></td>
</tr>
<tr>
<td>Course</td>
<td><input type='text' name='course' value='<?php echo $course; ?>' size="50" /></td>
</tr>
<tr>
<td>Birthday</td>
<td><input type='text' name='bday' value='<?php echo $bday; ?>' /></td>
</tr>
<tr>
<td>Year and Section</td>
<td><input type='text' name='year_section' value='<?php echo $year_section; ?>' /></td>
</tr>
<tr>
<td>Email</td>
<td><input type='text' name='email' value='<?php echo $email; ?>' /></td>
</tr>
<td></td>
<td>
<input type='hidden'
name='id' value='<?php echo $id ?>' />
<input type='submit'
value='Update Record' title="Click here to update the record in the
datbase" />
<a
href='index.php' title="Click here to return to the main
page">Return to Main Page </a>
</td>
</tr>
</table>
</form>
</body>
</html>
This application program that I wrote is
not very difficult is you know how to use object oriented approach in creating
database application in PHP and MySQL using the mysqli connection approach. I
hope you learn something out of it. If
you have some comments, suggestions and questions about this article you can
email me. I am very glad to answer your questions thank you very much for
always supporting this website.
Happy Productive Programming Everyone.
If you have some questions please send me an email at jake.r.pomperada@gmail.com and jakerpomperada@yahoo.com. My mobile number here in the Philippines is 09173084360.
Sample Program Output
