Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #9002

Re: comparing arrays

Show key headers only | View raw

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

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

comparing arrays bill <william@TechServSys.com> - 2013-04-07 07:34 -0400
  Re: comparing arrays Jerry Stuckle <jstucklex@attglobal.net> - 2013-04-07 08:34 -0400
    Re: comparing arrays bill <william@TechServSys.com> - 2013-04-07 10:00 -0400
      Re: comparing arrays Frank Steinmetzger <frank.steinmetzger@stud.tu-ilmenau.de> - 2013-04-07 16:14 +0200
        Re: comparing arrays Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-04-07 17:13 +0200
        Re: comparing arrays bill <william@TechServSys.com> - 2013-04-07 11:34 -0400
          Re: comparing arrays Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-04-07 21:33 +0200
      Re: comparing arrays The Natural Philosopher <tnp@invalid.invalid> - 2013-04-07 16:01 +0100
        Re: comparing arrays Frank Steinmetzger <frank.steinmetzger@stud.tu-ilmenau.de> - 2013-04-07 20:22 +0200
      Re: comparing arrays Jerry Stuckle <jstucklex@attglobal.net> - 2013-04-07 14:18 -0400
  Re: comparing arrays Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-04-07 15:37 +0200
    Re: comparing arrays bill <william@TechServSys.com> - 2013-04-07 09:59 -0400
      Re: comparing arrays Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-04-07 17:14 +0200
  Re: comparing arrays The Natural Philosopher <tnp@invalid.invalid> - 2013-04-07 15:59 +0100

csiph-web