PHP MYSQL - accessing array values dynamically

Question

I am creating a occupied/ Vacant room chart in Yii2 using PHP and MYSQL. I am able to get the result what I want, but what I am doing is not the correct method, as everything I am hardcoding. What I want is to make it dynamic.

MY queries are like this:

command = Yii::$app->db->createCommand
        ("select a.room_category, group_concat(b.room_name) vacant_beds
    from (select distinct id, room_category from room_category) a
    left join 
    (select rct.room_category AS room_category,
        rn.room_name 
    from room_category rct  
    left join room_name rn on rn.room_category = rct.id
    left join patient_detail pd on rn.id = pd.bed_type
    and (isnull(pd.discharge_date) or now() between pd.admission_date and pd.discharge_date)
    where isnull(pd.id) 
  order by rct.room_category, rn.room_name) b on a.room_category=b.room_category
group by a.room_category
order by a.id");
$rows= $command->queryAll();

the result of $rows is like this:

array (size=9)
  0 => 
    array (size=2)
      'room_category' => string 'MALE GENERAL WARD' (length=17)
      'vacant_beds' => string 'MG-8,MG-2,MG-4,MG-6,MG-7' (length=24)
  1 => 
    array (size=2)
      'room_category' => string 'FEMALE GENERAL WARD' (length=19)
      'vacant_beds' => string 'FG-4,FG-1,FG-2,FG-3' (length=19)
  2 => 
    array (size=2)
      'room_category' => string 'MOTHER CHILD WARD' (length=17)
      'vacant_beds' => string 'MC-2,MC-4,MC-5,MC-6' (length=19)
  3 => 
    array (size=2)
      'room_category' => string 'TWIN' (length=4)
      'vacant_beds' => string 'TW-A1,TW-A2,TW-B2,TW-C1,TW-C2' (length=29)
  4 => 
    array (size=2)
      'room_category' => string 'NICU' (length=4)
      'vacant_beds' => string 'NICU-6,NICU-1,NICU-7,NICU-3,NICU-8,NICU-4,NICU-5' (length=48)
  5 => 
    array (size=2)
      'room_category' => string 'CLASSIC' (length=7)
      'vacant_beds' => string 'CL-6,CL-8,CL-4,CL-5' (length=19)
  6 => 
    array (size=2)
      'room_category' => string 'DELUXE' (length=6)
      'vacant_beds' => string 'DLX-5,DLX-6' (length=11)
  7 => 
    array (size=2)
      'room_category' => string 'EXECUTIVE' (length=9)
      'vacant_beds' => null
  8 => 
    array (size=2)
      'room_category' => string 'AC GENERAL WARD' (length=15)
      'vacant_beds' => string 'AG-5,AG-1,AG-2,AG-3,AG-4' (length=24)

Then for each room I am doing it like this:

$commandOccupiedmg1 = Yii::$app->db->createCommand
        ("SELECT pd.patient_name as name,i.ipd_patient_id ipd,i.care_of_name relation, 
        i.mobile, i.district_city,pd.admission_date from patient_detail pd, 
        ipd_patient_entry i, room_name rn 
        where pd.ipd_patient_id=i.id and discharged !=1 and rn.id=pd.bed_type and rn.room_name='MG-1'");
$mg1s= $commandOccupiedmg1->queryAll();

The data of mg1s is like this:

array (size=1)
  0 => 
    array (size=6)
      'name' => string 'SAROJ MIRDHA' (length=12)
      'ipd' => string '0749/15' (length=7)
      'relation' => string 'GUDUM MIRDHA' (length=12)
      'mobile' => string '7381566425' (length=10)
      'district_city' => string 'SAMBALPUR' (length=9)
      'admission_date' => string '2015-06-16 21:45:00' (length=19)

and to access the value I am doing this query:

foreach($mg1s as $mg1){

    $mg1_patient_name=$mg1['name'];
    $mg1_mobile =$mg1['mobile'];
    $mg1_ipd = $mg1['ipd'];
    $mg1_relation = $mg1['relation'];
    $mg1_admission_date=$mg1['admission_date'];
}

Then to display the info for each room I am using this code. Doing this for every room.

<table  class="table table-striped table-bordered discharge-note-border">
    <tr>
<td>MALE</td>

<?php         
    if (array_key_exists('0', $rows) && strpos($rows[0]['vacant_beds'],'MG-1')!==FALSE) {
    echo "<td style='background-color:#FFFFCC;'/>MG-1 </td>";
    }else{
      echo "<td style='background-color:#CCFFFF;'>
        <div class='occupied'>MG-1<div id='occupied-hover'>       
        Patient Name - $mg1_patient_name  <br> IPD No. - $mg1_ipd 
        <br>          
        Guardian - $mg1_relation <br> Mobile - $mg1_mobile <br>
         Admission Date - $mg1_admission_date        
        </div></div> </td>";

    }

The question is that there are about 50 rooms. I am hard-coding everything. if any room is added or removed, I have to change the code or it will break.

Need a suggestion how I can do it dynamically and in a better manner.

Lose the group_concat. Appears like you are doing a bunch of work to avoid possibly having to make a second sql call? You have patients, beds, and rooms, rooms have beds, patients have a bed. 3 entities, 2 relationships. — Chris Caviness
– Chris Caviness, Commented Jan 27, 2017 at 15:40
Excuse me, 4 entities, 3 relationships, as it appears you have a categorizing group called "ward" — Chris Caviness
– Chris Caviness, Commented Jan 27, 2017 at 15:44
Yes you are right. so can you shed some more light, how I should proceed? — Pawan
– Pawan, Commented Jan 27, 2017 at 15:45
Well, what output are you looking for? A room census, you would start at wards join to rooms join to beds then left join to patients where patient.patientID is not null, and order by wards, rooms. Any bed with null as a patient id would be an empty bed so if you just want empty beds, same process but where patient.patiendID is null. — Chris Caviness
– Chris Caviness, Commented Jan 27, 2017 at 16:06

marche · Accepted Answer · 2017-01-27 20:50:31Z

1

If you are using Yii2, i would recommend using yii\db\ActiveRecord in conjunction with yii\db\Query.

You can generate model classes that extend from ActiveRecord using Gii. It generates relationships that can be used with Lazy Loading and/or Eager Loading.

Also, by using these you can build your queries with code easier to read and more dynamically. Besides, you can actually use objects from your model classes that have their own methods and extra attributes that are not stored on the database.

answered Jan 27, 2017 at 20:50

marche

1,7561 gold badge14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Danoweb · Accepted Answer · 2017-01-27 16:43:58Z

In a relational lookup like this, there are several database functions that are extremely helpful in building your output that allow you to be Dynamic.

I'm not very familiar with Yii, but typically you would want to perform with workflow:

//QUERY ALL ROOMS
$rooms = mysqli_query($rooms_query)
$rooms_count = myqsli_num_rows($rooms);

for($r = 0; $rooms_count < $r; $r++)
{  
    //GET DATA AND STEP TO NEXT RECORD
    $rooms_details = mysqli_fetch_assoc($rooms)
    //STEP THROUGH EACH ROOM AND ASK THE DB FOR PERTINENT INFORMATION
    $vacancies_sql = "SELECT vacant_beds FROM (vacancies) WHERE room_id = $rooms_details['id']";
    $vacancies = mylsqli_query($vancancies_sql);
    $vacancies_count = mysqli_num_rows($vacancies);

    //GO THROUGH EACH VANCANY RECORD, AND ADD IT, AND IT's ROOM TO OUTPUT
    $output[$r] = array('room' => $rooms_details['room_name']);

    for($v=0;$v < $vacancies_count; $v++)
    {
        $vacancies_details = mysqli_fetch_assoc($vacancies);
        $output[$r]['vacancies'] = $vancancies_details['vacant_bed_id'];
    }

return $output;

So, this will give you a multidimensional with the details you need. Structured like:

array [0]=> ( room = 'MALE', vacancies = array ( 0 = 'mg1', etc...)

So, to get an idea of what this looks like you can try print_r($output)

Obviously, this isn't complete working code. It's psuedo code to show the idea and approach to massage the data into a useful MultiDimensional Array that you can easily utilize in your views.

Hope this helps! If you would like me to further explain, or clarify I would be happy to update my answer just let me know in a comment.

Ok I will try it and let you know. Thanks for the direction though.

Collectives™ on Stack Overflow

PHP MYSQL - accessing array values dynamically

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related