CRUD operations for a patient database

Question

My code performs CRUD operations in PHP using OOP and dynamic queries. How can I completely separate HTML tables from this code?

Db.php

class Db {

Protected $conn;
public $table;
public $fields;
public $values;
public $sql;
public $array_keys;
public $array_values;


//put your code here
public function __construct() {

    $servername = "localhost";
    $username = "root";
    $password = "abdullah123";

    try {
        $this->conn = new PDO("mysql:host=$servername;dbname=Pathalogy_Lab", $username, $password);
        // set the PDO error mode to exception
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
}

public function tableData($fields, $table) {

    unset($fields['Patient_data']);
    unset($fields['logout']);
    unset($fields['insert_Report']);

    $this->array_keys = array_keys($fields);
    $this->array_values = array_values($fields);

    $this->table = $table;

    $this->sqlQuery();
}

public function tableColums() {
    return "`" . implode("`, `", $this->array_keys) . "`";

}

public function tableValues() {
    return "'" . implode("', '", $this->array_values) . "'";
}

public function sqlQuery() {

    $sql = "INSERT INTO `{$this->table}` (" . $this->tableColums() . ") VALUES (" . $this->tableValues() . ");";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();

    if (Patient == $this->table) {
        $this->addPatient();
    } elseif (Reports == $this->table) {
        $this->addReport();
    }
}


public function addPatient() {

    header('Location: ../FrontEndFiles/insert_report.php');
}

public function addReport() {

    header('Location: ../FrontEndFiles/Admin_Index.php');
}

public function logout() {
    unset($_SESSION["dbuser_name"], $_SESSION["dbpassword"], $_SESSION["dbid"]);
    session_unset();
    Session_destroy();

    if (!isset($_SESSION['dbuser_name']) || !isset($_SESSION['Patient_password']) || !isset($_SESSION['Patient_ID'])) {
        header('Location: ../FrontEndFiles/Index.php');
        exit();
    }
}

}

Admin.php

class Admin extends Db {

public $data_id;

public function listRecord() {

    if (isset($_SESSION['dbuser_name']) && isset($_SESSION['dbpassword'])) {


        $query = "select Patient_ID, Patient_name, Patient_Address, Patient_Phone from Patient where Role= 'u'";
        $result = $this->conn->prepare($query);
        $result->execute(); 

        if ($row_count > 0) {
            // output data of each row

            $this->detailsHeredocs();
            $nust = $result->fetchall();

            foreach ($nust as $row1) {

                echo"       
<tr> 
<td>  $row1[Patient_name]    </td>
<td>  $row1[Patient_Address] </td>

<td><a href='../Admin/AdminData.php?report_id=$row1[Patient_ID]'>Details</a></td>

</tr>";
            }
            echo <<<HTML
            </tbody>
            </table>
            </div>
  <script>
   $(document).ready(function(){
   $('#myTable').DataTable();
  }); 
  </script>
            </body>
            </html>
 HTML;
        }
    }
}

public function Details($id) {

    $this->data_id = $id;

    if (isset($_SESSION['dbuser_name']) && isset($_SESSION['dbpassword'])) {

        $query = "select Report_type, Report_Id, Report_Reults,Patient_name, Patient_Address, "
                . "Patient_Phone from Patient join Reports WHERE "
                . "Reports.Patient_ID=$this->data_id and Patient.Patient_ID=$this->data_id";

        $result = $this->conn->prepare($query);
        $row = $result->execute();
        $row_count = $result->rowCount();

        if ($row_count > 0) {
            // output data of each row
            $this->patientListHeredocs();
            $nust = $result->fetchAll();
            foreach ($nust as $row1) {

                echo"       
                <tr>
                    <td>  $row1[Patient_name]    </td>
                    <td>  $row1[Patient_Address] </td>
                    <td>  $row1[Patient_Phone]   </td>
                    <td>  $row1[Report_type]     </td>
                    <td>  $row1[Report_Reults]   </td>
                          <td><a href='../Admin /AdminData.php?del_id=$row1[Report_Id]'>Delete</a></td>
                </tr>";
            }
            echo <<<HTML
            </tbody>
            </table>
            </div>
 <script>
 $(document).ready(function(){
 $('#myTable').DataTable();
 }); 
 </script>
            </body>
            </html>
HTML;
        }
    }
 }

 public function Delete($del) {

    if (isset($_SESSION['dbuser_name']) && isset($_SESSION['dbpassword'])) {

        $this->data_id = $del;

        $query = "DELETE FROM Reports Where Report_Id=$this->data_id";

        $result = $this->conn->prepare($query);
        $result->execute();
        $affected_rows = $result->rowCount();
        if ($affected_rows == 1) {

            header('Location: ../FrontEndFiles/Admin_Index.php');
        } else {

            echo 'No data DELEted';
        }
    }
 } 

 public function DeletePatient($param) {

    if (isset($_SESSION['dbuser_name']) &&   isset($_SESSION['dbpassword'])) {

        $this->data_id = $param;
        $query = "Delete from Patient WHERE Patient_ID=$this->data_id";
        $result = $this->conn->prepare($query);
        $result->execute();

        header('Location: ../Admin/Admin_Index.php');
    }
}

public function PatientList() {

    if (isset($_SESSION['dbuser_name']) &&       isset($_SESSION['dbpassword'])) {

        $query = "select Patient_ID, Patient_name, Patient_Address, Patient_Phone from Patient where Role= 'u'";

        $result = $this->conn->prepare($query);
        $result->execute();
        $row_count = $result->rowCount();

        if ($row_count > 0) {
            // output data of each row
            ob_start();
            include("../FrontEndFiles/navBar.html");
            include("../FrontEndFiles/sideBar.html");
            $include = ob_get_contents();
            ob_end_clean();

            echo <<<HTML
    {$include}


       <div class="col-md-8"> 
            <h2> Data</h2>
                <table id="myTable" class="display">
                    <thead>
                        <tr>
                            <th>Patient Name</th>
                            <th>Action</th>
                        </tr>
                     </thead>
                        <tbody> 
 HTML;
            $nust = $result->fetchAll();
            foreach ($nust as $row1) {
                    echo"       
                            <tr>
                                <td>  $row1[Patient_name]    </td>     
                                <td><a href='../Admin/AdminData.php?deleteData=$row1[Patient_ID]'>Delete</a></td>
                            </tr>";
                    }
echo <<<HTML
                        </tbody>
                </table>
            </div>
<script>
$(document).ready(function(){
$('#myTable').DataTable();
}); 
</script>
</body>
</html>
HTML;
        }
    }
    }

    public function loginAdmin($login_admin) {

    if (!empty($login_admin[0]) || !empty($login_admin[1])) {

        $query = "SELECT * FROM Patient WHERE Patient_name  =             '$login_admin[0]' and Patient_password = $login_admin[1] and Role = 'a'";
        $stmt = $this->conn->query($query);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        echo $row['Patient_name'];
        echo $row['Patient_password'];
        echo $row['Patient_ID'];

        if ($row) {

            $_SESSION["dbuser_name"] = $row['Patient_name'];
            $_SESSION["dbpassword"] = $row['Patient_password'];
            $_SESSION["dbid"] = $row['Patient_ID'];

            header('Location: ../FrontEndFiles/Admin_Index.php');
        } else {

            header('Location: ../FrontEndFiles/AdminLogin.php');
        }
    } else {
        header('Location: ../FrontEndFiles/AdminLogin.php');
    }
    }

    public function detailsHeredocs() {

    ob_start();
    include("../FrontEndFiles/navBar.html");
    include("../FrontEndFiles/sideBar.html");
    $include = ob_get_contents();
    ob_end_clean();

    echo <<<HTML
  {$include} 

       <div class="col-md-8">  
        <h2>Report's Data</h2>
    <table id="myTable" class="display">
<thead>
    <tr>
        <th>Patient Name</th>
        <th>Patient Address</th>
        <th>Action</th>
    </tr>
</thead>
<tbody>

HTML;
}

public function patientListHeredocs() {
    ob_start();
    include("../FrontEndFiles/navBar.html");
    include("../FrontEndFiles/sideBar.html");
    $include = ob_get_contents();
    ob_end_clean();

    echo <<<HTML
    {$include}

       <div class="col-md-8"> 

        <h2>Data</h2>
    <table id="myTable" class="display">
        <thead>
            <tr>
                <th>Patient Name</th>
                <th>Patient Address</th>
                <th>Patient Phone</th>
                <th>Report Type</th>
                <th>Report Results</th>
                <th>Action</th>
            </tr>
        </thead>
            <tbody>


HTML;
 }

}

what do you mean by completely separate HTML tables from this code? do you not want Tables rendered on your page? — Malachi, Feb 7, 2017 at 15:23
@Malachi The question is about how to organize the code to separate the presentation from the business logic. — 200_success, Feb 7, 2017 at 15:32

Community · Accepted Answer · 2017-04-13 12:40:41Z

I wrote about 'poor-mans MVC' here, but I'll write about it again for you.

Basically, you want to separate your code into 'Models', 'Views' and 'Controllers'. The 'Controller' does the brunt of the PHP work, and it fills a 'Model' with some data. Then the 'Controller' passes the aforementioned 'Model' off to the 'View', which simply displays what is in the 'Model'.

I'll work up some examples later today and tonight if I get time, but your 'View' should have as minimal PHP code as possible. Basically it should just display everything from your 'Model'.

I've gotten an example together in another question on this site. Basically, you should be able to take that code, build the proper structure, and then build your Controller, Model and View objects as necessary.

I wouldn't inherit Db as a class, I would implement it and let it expose a clean API. Inheriting it implies that the Admin class should be used in places where Db would belong, but creating and instance of it is the right way to do it. Then you can build a clean API like: $db->GetData('tableName', 'columnList') which would query tableName for columnList (i.e. *) then return the resultant arrays.

Your current Db/Admin class violate SRP (single-responsibility principle): the idea that any code should have one purpose and one purpose only. The Db class should be responsible for querying and returning data from the DB, not for transforming that data to HTML and such, sending headers, etc. Admin should not inherit Db, because the Admin class is not an extension of Db. It doesn't add or change functionality regarding Db itself, it wraps it with a UI.

Other than that your naming is inconsistent. You have listRecord and Details which are both public functions, I don't know what the PHP standard is but pick a casing scheme and stick with it in that context, it's usually easier to identify/find information in it later.

Seb · Accepted Answer · 2017-02-07 17:08:09Z

Your code does not do what you said it does (My code performs CRUD operations in PHP using OOP and dynamic queries). At least not in the way I would expect it. It does much more. And your way of using OOP just uses the possibility to extend a class. But even your base class DB already does completely not db related stuff (issuing location headers is no db-task, isn't it). And your Admin class does things like getting patient details - what has this to do with admin? Or doing the acutal login for the admin and then redirect - that's not db related again.

I was quite surprised to see a method sqlQuery() do an insert without any params.

Please use a class for a single thing. If you want to do redirects in a class, then create an extra class that is only used for the redirects.

What your question sounds like is more like using a MVC-scheme. Model (= database), view (= html code) and controller (= logic to connect model and view). There are good frameworks out there, that would definitly help you to do it. Or you can do it yourself.

Some basic structure you might use - if you do not want a MVC framework:

Database-Classes:

IMPORTANT: no html code and no redirects in these classes. ONLY db related stuff!

abstract class DB - common database things like connection pooling, getting the database connection, basic query functionality - but in no way for a specific table
class Admin extends DB - get admin credentials
class Patient extends DB - all patient related stuff
class PatientRecords extends DB - the PatientRecords

Might have missed a few other tables.

And then you have php files for each request (or a single index.php that handles all requests). In this file(s) you need to load the appropriate database table model and do the request. The output is then in these php files or in even other files you might include. You might want some helper classes, like checking if the user is logged in.

But in any way you project sounds like you should use a MVC framework.

Stack Exchange Network

CRUD operations for a patient database

2 Answers 2

Not the answer you're looking for? Browse other questions tagged
php
object-oriented
html
mysql
crud
or ask your own question.

Linked

Hot Network Questions

CRUD operations for a patient database

2 Answers 2

Not the answer you're looking for? Browse other questions tagged phpobject-orientedhtmlmysqlcrud or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
php
object-oriented
html
mysql
crud
or ask your own question.