Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.mixmin.net!news2.arglkargh.de!news.ecp.fr!news.muarf.org!nntpfeed.proxad.net!proxad.net!feeder2-2.proxad.net!newsfeed.arcor.de!newsspool2.arcor-online.net!news.arcor.de.POSTED!not-for-mail
Content-Type: text/plain; charset="UTF-8"
Message-ID: <4479633.DZxCAXMi2a@PointedEars.de>
From: Thomas 'PointedEars' Lahn <PointedEars@web.de>
Reply-To: Thomas 'PointedEars' Lahn <php@PointedEars.de>
Organization: PointedEars Software (PES)
Date: Sun, 07 Apr 2013 21:33:12 +0200
User-Agent: KNode/4.8.5
Content-Transfer-Encoding: 8Bit
X-Face: %i>XG-yXR'\"2P/C_aO%~;2o~?g0pPKmbOw^=NT`tprDEf++D.m7"}HW6.#=U:?2GGctkL,f89@H46O$ASoW&?s}.k+&.<b';Md8`dH6iqhT)6C^.Px|[=M@7=Ik[_w<%n1Up"LPQNu2m8|L!/3iby{-]A+#YE}Kl{Cw$\U!kD%K}\2jz"QQP6Uqr],./"?;=4v
Face: iVBORw0KGgoAAAANSUhEUgAAADAAAAAwBAMAAAClLOS0AAAAGFBMVEXTxa4RFk5dUWANED8PFEfy7+MGBiW+n3ZNF/QuAAACaElEQVQ4jVXUwVOcMBQG8Dc7Rc4PUntdWV2uxjDpGaGeozOp1woar4jd5t/v9wLstMwsA/ntlxdCAgUc1hjTc9/JCZfGoo3wG3HdmdAWrIJRHe7GM/TmpY5VFefuVcAkkPbLIaN8rmPmjloyZxgyR3GuJ4K0AGtJ2htz8o7yqikm759fldQXaMpbDzjKAG+8v+AugVTOPO5DOjLvGtUYQwh0CPjnVMyGd+8/GfUB5nLKJDD2aLDh5HYyMDJGDwQIo2ZmZcKbowNmAdB/AzyFhrmF2MHRb0QJJfaAnwGB6orZhoykLzJtGwF/xpYxI1dswomiUj3gTuAIqCn/4C7cULwGNBtwMTk3Y4LfKB5YUaOKBKYtpplm7u0vip8tU1NWWyI/7XdcSuIDoMt6rVHMWT0DbjHPGqDqZVSa6zleLcUTcIKLoMv3ueJluALtAo9B302zPPlrtiVScRdCjXvVh3e3JpYa/jjkuC9N+LrBMlz/eAN4eQijX2EdLo6c5tGGHwLyHFtXk89dDGHwCVhG9T0S/j55AhRZgkMCmUQXJ49TnS1wnQDvw0eAh9ICeMmEFbCnPMFzjAvsWoEWEFdYEx+S0MoUZ1gT1wId8+AF3Bl2OoEu906AUHx5VLw/gXYg/x84loOah/2UYNrgiwSwGO7RfUzVBbx/kgpckumGOi6QirtD6gkLTitbnxNol47S2jVc2vsN5kPqaAHT8uUdAJM4v/DanjYOwmUjWznGfwB7sGtAtor5BgofDuzaRj4kSQAqDakTsKORa3Q3xKi3gE1fhl71KRMqrdZ2AWNNg/YOhQyrVBnb+i+nEg4bsDA+egAAAABJRU5ErkJggg==
Subject: Re: comparing arrays
Newsgroups: comp.lang.php
References: <kjrlk6$8es$1@speranza.aioe.org> <kjrp0m$ote$1@dont-email.me> <kjru58$19d$1@speranza.aioe.org> <kjrv05$5lq$2@inn-newsserver.rz.tu-ilmenau.de> <kjs3lm$gmi$1@speranza.aioe.org>
MIME-Version: 1.0
Lines: 103
NNTP-Posting-Date: 07 Apr 2013 21:34:31 CEST
NNTP-Posting-Host: 93b806f6.newsspool1.arcor-online.net
X-Trace: DXC=JMl[KTJI[Pk9kIfcjg:0fdic==]BZ:afn4Fo<]lROoRankgeX?EC@@`59mb1gI=;^iDZm8W4\YJNlb@mF9jNikdeBoKGPB=12Ag`GATnk>[jdk
X-Complaints-To: usenet-abuse@arcor.de
Xref: csiph.com comp.lang.php:9002

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