A complete database class in PHP

In this code, we present a database class that can be easily used to perform any operation on database, e.g., select, insert, update, delete, etc.

The following is the code of database class:

filename: include/functions.php

<?php
include("db/opendb.php");
$msg = NULL;

/// STARTING CODE OF Database Class /////////
class Database
{
  public $pstmt=NULL;
  
  function FetchRecord($query)
  {
      
      global $conn;
  
    $this->pstmt = $conn -> prepare($query);
    $this->pstmt -> execute();
  
  }
  
  
  function UpdateRecord($query, $operation)
  {
    global $conn;
    $msg = NULL;
    $stmt = $conn->prepare($query);	

    try
    {
      $stmt->execute();
      $msg = "Record " . $operation;
    }
    catch(PDOException $e)
    {
    $msg = $e -> getMessage();	
    }
    
  
  return $msg;
  
  }
  
  function CloseDBCon()
  {
    $conn = NULL;
  }
}
//============= Database Class END ==================

Following are the usage examples of the class:

Insert Operation:

insert.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>
</head>

<body>
<?php
include("navbar.php");
include("include/functions.php");
$msg = NULL;
$dbobj = new Database();

if(isset($_POST['btnsubmit']) )
{
  $id = htmlspecialchars(addslashes($_POST['txtstudentid'])); // its better to put ID in hidden field rather than readonly field
  $name = htmlspecialchars(addslashes(trim($_POST['txtname'])));
  $passwd = htmlspecialchars(addslashes(trim($_POST['txtpasswd'])));

  $query = "insert into student(StudentID, StudentName, StudentPass) values ('$name', '$passwd','$id')";	

  $msg = $dbobj -> UpdateRecord($query, "Inserted");
}

?>

<form id="form1" name="form1" method="post" action="">
  <table width="200" border="1">
    <tr>
      <td>StudentID</td>
      <td>
      <input type="text" name="txtstudentid"  id="txtstudentid"  /></td>
    </tr>
    <tr>
      <td>Name</td>
      <td><input type="text" name="txtname" id="txtname"   /></td>
    </tr>
    <tr>
      <td>Password</td>
      <td><input type="text" name="txtpasswd" id="txtpasswd"   /></td>
    </tr>
    <tr>
      <td><input type="submit" name="btnsubmit" id="btnsubmit" value="Submit" /></td>
      <td>&nbsp;</td>
    </tr>
  
    <tr>
      <td colspan="2"><?php echo $msg; ?></td>
      
    </tr>
  </table>
</form>
<?php 
$dbobj -> CloseDBCon();
?>
</body>
</html>

Edit / Update Operation:

edit.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>Edit</title>
</head>

<body>
<?php
include("navbar.php");
include("include/functions.php");
$msg = NULL;

$id = htmlspecialchars(addslashes(trim($_GET['id'])));

$dbobj = new Database();
$name="";
$passwd="";

if(isset($_POST['btnsubmit']) )
{
  $id = $_POST['txtstudentid']; // its better to put ID in hidden field rather than readonly field
  $name = htmlspecialchars(addslashes(trim($_POST['txtname'])));
  $passwd = htmlspecialchars(addslashes(trim($_POST['txtpasswd'])));

  $query = "update student set StudentName='$name', StudentPass='$passwd' where StudentID='$id'";	

  $msg = $dbobj -> UpdateRecord($query, "Updated");
}

$dbobj -> FetchRecord("SELECT StudentID, StudentName, StudentPass FROM student where StudentID='$id'");

while($row = $dbobj->pstmt->fetch())
    {
      $id = $row[0];
      $name = $row[1];
      $passwd = $row[2];
    }

?>

<form id="form1" name="form1" method="post" action="">
  <table width="200" border="1">
    <tr>
      <td>StudentID</td>
      <td>
      <input type="text" name="txtstudentid" readonly="readonly" id="txtstudentid" value="<?php echo $id; ?>" /></td>
    </tr>
    <tr>
      <td>Name</td>
      <td><input type="text" name="txtname" id="txtname" value="<?php echo $name ?>"  /></td>
    </tr>
    <tr>
      <td>Password</td>
      <td><input type="text" name="txtpasswd" id="txtpasswd" value="<?php echo $passwd; ?>"  /></td>
    </tr>
    <tr>
      <td><input type="submit" name="btnsubmit" id="btnsubmit" value="Submit" /></td>
      <td>&nbsp;</td>
    </tr>
  
    <tr>
      <td colspan="2"><?php echo $msg; ?></td>
      
    </tr>
  </table>
</form>
<?php 
$dbobj -> CloseDBCon();
?>
</body>
</html>

Delete Operation

delete.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>
</head>

<body>
<?php
include("include/functions.php");
$msg = NULL;

$dbobj = new Database();
$id = htmlspecialchars(addslashes(trim($_GET['id'])));
$query = "delete from student where StudentID='$id'";	
$msg = $dbobj -> UpdateRecord($query, "Deleted");
$dbobj -> CloseDBCon();

echo "<script language=\"javascript\" type=\"text/javascript\">window.location.href=\"list.php?msg=$msg\";</script>";
?>
</body>
</html>

View single record

view.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>
</head>

<body>
<?php
include("navbar.php");
include("include/functions.php");
$msg = NULL;

$id = htmlspecialchars(addslashes(trim($_GET['id'])));

$dbobj = new Database();


$dbobj -> FetchRecord("SELECT StudentID, StudentName, StudentPass FROM student where StudentID='$id'");
while($row = $dbobj->pstmt->fetch())
    {
      $name  = $row[1];
      $passwd = $row[2];
    }
?>


  <table width="200" border="1">
    <tr>
      <td>StudentID</td>
      <td><?php echo $id;  ?></td>
    </tr>
    <tr>
      <td>Name</td>
      <td><?php echo $name; ?></td>
    </tr>
    <tr>
      <td>Password</td>
      <td><?php echo $passwd; ?></td>
    </tr>
    <tr>
      <td>
    <input type="button" name="btnsubmit" id="btnsubmit" value="Edit" onclick="window.location.href='edit.php?id=<?php echo $id; ?>';" />
    <input type="button" name="btnback" id="btnback" value="Back" onclick="window.location.href='list.php';" />
    </td>
      <td>&nbsp;</td>
    </tr>
  
    <tr>
      <td colspan="2"><?php echo $msg; ?></td>
      
    </tr>
  </table>

<?php 
$dbobj -> CloseDBCon();
?>
</body>
</html>

View multiple records

list.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>
</head>

<body>
<?php
include("navbar.php");
include("include/functions.php");
$msg = NULL;



$dbobj = new Database();

$dbobj -> FetchRecord("SELECT StudentID, StudentName, StudentPass FROM student");

echo isset($_GET['msg'])?$_GET['msg']:"";
echo "<br>";
?>

<table border="1">
<tr>
<th></th>
<th></th>
<th></th>
<th>StudentID</th>
<th>StudentName</th>
<th>StudentPass</th>
</tr>
<?php 
    while($row = $dbobj->pstmt->fetch())
    {	
  ?>
  <tr>
         <td><a href="view.php?id=<?php echo $row[0]; ?>" >view</a></td>
       <td><a href="edit.php?id=<?php echo $row[0]; ?>" >edit</a></td>
       <td><a href="delete.php?id=<?php echo $row[0]; ?>" onclick="return confirm('delete record?');" >delete</a></td>
       <td><?php echo $row[0]; ?></td>
           <td><?php echo $row[1]; ?></td>	
           <td><?php echo $row[2]; ?></td>			   
           
  </tr>
  <?php
}

?>

</table>

<?php 
$dbobj -> CloseDBCon();
?>
</body>
</html>

navigation menu

navbar.php

<html>
<body>
<a href="list.php">LIST</a>&nbsp | &nbsp;
<a href="insert.php">INSERT</a>
<br><br>
</body>
</html>

Database File:

student.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `studentsys`
--

-- --------------------------------------------------------

--
-- Table structure for table `student`
--

CREATE TABLE `student` (
  `StudentID` varchar(4) NOT NULL,
  `StudentName` text NOT NULL,
  `StudentPass` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`StudentID`, `StudentName`, `StudentPass`) VALUES
('s1', 'Osman', '123'),
('s2', 'Kamal', '223'),
('s4', 'Shahid', 'def');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `student`
--
ALTER TABLE `student`
  ADD PRIMARY KEY (`StudentID`);

The complete example can be downloaded from THIS LINK

 

One Comment

Add a Comment

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