Trying to delete a row from a table

Question

At the moment I am displaying in a table all the patients that are registered to a health centre. I have added a delete button or delete link that will remove the patient from the table. When I click on the delete button I am getting an error message and all of the patients that were previously displayed are gone and the 'all patient view' page now echoes out '0 results'.
If someone could help me resolve the issue so that I can remove a patient from the table that would be much appreciated.

Error Message

Warning: main(): Couldn't fetch mysqli_result in E:\WebProgrammingAssignment\views\AllPatientsView.php on line 48

UPDATED Register Patient Model

<?php
require_once('DAO.php');

class RegisterPatientModel extends DAO
{

    protected $target = "patient";

    public function __construct()
    {
        parent::__construct();
    }

    public function insertPatient($firstname, $lastname, $patstreet, $patcity, $patpostcode, $patphone, $doctorid, $dob)
    {
        $firstname = parent::escape($firstname);
        $empnin = parent::escape($lastname);
        $patstreet = parent::escape($patstreet);
        $patcity = parent::escape($patcity);
        $patpostcode = parent::escape($patpostcode);
        $sql = "INSERT INTO {$this->target} (`firstname`, `lastname`, `patstreet`, `patcity`, `patpostcode`, `patphone`, `doctorid`, `dob`) VALUES ('{$firstname}', '{$lastname}', '{$patstreet}', '{$patcity}', '{$patpostcode}', '{$patphone}', '{$doctorid}', '{$dob}');";
        return parent::query($sql);
    }

       public function deletePatient($patientid)
{
$sql = "DELETE
    FROM {$this->target}
    WHERE patientid='{$patientid}'";

return parent::query($sql);
}
    function getAllPatients()
    {
        $sql = "SELECT a.patientid, 
       concat(d.firstname, ' ', d.lastname) as fullname_doctor,
       a.firstname, a.lastname, a.patstreet, a.patcity, a.patpostcode, a.patphone, a.dob
       FROM patient as a
       INNER JOIN doctor as d
       on a.doctorid = d.doctorid;";
        return parent::query($sql);
    }
}

?>

UPDATE All Patient View

<html>
<tr>
<td colspan="5" align="center"> 

  <div id="boxalign2" class="boxalign2">
    <div class="inputwrap">


<br>
<table id="customers" width="900" border="1" cellspacing="0" cellpadding="1">
    <tr align="center">
      <td bgcolor="#008000">Patient ID</td>
      <td bgcolor="#008000">Doctor Name</td>
      <td bgcolor="#008000">First Name</td>
      <td bgcolor="#008000">Last Name</td>
        <td bgcolor="#008000">Street</td>
        <td bgcolor="#008000">City</td>
        <td bgcolor="#008000">Post Code</td>
        <td bgcolor="#008000">Telephone</td>
        <td bgcolor="#008000">DOB</td>        
      </tr>
    <?php
$allpatients = $_SESSION['patients'];
if ($allpatients->num_rows > 0) {
    while ($row = $allpatients->fetch_assoc()) {

        echo "<td>" . $row["patientid"] . "</td>";
        echo "<td>" . $row["fullname_doctor"] . "</td>";
        echo "<td>" . $row["firstname"] . "</td>";
        echo "<td>" . $row["lastname"] . "</td>";
        echo "<td>" . $row["patstreet"] . "</td>";
        echo "<td>" . $row["patcity"] . "</td>";
        echo "<td>" . $row["patpostcode"] . "</td>";
        echo "<td>" . $row["patphone"] . "</td>";
        echo "<td>" . $row["dob"] . "</td>";
        echo "<td><a href='../controllers/ViewAllPatientsController.php?patientid=" . $row['patientid'] . "'>Delete</a></td>";


        echo "</tr>";
    }
} else {
    echo "0 results.";
}

?>

UPDATED View Patient Controller

<?php

session_start();
require_once("../models/RegisterPatientModel.php");


$vapc = new RegisterPatientModel;

if (isset($_GET['patientid']))  {
$vapc->deletePatient($_GET['patientid']); 
}
$allpatients = $vapc->getAllPatients();

require_once("../views/AllPatientsView.php");

how many data rows do you expect to delete for a patient? 1? 2? 3? — Martin, May 03 '17 at 21:18
If your code is live code it's appauling, you are at risk from database manipulation and you carry out no checking of the values before putting them into the SQL, you really should look up [Prepared Statements](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and actually use them. — Martin, May 03 '17 at 21:20
For deleting one row, I **strongly** suggest you add a `LIMIT 1` to your delete SQL,so you don't inadvertantly delete all records. Or even more records than you want to — Martin, May 03 '17 at 21:21
Are you ICO compliant - or compliant with whatever body operates in your particular jurisdiction? — Strawberry, May 04 '17 at 12:55
I'm voting to close this question as off-topic because you probably shouldn't be in charge of patient data. — Strawberry, May 04 '17 at 12:56
@Strawberry What I am doing is just for a University assignment. — Matthew, May 04 '17 at 15:10
@Strawberry The patient data is not of actual people and just some information that I have made up. What I am building is part of my assignment specification and the work will be demoed to a lecturer. It is not for commercial use. Please do not close this question as I have had positive answers that have helped towards my work. Thanks. — Matthew, May 04 '17 at 15:16

score 2 · Accepted Answer · edited May 23 '17 at 12:17

2

try this:

public function deletePatient($patientid)
{
    $sql = "DELETE
        FROM {$this->target}
        WHERE patientid='{$patientid}'";

    echo $sql;
    die();

    return parent::query($sql);
}

You will be able to figure out if the SQL query is getting created correctly.

Obligatory warning: this is not a good way to make queries.

See How can I prevent SQL injection in PHP?

Update the 1st

Since deletePatient() is not firing, the issue must be earlier in the source.

Try removing

require_once("../views/AllPatientsView.php");

from DeletePatientController.php

Update the 2nd

You are not using $_POST, so remove

if (isset($_POST["Delete"])) {

And use $_GET instead to access the patientid

Update the 3rd

if (isset($_GET['patientid']))  {
    $patientid->{$_GET['patientid']}();
}

$rpms->deletePatient($patientid);

Should be

if (isset($_GET['patientid']))  {
    $rpms->deletePatient($_GET['patientid']); 
}

Update the 4th

Since the query is not the source of the issue, remove the debug statements:

public function deletePatient($patientid)
{
    $sql = "DELETE
        FROM {$this->target}
        WHERE patientid='{$patientid}'";

    return parent::query($sql);
}

Update the 5th

Since the delete itself is not part of the issue, your next step is to debug the other parts of the code.

$allpatients = $vapc->getAllPatients();

$_SESSION['patients'] = $allpatients;

...

$allpatients = $_SESSION['patients'];

The $_SESSION shenanigans are not needed. Remove $_SESSION['patients'] = $allpatients; and $allpatients = $_SESSION['patients'];.

Then, change

if ($allpatients->num_rows > 0) {

to

var_dump($allpatients);
die();
if ($allpatients->num_rows > 0) {

and see if that leads anywhere. You should see some kind of DAO object or mysql_result (whatever parent::query() returns).

Quick Tip

You can remove the last ?> from .php files. This will save you from some weird Header bugs down the line with trailing line breaks in your outputs.

edited May 23 '17 at 12:17

Community

1
1

answered May 03 '17 at 21:20

willoller

7,106
1
35
63

I am using mysql_real_escape_string. It is included in my DAO page but I haven't added that onto here. – Matthew May 03 '17 at 21:27
The query statement isn't working. Same error is appearing and it isn't echoing out the $sql – Matthew May 03 '17 at 21:28
@Matthew I hope you have [PHP errors turned on](http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display)? – Martin May 03 '17 at 21:29
@Matthew your comment sounds like you've not used willoller's code, as that code clearly has a `die` statement in it so the query should never execute. – Martin May 03 '17 at 21:30
@Matthew You will probably have to populate some patients in the db to get that code to work. It will fire when you click a Delete Button again. – willoller May 03 '17 at 21:31
@willoller The db is already populated, I have since added some more patients into the db and tried the delete button again. Same error as before is appearing. – Matthew May 03 '17 at 21:36
@willoller I have removed the require_once("../views/AllPatientsView.php"); from the DeletePatientController and now when I press delete it just loads a blank page – Matthew May 03 '17 at 21:48
@willoller That makes more sense. It is now echoing the query statement but it is not being executed and the same error message is still being displayed. – Matthew May 03 '17 at 22:15
Yeah the die() happens before the query runs. It's to show you what SQL *would* fire, to debug if the query is the source of the problem. Try removing the `echo` and `die` statements now. – willoller May 03 '17 at 22:57
@willoller So I am now able to delete patients. However, that error message from before is still showing ? – Matthew May 03 '17 at 23:21

Trying to delete a row from a table

1 Answers1