I am working on an application build on Laravel on the back in with Mysql. I have the below Mysql query doest quite work as expected yet. I am not a Mysql expert but basically what I am trying to do is for every AptStatus = 3 I have a Null field for Operatory. Id like to convert that Null field to the Operatory name for each Operatory in the location. (hopefully that makes sense).
I am storing this in php as a variable to store my list of locations.
$locs = (below query in Eloquent)
Locations query:
select
op.Location, op.Operatory
from rpt_officeschedules as op
left join apptwidget_locations al on op.Location = al.location
where
(op.Operatory LIKE '%NP%' or op.Operatory LIKE '%OPEN%') and
al.isOfficeClosed = 0
group by Location, Operatory;
Query Results:
Location | Operatory |
--------- ----------
Addison. 2 NP
Addison. 3 NP/TX
MySql Query:
select
Location,
AptDate,
IF(AptStatus = 3, '08:00:00', AptTime) AptTime,
IF(AptStatus = 3, 540, AptLength) AptLength,
AptStatus,
OperatoryNum,
Operatory
from rpt_officeschedules
where
AptTime between '07:59:00' and '17:30:00' and
AptDate between '2020-11-07' and '2020-11-08' and
(
Operatory LIKE '%NP%' or
Operatory LIKE '%OPEN%' or
Operatory is null
)
order by Location, AptDate, OperatoryNum, AptTime, Operatory;
Query results
Location | AptDate | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- --------- --------- ----------- ----------- ----------- --------------
Addison. 2020-11-07 08:00:00. 540 null 3 0
Addison 2020-11-07 08:00:00. 540 null 3 0
Addison. 2020-11-08 08:00:00. 540 null 3 0
Addison 2020-11-08 08:00:00. 540 null 3 0
What id like to do is when a location shows an AptStatus = 3, to convert that null field for each Operatory name ($locs)
Expected results:
Location | AptDate | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- --------- --------- ----------- ----------- ----------- --------------
Addison. 2020-11-07 08:00:00. 540 2 NP 3 0
Addison 2020-11-07 08:00:00. 540 3 NP/TX 3 0
Addison. 2020-11-08 08:00:00. 540 2 NP 3 0
Addison 2020-11-08 08:00:00. 540 3 NP/TX 3 0
Any Ideas?