| comparing arrays [message #181035] |
Sun, 07 April 2013 11:34  |
bill
Messages: 310
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
I need to see if a patient has appointments with a
psychotherapist and a physician on the same day.
I request the schedule of each therapist and each physician as
arrays of arrays:
time, patient_number, duration (note, the therapist/physician id
is not in the array)
They are non-associative arrays (numeric keys)
I might have 1..n physicians and 1..n therapists
I can generate arrays of physicians and arrays of therapists
Option 1 is a nightmare of nested foreachs
I am sure there must be an array function that will permit this
check. AFAIK, the _intersect functions require the same date in
all, when what I want is "any".
my current thought is to create arrays of just pt_number, merge
the physician arrays, merge the therapist arrays, array_flip to
move the values to keys and then array_intersect.
But, before I try that I welcome any suggestions.
bill
|
|
|
|
| Re: comparing arrays [message #181038 is a reply to message #181035] |
Sun, 07 April 2013 12:34  |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 4/7/2013 7:34 AM, bill wrote:
> I need to see if a patient has appointments with a psychotherapist and a
> physician on the same day.
>
> I request the schedule of each therapist and each physician as arrays of
> arrays:
> time, patient_number, duration (note, the therapist/physician id is not
> in the array)
> They are non-associative arrays (numeric keys)
> I might have 1..n physicians and 1..n therapists
> I can generate arrays of physicians and arrays of therapists
>
> Option 1 is a nightmare of nested foreachs
>
> I am sure there must be an array function that will permit this check.
> AFAIK, the _intersect functions require the same date in all, when what
> I want is "any".
>
> my current thought is to create arrays of just pt_number, merge the
> physician arrays, merge the therapist arrays, array_flip to move the
> values to keys and then array_intersect.
>
> But, before I try that I welcome any suggestions.
>
> bill
Does the information come from a SQL database? If so, I'd do it in SQL.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: comparing arrays [message #181042 is a reply to message #181035] |
Sun, 07 April 2013 13:37 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
bill wrote:
> I need to see if a patient has appointments with a
> psychotherapist and a physician on the same day.
>
> I request the schedule of each therapist and each physician as
> arrays of arrays:
> time, patient_number, duration (note, the therapist/physician id
> is not in the array)
> They are non-associative arrays (numeric keys)
> I might have 1..n physicians and 1..n therapists
> I can generate arrays of physicians and arrays of therapists
>
> Option 1 is a nightmare of nested foreachs
>
> I am sure there must be an array function that will permit this
> check.
I am quite sure there isn't.
> AFAIK, the _intersect functions require the same date in
> all, when what I want is "any".
>
> my current thought is to create arrays of just pt_number, merge
> the physician arrays, merge the therapist arrays, array_flip to
> move the values to keys and then array_intersect.
A merge will destroy the association between patient, date, and doctor.
> But, before I try that I welcome any suggestions.
You should probably do this in a relational database. But if you
absolutely must do it in PHP, you can determine what the keys of your data
are, and in which order you need them: the patient, the date, or the doctor.
Then you can build from the records a “multi-dimensional array” (for lack of
a better word) from which becomes apparent where there are schedule
collisions. You can take advantage of the fact that PHP builds “multi-
dimensional arrays” automatically if you add another index expression.
For example, in a loop,
$appointments[$patient_id][$date] = $doctor;
would store the information that the patient with ID $patient_id has an
appointment with $doctor on $date. Obviously it is very easy then to
organize the data by different primary keys in different “multi-dimensional
arrays” (which themselves may be elements of yet another array, to avoid
extra variables). If an element of an array with an index already exists,
there is the collision.
HTH
PointedEars
--
Sometimes, what you learn is wrong. If those wrong ideas are close to the
root of the knowledge tree you build on a particular subject, pruning the
bad branches can sometimes cause the whole tree to collapse.
-- Mike Duffy in cljs, <news:Xns9FB6521286DB8invalidcom(at)94(dot)75(dot)214(dot)39>
|
|
|
|
| Re: comparing arrays [message #181043 is a reply to message #181042] |
Sun, 07 April 2013 13:59 |
bill
Messages: 310
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
On 4/7/2013 9:37 AM, Thomas 'PointedEars' Lahn wrote:
> bill wrote:
>
>> I need to see if a patient has appointments with a
>> psychotherapist and a physician on the same day.
>>
>> I request the schedule of each therapist and each physician as
>> arrays of arrays:
>> time, patient_number, duration (note, the therapist/physician id
>> is not in the array)
>> They are non-associative arrays (numeric keys)
>> I might have 1..n physicians and 1..n therapists
>> I can generate arrays of physicians and arrays of therapists
>>
>> Option 1 is a nightmare of nested foreachs
>>
>> I am sure there must be an array function that will permit this
>> check.
>
> I am quite sure there isn't.
>
>> AFAIK, the _intersect functions require the same date in
>> all, when what I want is "any".
>>
>> my current thought is to create arrays of just pt_number, merge
>> the physician arrays, merge the therapist arrays, array_flip to
>> move the values to keys and then array_intersect.
>
> A merge will destroy the association between patient, date, and doctor.
>
>> But, before I try that I welcome any suggestions.
>
> You should probably do this in a relational database. But if you
> absolutely must do it in PHP, you can determine what the keys of your data
> are, and in which order you need them: the patient, the date, or the doctor.
>
> Then you can build from the records a “multi-dimensional array” (for lack of
> a better word) from which becomes apparent where there are schedule
> collisions. You can take advantage of the fact that PHP builds “multi-
> dimensional arrays” automatically if you add another index expression.
>
> For example, in a loop,
>
> $appointments[$patient_id][$date] = $doctor;
>
> would store the information that the patient with ID $patient_id has an
> appointment with $doctor on $date. Obviously it is very easy then to
> organize the data by different primary keys in different “multi-dimensional
> arrays” (which themselves may be elements of yet another array, to avoid
> extra variables). If an element of an array with an index already exists,
> there is the collision.
>
>
> HTH
It is actually slightly simpler as I only need to check for
"today". But is more complex in that I have two classes for
"doctor:" therapist or physician.
By doing it in a db are you suggesting a temporary table, or
using the tables I have ?
bill
|
|
|
|
| Re: comparing arrays [message #181044 is a reply to message #181038] |
Sun, 07 April 2013 14:00 |
bill
Messages: 310
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
On 4/7/2013 8:34 AM, Jerry Stuckle wrote:
> On 4/7/2013 7:34 AM, bill wrote:
>> I need to see if a patient has appointments with a
>> psychotherapist and a
>> physician on the same day.
>>
>> I request the schedule of each therapist and each physician as
>> arrays of
>> arrays:
>> time, patient_number, duration (note, the therapist/physician
>> id is not
>> in the array)
>> They are non-associative arrays (numeric keys)
>> I might have 1..n physicians and 1..n therapists
>> I can generate arrays of physicians and arrays of therapists
>>
>> Option 1 is a nightmare of nested foreachs
>>
>> I am sure there must be an array function that will permit this
>> check.
>> AFAIK, the _intersect functions require the same date in all,
>> when what
>> I want is "any".
>>
>> my current thought is to create arrays of just pt_number, merge
>> the
>> physician arrays, merge the therapist arrays, array_flip to
>> move the
>> values to keys and then array_intersect.
>>
>> But, before I try that I welcome any suggestions.
>>
>> bill
>
> Does the information come from a SQL database? If so, I'd do it
> in SQL.
>
Yes it does, 3 tables: users (therapist/physician), pt_info,
schedule.
A little more of a hint please.
bill
|
|
|
|
| Re: comparing arrays [message #181045 is a reply to message #181044] |
Sun, 07 April 2013 14:14 |
Frank Steinmetzger
Messages: 2
Registered: April 2013
Karma: 0
|
Junior Member |
|
|
bill wrote:
> On 4/7/2013 8:34 AM, Jerry Stuckle wrote:
>> On 4/7/2013 7:34 AM, bill wrote:
>>> I need to see if a patient has appointments with a psychotherapist
>>> and a physician on the same day.
>>>
>>> I request the schedule of each therapist and each physician as
>>> arrays of arrays:
>>> time, patient_number, duration (note, the therapist/physician
>>> id is not in the array)
>>> They are non-associative arrays (numeric keys)
>>> I might have 1..n physicians and 1..n therapists
>>> I can generate arrays of physicians and arrays of therapists
>>> […]
>>>
>> Does the information come from a SQL database? If so, I'd do it
>> in SQL.
>>
>
> Yes it does, 3 tables: users (therapist/physician), pt_info,
> schedule.
>
> A little more of a hint please.
You select two different appointments that have the same date:
SELECT s1.patient_number AS pn, s1.time AS t1, s2.time AS t2,
s1.duration AS d1, s2.duration AS d2
FROM schedule s1, schedule s2
WHERE s1.time=s2.time AND s1.time=<today's date>
AND s1.patient_number=s2.patient_number
AND s1.patient_number=<ID of patient of interest>
AND s1.id!=s2.id
AND s1.doctor_number!=s2.doctor_number
Of course, those times don't work that way, b/c they have date *and* time,
and you want to deal with date only. I'm not into advanced SQL, perhaps there
is an SQL function to only extract and use the date part.
You then would need to PHP-fiddle the time and duration together for only
those appointments that are today in a nested for-loop.
--
Gruß | Greetings | Qapla’
Please do not share anything from, with or about me with any Facebook
service.
Dyslexics of the world, untie!
|
|
|
|
| Re: comparing arrays [message #181047 is a reply to message #181035] |
Sun, 07 April 2013 14:59 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 07/04/13 12:34, bill wrote:
> I need to see if a patient has appointments with a psychotherapist and a
> physician on the same day.
>
> I request the schedule of each therapist and each physician as arrays of
> arrays:
> time, patient_number, duration (note, the therapist/physician id is not
> in the array)
> They are non-associative arrays (numeric keys)
> I might have 1..n physicians and 1..n therapists
> I can generate arrays of physicians and arrays of therapists
>
> Option 1 is a nightmare of nested foreachs
>
> I am sure there must be an array function that will permit this check.
> AFAIK, the _intersect functions require the same date in all, when what
> I want is "any".
>
> my current thought is to create arrays of just pt_number, merge the
> physician arrays, merge the therapist arrays, array_flip to move the
> values to keys and then array_intersect.
>
> But, before I try that I welcome any suggestions.
>
> bill
You need a better SQL statement to select patients and dates where this
condition exists. Don't do this in PHP, do it in Mysql.
Sadly my SQL is only good enough to know that this *can* be done, and
simply. It would take me hours to get the SQL query right, but
comp.databases.mysql is that way==>
Assuming you are in fact querying a database to get your arrays.
If not you bloody well should be :-)
--
Ineptocracy
(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.
|
|
|
|
| Re: comparing arrays [message #181048 is a reply to message #181035] |
Sun, 07 April 2013 15:01 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 07/04/13 15:13, Frank Steinmetzger wrote:
> bill wrote:
>
>> On 4/7/2013 8:34 AM, Jerry Stuckle wrote:
>>> On 4/7/2013 7:34 AM, bill wrote:
>>>> I need to see if a patient has appointments with a psychotherapist
>>>> and a physician on the same day.
>>>>
>>>> I request the schedule of each therapist and each physician as
>>>> arrays of arrays:
>>>> time, patient_number, duration (note, the therapist/physician
>>>> id is not in the array)
>>>> They are non-associative arrays (numeric keys)
>>>> I might have 1..n physicians and 1..n therapists
>>>> I can generate arrays of physicians and arrays of therapists
>>>> […]
>>>>
>>> Does the information come from a SQL database? If so, I'd do it
>>> in SQL.
>>>
>>
>> Yes it does, 3 tables: users (therapist/physician), pt_info,
>> schedule.
>>
>> A little more of a hint please.
>
> You select two different appointments that have the same date:
>
> SELECT s1.patient_number AS pn, s1.time AS t1, s2.time AS t2,
> s1.duration AS d1, s2.duration AS d2
> WHERE s1.time=s2.time AND s1.time=<today's date>
> AND s1.patient_number=s2.patient_number
> AND s1.patient_number=<ID of patient of interest>
> AND s1.id!=s2.id
> AND s1.doctor_number!=s2.doctor_number
>
> Of course, those times don't work that way, b/c they have date *and* time,
> and you want to deal with date only. I'm not into advanced SQL, perhaps there
> is an SQL function to only extract and use the date part.
>
There is. date(time) will extract the date alone
> You then would need to PHP-fiddle the time and duration together for only
> those appointments that are today in a nested for-loop.
>
why not elect only 'todays' date in the first place?
--
Ineptocracy
(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.
|
|
|
|
| Re: comparing arrays [message #181052 is a reply to message #181045] |
Sun, 07 April 2013 15:13 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
Frank Steinmetzger wrote:
> bill wrote:
>> On 4/7/2013 8:34 AM, Jerry Stuckle wrote:
>>> On 4/7/2013 7:34 AM, bill wrote:
>>>> I need to see if a patient has appointments with a psychotherapist
>>>> and a physician on the same day.
>>>>
>>>> I request the schedule of each therapist and each physician as
>>>> arrays of arrays:
>>>> time, patient_number, duration (note, the therapist/physician
>>>> id is not in the array)
>>>> They are non-associative arrays (numeric keys)
>>>> I might have 1..n physicians and 1..n therapists
>>>> I can generate arrays of physicians and arrays of therapists
>>>> […]
>>>>
>>> Does the information come from a SQL database? If so, I'd do it
>>> in SQL.
>>
>> Yes it does, 3 tables: users (therapist/physician), pt_info,
>> schedule.
>>
>> A little more of a hint please.
>
> You select two different appointments that have the same date:
>
> SELECT s1.patient_number AS pn, s1.time AS t1, s2.time AS t2,
> s1.duration AS d1, s2.duration AS d2
> FROM schedule s1, schedule s2
> WHERE s1.time=s2.time AND s1.time=<today's date>
> AND s1.patient_number=s2.patient_number
> AND s1.patient_number=<ID of patient of interest>
> AND s1.id!=s2.id
> AND s1.doctor_number!=s2.doctor_number
Please don't. If each schedule table has as little as 100 records, there
will be 10'000 result records to filter from. Read on JOIN to avoid
Cartesian products like this.
> Of course, those times don't work that way, b/c they have date *and* time,
> and you want to deal with date only. I'm not into advanced SQL,
Apparently you are not into SQL, or relational databases for that matter, at
all.
> perhaps there is an SQL function to only extract and use the date part.
QED.
> You then would need to PHP-fiddle the time and duration together for only
> those appointments that are today in a nested for-loop.
No. Apparently you are also not into PHP.
Please refrain from suggesting “solutions” in areas where you do not have a
(minimum) clue. TIA.
PointedEars
--
Prototype.js was written by people who don't know javascript for people
who don't know javascript. People who don't know javascript are not
the best source of advice on designing systems that use javascript.
-- Richard Cornford, cljs, <f806at$ail$1$8300dec7(at)news(dot)demon(dot)co(dot)uk>
|
|
|
|
| Re: comparing arrays [message #181053 is a reply to message #181043] |
Sun, 07 April 2013 15:14 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
bill wrote:
> On 4/7/2013 9:37 AM, Thomas 'PointedEars' Lahn wrote:
>> bill wrote:
>>> I need to see if a patient has appointments with a
>>> psychotherapist and a physician on the same day.
>>>
>>> I request the schedule of each therapist and each physician as
>>> arrays of arrays:
>>> time, patient_number, duration (note, the therapist/physician id
>>> is not in the array)
>>> They are non-associative arrays (numeric keys)
>>> I might have 1..n physicians and 1..n therapists
>>> I can generate arrays of physicians and arrays of therapists
>>> […]
>>
>> You should probably do this in a relational database. But if you
>> absolutely must do it in PHP, you can determine what the keys of your
>> data are, and in which order you need them: the patient, the date, or the
>> doctor.
>>
>> Then you can build from the records a “multi-dimensional array” (for lack
>> of a better word) from which becomes apparent where there are schedule
>> collisions. You can take advantage of the fact that PHP builds “multi-
>> dimensional arrays” automatically if you add another index expression.
>>
>> For example, in a loop,
>>
>> $appointments[$patient_id][$date] = $doctor;
>>
>> would store the information that the patient with ID $patient_id has an
>> appointment with $doctor on $date. Obviously it is very easy then to
>> organize the data by different primary keys in different
>> “multi-dimensional arrays” (which themselves may be elements of yet
>> another array, to avoid
>> extra variables). If an element of an array with an index already
>> exists, there is the collision.
>
> It is actually slightly simpler as I only need to check for
> "today". But is more complex in that I have two classes for
> "doctor:" therapist or physician.
Am I to understand that you need to consider only one patient, only one
date, and cannot figure out how to filter your array of arrays to see that
there are different kinds of doctors involved using these criteria?
> By doing it in a db are you suggesting a temporary table, or
> using the tables I have ?
The latter, sort of. You would JOIN the tables you have and apply
aggregation functions on the result (certainly not as Frank Steinmetzger
suggested). If you would need to do this often, it would be a good idea to
store the result in a TEMPORARY TABLE, if your DBMS supports that, or in
general in a so-called “flat table”.
People have also devised and implemented a rather elegant way of querying
data without a database – Language-INtegrated Query (LINQ) – that you also
might find useful. [1]
Please trim your quotes.
PointedEars
___________
[1] <http://stackoverflow.com/questions/5792388/is-there-something-in-php-
equivalent-to-linq-in-c>
--
Anyone who slaps a 'this page is best viewed with Browser X' label on
a Web page appears to be yearning for the bad old days, before the Web,
when you had very little chance of reading a document written on another
computer, another word processor, or another network. -- Tim Berners-Lee
|
|
|
|
| Re: comparing arrays [message #181054 is a reply to message #181045] |
Sun, 07 April 2013 15:34 |
bill
Messages: 310
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
On 4/7/2013 10:14 AM, Frank Steinmetzger wrote:
> bill wrote:
>
>> On 4/7/2013 8:34 AM, Jerry Stuckle wrote:
>>> On 4/7/2013 7:34 AM, bill wrote:
>>>> I need to see if a patient has appointments with a psychotherapist
>>>> and a physician on the same day.
>>>>
>>>> I request the schedule of each therapist and each physician as
>>>> arrays of arrays:
>>>> time, patient_number, duration (note, the therapist/physician
>>>> id is not in the array)
>>>> They are non-associative arrays (numeric keys)
>>>> I might have 1..n physicians and 1..n therapists
>>>> I can generate arrays of physicians and arrays of therapists
>>>> […]
>>>>
>>> Does the information come from a SQL database? If so, I'd do it
>>> in SQL.
>>>
>>
>> Yes it does, 3 tables: users (therapist/physician), pt_info,
>> schedule.
>>
>> A little more of a hint please.
>
> You select two different appointments that have the same date:
>
> SELECT s1.patient_number AS pn, s1.time AS t1, s2.time AS t2,
> s1.duration AS d1, s2.duration AS d2
> FROM schedule s1, schedule s2
> WHERE s1.time=s2.time AND s1.time=<today's date>
> AND s1.patient_number=s2.patient_number
> AND s1.patient_number=<ID of patient of interest>
> AND s1.id!=s2.id
> AND s1.doctor_number!=s2.doctor_number
>
> Of course, those times don't work that way, b/c they have date *and* time,
> and you want to deal with date only. I'm not into advanced SQL, perhaps there
> is an SQL function to only extract and use the date part.
there is
>
> You then would need to PHP-fiddle the time and duration together for only
> those appointments that are today in a nested for-loop.
select s1.user as s1user,
s2.user as s2user,
s1.apptTime as s1apptTime,
s2.apptTime as s2pptTime,
users.physician,
users.user_id
from schedule as s1 join schedule as s2 join users
on (s1.patient_number = s2.patient_number
and users.user_id = s2.user)
where s1.patient_number = s2.patient_number
and s1.appt_status = 'a'
and s2.appt_status = 'a'
and s1.patient_number = '5214'
and s1.schedule_id != s2.schedule_id
and physician = '1'
and date(s1.apptTime) = date(s2.apptTime)
and date(s1.apptTime) = '2013-04-03'
did the job
Thanks for the great suggestion.
bill
|
|
|
|
| Re: comparing arrays [message #181058 is a reply to message #181044] |
Sun, 07 April 2013 18:18 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 4/7/2013 10:00 AM, bill wrote:
> On 4/7/2013 8:34 AM, Jerry Stuckle wrote:
>> On 4/7/2013 7:34 AM, bill wrote:
>>> I need to see if a patient has appointments with a
>>> psychotherapist and a
>>> physician on the same day.
>>>
>>> I request the schedule of each therapist and each physician as
>>> arrays of
>>> arrays:
>>> time, patient_number, duration (note, the therapist/physician
>>> id is not
>>> in the array)
>>> They are non-associative arrays (numeric keys)
>>> I might have 1..n physicians and 1..n therapists
>>> I can generate arrays of physicians and arrays of therapists
>>>
>>> Option 1 is a nightmare of nested foreachs
>>>
>>> I am sure there must be an array function that will permit this
>>> check.
>>> AFAIK, the _intersect functions require the same date in all,
>>> when what
>>> I want is "any".
>>>
>>> my current thought is to create arrays of just pt_number, merge
>>> the
>>> physician arrays, merge the therapist arrays, array_flip to
>>> move the
>>> values to keys and then array_intersect.
>>>
>>> But, before I try that I welcome any suggestions.
>>>
>>> bill
>>
>> Does the information come from a SQL database? If so, I'd do it
>> in SQL.
>>
>
> Yes it does, 3 tables: users (therapist/physician), pt_info, schedule.
>
> A little more of a hint please.
>
> bill
Try a newsgroup related to the SQL database you're using for help on
creating the correct SQL statement(s).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: comparing arrays [message #181059 is a reply to message #181048] |
Sun, 07 April 2013 18:22 |
Frank Steinmetzger
Messages: 2
Registered: April 2013
Karma: 0
|
Junior Member |
|
|
The Natural Philosopher wrote:
>> You then would need to PHP-fiddle the time and duration together for only
>> those appointments that are today in a nested for-loop.
>>
> why not elect only 'todays' date in the first place?
That's what I meant (well, implicitly). With the “only” I was referring to
the list of appointments of “only today”, which had been extracted with the
date-extracting funtion I didn't know about.
--
Gruß | Greetings | Qapla’
Please do not share anything from, with or about me with any Facebook
service.
The government has to make cuts, now 20 ministers have to share one brain.
|
|
|
|
| Re: comparing arrays [message #181060 is a reply to message #181054] |
Sun, 07 April 2013 19:33 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
bill wrote:
> On 4/7/2013 10:14 AM, Frank Steinmetzger wrote:
>> You select two different appointments that have the same date:
>>
>> SELECT s1.patient_number AS pn, s1.time AS t1, s2.time AS t2,
>> s1.duration AS d1, s2.duration AS d2
>> FROM schedule s1, schedule s2
>> WHERE s1.time=s2.time AND s1.time=<today's date>
>> AND s1.patient_number=s2.patient_number
>> AND s1.patient_number=<ID of patient of interest>
>> AND s1.id!=s2.id
>> AND s1.doctor_number!=s2.doctor_number
>>
>> Of course, those times don't work that way, b/c they have date *and*
>> time, and you want to deal with date only. I'm not into advanced SQL,
>> perhaps there is an SQL function to only extract and use the date part.
>
> there is
>>
>> You then would need to PHP-fiddle the time and duration together for only
>> those appointments that are today in a nested for-loop.
> select s1.user as s1user,
> s2.user as s2user,
> s1.apptTime as s1apptTime,
> s2.apptTime as s2pptTime,
> users.physician,
> users.user_id
> from schedule as s1 join schedule as s2 join users
> on (s1.patient_number = s2.patient_number
> and users.user_id = s2.user)
>
> where s1.patient_number = s2.patient_number
> and s1.appt_status = 'a'
> and s2.appt_status = 'a'
> and s1.patient_number = '5214'
> and s1.schedule_id != s2.schedule_id
> and physician = '1'
> and date(s1.apptTime) = date(s2.apptTime)
> and date(s1.apptTime) = '2013-04-03'
>
> did the job
SELECT s1.user AS s1user,
s2.user AS s2user,
s1.apptTime AS s1apptTime,
s2.apptTime AS s2pptTime,
users.physician,
users.user_id
FROM
(schedule AS s1 INNER JOIN schedule AS s2
ON (s1.patient_number = s2.patient_number
AND s1.appt_status = s2.appt_status)) AS tmp
INNER JOIN users
ON (users.user_id = tmp.user)
WHERE s1.appt_status = 'a'
AND s1.patient_number = 5214
AND s1.schedule_id != s2.schedule_id
AND physician = 1
AND DATE(s1.apptTime) = DATE(s2.apptTime)
AND DATE(s1.apptTime) = '2013-04-03'
probably does it better.
- Write queries so that you can tell names, keywords and function
calls apart. It is customary to write keywords and function
identifiers all-uppercase, and keep names either all-lowercase
(e.g., in MySQL and SQLite) or CamelCase (e.g., in MS SQL).
- Quote all names to avoid them being considered keywords, causing
a syntax error. (Since the SQL dialect is unknown, no recommendation
can be made how to quote.)
- Write explicitly what kind of JOIN it is.
- Add a condition for each single JOIN, so that the result set the next
table needs to be joined with is smaller.
- If you JOIN tables on values they have in common, it makes
no sense to filter for them again.
- JOIN on common values, filter (WHERE) on different ones, so that the
result set that needs to be filtered is smaller.
- Your keys are probably numbers. Do not write them as strings, then.
- You almost never want to write a query in PHP like this; see
“SQL injection”.
Further optimizations (like USING) are possible with MySQL (on-topic in
comp.databases.mysql).
> Thanks for the great suggestion.
You are welcome.
PointedEars
--
Anyone who slaps a 'this page is best viewed with Browser X' label on
a Web page appears to be yearning for the bad old days, before the Web,
when you had very little chance of reading a document written on another
computer, another word processor, or another network. -- Tim Berners-Lee
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]