Display data groupwise in php

[dishadcruze]

I am developing a script for hospital.

I have wards like General, Delux, ICU etc. Each ward can have number of patients. Each nurse will be allocated to some wards in weekly basis. Now I want to display for the nurse that how many patients are there in the ward which is allocated to her/him on that week.

Here is my wards table

Here is my ward allotment for each nurse

 

And patient admitted details i get from admission table, which is like this

 

Now i want to show result like this

From 16/09/2019 to 22/09/2019
 

General Ward

Patient           bed No       shift
HSSC022      3                 1

ICU 
HSSC014      1                 1    

I have written query like this, but it doesn't show this way

$firstday = date('Y-m-d', strtotime("this week monday")); 
$lastday =  date("Y-m-d", strtotime("this week sunday"));


 "SELECT wa.sdate, DAYNAME(wa.sdate) AS Day, wa.shift, w.ward_name, a.patient_id, a.ward, p.patient_name FROM ward_allotment wa INNER JOIN admission a ON wa.ward=a.ward INNER JOIN patient p ON a.patient_id=p.patient_id INNER JOIN wards w ON wa.ward=w.wid WHERE wa.week=".$_SESSION['Cweek']." AND a.from_date>='".$firstday."' AND a.discharge_date='0000-00-00' AND wa.nurse=".$_SESSION['user_id']." GROUP BY a.patient_id";

Can somebody help me how to do it.

 

 

[Barand]

Thanks for the sample test data. I tried loading those images into my database, so I could test queries prior to posting back,  but can't get queries to work on them.

Any chance you could post a dump of the data in those tables, or at least provide the data in usable text form?

[Barand]

I find the easiest way for this type of report is to store the data in a structured array as you process the query results. The array structure should reflect the report structure. EG

Array
(
    [Week 38 Thursday 19/09/2019] => Array
        (
            [shift] => 1
            [ward] => ICU
            [patients] => Array
                (
                    [0] => Array
                        (
                            [bed] => 1
                            [id] => HSSC014
                            [name] => Patient E
                        )

                    [1] => Array
                        (
                            [bed] => 3
                            [id] => HSSC019
                            [name] => Patient B
                        )

                    [2] => Array
                        (
                            [bed] => 6
                            [id] => 3bb2dc
                            [name] => Patient J
                        )

                )

        )

It is then just a matter of looping through the arrays to produce the desired output.

HINT: You want to show patients on each day where that date is between the patients admission date and discharge date (ie.. the patient is there). It makes the logic much simpler, therefore, if unknown discharge dates (sometime in the future) are set to the "infinity date" (9999-12-31)

+-----+------------+------+------+------------+----------------+-------+
| aid | patient_id | ward | bed  | from_date  | discharge_date | notes |
+-----+------------+------+------+------------+----------------+-------+
|   8 | 3bb2dc     |    7 |    6 | 2019-09-19 | 2019-09-22     | NULL  |
|   9 | HSSC018    |    5 |    1 | 2019-09-19 | 9999-12-31     | NULL  |  <-- discharge date not yet known
+-----+------------+------+------+------------+----------------+-------+

This code builds the array from the query

Now you just have to loop through the array with a couple of nested foreach() loops to output, like this ...

$tdata = '';
foreach ($data as $dt => $ddata) {
    $tdata .= "<tr><th class='day'>$dt</th><th class='day' colspan='5'>&nbsp;</th></tr>
               <tr><td>&nbsp;</td><td class='ca'>{$ddata['shift']}</td><td>{$ddata['ward']}</td><td colspan='3'>&nbsp;</td></tr>\n";
    foreach ($ddata['patients'] as $p) {
        $tdata .= "<tr><td colspan='3'>&nbsp;</td><td class='ca'>{$p['bed']}</td><td>{$p['id']}</td><td>{$p['name']}</td></tr>\n";
    }
}                        
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Sample</title>
<style type="text/css">
    body    { font-family: verdana,sans-serif; font-size: 12pt; padding: 20px 50px; }
    th      { padding: 16px; text-align: left; background-color: #396; color: #FFF; }
    th.day  { background-color: #EEE; color: black; }
    td      { padding: 8px 16px; }
    .ca     { text-align: center; }
</style>
</head>
<body>
    <table>
        <tr><th>Date</th><th>Shift</th><th>Ward</th><th>Bed</th><th colspan="2">Patient</th></tr>
        <?=$tdata?>
    </table>
</body>
</html>

Results

Edited September 19, 2019 by Barand

[dishadcruze]

Thanks @Barand

But i couldn't solve 'Fatal error: Uncaught Error: Call to a member function execute() on boolean' in the query as am very well-versed with PDO. 

Here is the screenshot of the query where it says its a error.

 

 

[chhorn]

If you get the error "call to a member function xy on bool" in context of database queries you can (mostly) think of an error within your sql statement. On your development machine you should always enable throwing errors:

$pdo = new PDO('mysql:host=localhost;dbname=someTable', 'username', 'password', [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

[Fabel]

 

try 

WHERE wa.nurse=?

 

or you can go with:

WHERE wa.nurse=:nid

$stmt->bindParam(":nid", $nid);
$stmt->execute(); 

@Barand already showed you this and he showed you how to make your query beter readable:

Edited September 20, 2019 by Fabel

[dishadcruze]

Thanks @Fabel

But still getting 'Fatal error: Uncaught Error: Call to a member function bindParam() on boolean' for  this

$stmt = $con->prepare("SELECT sdate 
, DATE_FORMAT(sdate, 'Week %v %W %d/%m/%Y') AS fdate
, wa.shift
, w.ward_name
, a.bed
, a.patient_id
, p.patient_name FROM ward_allotment wa 
INNER JOIN wards w ON wa.ward=w.wid 
INNER JOIN admission a 
ON wa.ward=a.ward 
AND wa.sdate BETWEEN a.from_date AND a.discharge_date 
INNER JOIN patient p ON a.patient_id=p.patient_id 
WHERE wa.nurse=:nid 
AND sdate BETWEEN CURDATE() AND CURDATE() + INTERVAL 6 DAY 
ORDER BY sdate, shift, ward_name, a.bed, a.patient_id");

$stmt->bindParam(":nid", $nid);

$stmt->execute();

 

Edited September 20, 2019 by dishadcruze

[Fabel]

did you give

$nid

a value? And 'nid' stands for 'nurs id'? Maybe you could call it, like patient_id, also nurse_id? But that's personal. 

 

Maybe you could var_dump the query and screenshot that with of the error? You could also try to use a query like 'SELECT id FROM ward_allotment WHERE nurse=2' to check if the query itself works.

Edited September 20, 2019 by Fabel

[dishadcruze]

if i use

WHERE wa.nurse=".$nid."

Error wont be there in that like, but error in

 

[Fabel]

1 minute ago, dishadcruze said:

WHERE wa.nurse=".$nid."

 

This is not the safe way of doing it. And if you use this you don't need the $stmt->bindParam, because there is no ':id' to bind a parameter to. (am I explaining that correctly?) 

 

Barand gave you a working example of the script. Try to type that exactly as how Barand typed it, it should be working.

 

[dishadcruze]

Thanks. But i tried doing exactly the same i mean without using $nid but using ?.

When i used ?, i did not use bindParam. I will try it out once again

[Barand]

In your original post, if you scroll right about 3 feet you will find...

15 hours ago, dishadcruze said:

 ... INNER JOIN patient p ON a.patient_id=p.patient_id INNER JOIN wards w ON wa.ward=w.wid ...

When I set up the test database I named the table "ward" (I generally use a convention of singlular names for entities) and so it is "ward" in my query.

If you had the error reporting attribute set in PDO it would have told you.

[dishadcruze]

Yes, That i had corrected.

Sorry for the silly mistake. I missed out echo while displaying

<?php  $tdata; ?>

I corrected it 

<?php echo $tdata; ?>

Thanks @Barand and @Fabel

[Barand]

... or you could have  used

<?=$tdata?>

as I did.

FYI, my PDO connection code is...

    $dsn = "mysql:dbname=$database; host=$host; charset=utf8";

    $db = new pdo($dsn, $username, $password, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);

so that any errors are reported

[Barand]

P.S.

If you want to show the nurse allotment even when there are no patients on the allocated ward...

... then you will need LEFT JOINS thus

$stmt = $db->prepare("SELECT   sdate
                             , DATE_FORMAT(sdate, 'Week %v %W %d/%m/%Y') as fdate
                             , wa.shift
                             , w.ward_name
                             , a.bed
                             , a.patient_id
                             , p.patient_name
                        FROM ward_allotment wa
                             INNER JOIN
                             ward w ON wa.ward = w.wid
                             LEFT JOIN
                             admission a ON wa.ward = a.ward
                                         AND wa.sdate BETWEEN a.from_date AND a.discharge_date
                             LEFT JOIN 
                             patient p ON a.patient_id = p.patient_id
                        WHERE wa.nurse = ?
                             AND sdate BETWEEN CURDATE() - interval 30 day AND CURDATE() + INTERVAL 6 DAY
                        ORDER BY sdate, shift, ward_name, a.bed, a.patient_id
                        ");

 

[Rendimo]

No need to use $stmt->bindParam. Maybe it causes fatal error.