Jump to content
dishadcruze

Display data groupwise in php

Recommended Posts

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

wards.PNG.41c5c692f4644cfbc0b66f814e049dc3.PNG

Here is my ward allotment for each nurse

ward_allotment.PNG.7fb65d489106c4b617a3c23cbe28bb6e.PNG

 

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

admit.PNG.ecd03fb975e51fdde68411c202901606.PNG

 

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.

 

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

image.png.cfe9ea2f20eefe67a4f41ea29e86fa78.png

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

image.png.21a1d95f52f44f0be1ec36125514b89e.png

Edited by Barand
  • Great Answer 1

Share this post


Link to post
Share on other sites

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.

error.thumb.PNG.6213dfd7fd54fd91c3399eae747a89bf.PNG

 

 

Share this post


Link to post
Share on other sites

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
]);

Share this post


Link to post
Share on other sites

Knipsel2.PNG.027a045a95d9623881e2129cb5b49dca.PNG 

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:

Knipsel2.PNG.3226e545762c70c6c74c0854ce3c7471.PNG

Edited by Fabel
  • Thanks 1

Share this post


Link to post
Share on other sites

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 by dishadcruze

Share this post


Link to post
Share on other sites

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 by Fabel

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

... 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

  • Like 1

Share this post


Link to post
Share on other sites

P.S.

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

image.png.8cd3e97f9db837dd0ce51bc390433b11.png

... 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
                        ");

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.