0

I have a multi-dimensional array $rows which return the result like below; I have created queries based on array_keys. Now the problem I am facing is $rows may or may not return some of the rows if there is no vacant beds and all my queries goes for a toss.

I have tried to use in_array function, but it appears this doens't work with multi-dimensional array.

array (size=6)
      0 => 
        array (size=2)
          'room_category' => string 'MALE GENERAL WARD' (length=17)
          'vacant_beds' => string 'MG-5' (length=4)
      1 => 
        array (size=2)
          'room_category' => string 'FEMALE GENERAL WARD' (length=19)
          'vacant_beds' => string 'FG-2,FG-3,FG-4' (length=14)
      2 => 
        array (size=2)
          'room_category' => string 'MOTHER CHILD WARD' (length=17)
          'vacant_beds' => string 'MC-1,MC-3,MC-4' (length=14)
      3 => 
        array (size=2)
          'room_category' => string 'NICU' (length=4)
          'vacant_beds' => string 'NICU-8,NICU-4,NICU-5,NICU-6,NICU-1,NICU-7,NICU-2' (length=48)
      4 => 
        array (size=2)
          'room_category' => string 'CLASSIC' (length=7)
          'vacant_beds' => string 'CL-9,CL-4,CL-5,CL-7,CL-8' (length=24)
      5 => 
        array (size=2)
          'room_category' => string 'DELUXE' (length=6)
          'vacant_beds' => string 'DLX-6,DLX-3,DLX-4,DLX-5' (length=23)

What approach I should take or ensure that it returns a row even if a specific ward is not vacant.

My code for $rows is like this:

select `rct`.`room_category` AS `room_category`,
    group_concat(`rn`.`room_name` separator ',') AS `vacant_beds` 
    from ((`room_name` `rn` join `room_category` 
    `rct` 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`) group by `rn`.`room_category`
Pawan
  • 3,864
  • 17
  • 50
  • 83
  • 1
    possible duplicate of [PHP multi dimensional array search](http://stackoverflow.com/questions/6661530/php-multi-dimensional-array-search) – Marc Apr 12 '15 at 15:24
  • Hi Marc - Question heading may be duplicate, but I have asked for additional solutions like whether I can return array keys with empty values. – Pawan Apr 12 '15 at 15:27

1 Answers1

1

Your JOIN to the empty beds query is leaving out empty values. I suggest you use a subquery to generate a complete list of room_category values.

            select distinct room_category
              from room_category

Then I suggest you LEFT JOIN that subquery into the rest of your query, and using its value to group by. That will end up looking something like this:

select a.room_category, 
       group_concat(b.room_name ORDER BY b.room_name) vacancies
  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.room_category
O. Jones
  • 103,626
  • 17
  • 118
  • 172