SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    New York
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please help me with this complex "verify" query

    Here is the logic to the "verify" query. It is looking to make sure that a time slot has the entire timeframe available. If a slot starts the same as one ends, it is acceptable and considered free time (ex. a schedule ending at 4pm can be followed by another starting at 4pm). I implemented these rules to define a record that conflicts with the time space available.
    • Day must match so comparison will make sense.
    • Neither the starting time nor the ending time of the new schedule can be in between the start or select time of any other slot (within the matching day results).
    • Start time of new show must not match end time of compared schedule.
    • End time of new show must not match start time of compared schedule.


    This is the best I can come up with in terms of rules.
    Here is the query building function, keep in mind there is a join involved. It is negligible in your troubleshooting:
    Code:
    select
    
    schedule.schedule_id, schedule.radioshow_id, radioshow.name
    from
    schedule
    left join
    radioshow
    on
    radioshow.radioshow_id = schedule.radioshow_id
    where
    schedule.day = '.$day.' && (
    '.$timeStart.'
    between
    schedule.showstart and schedule.showend || '.$timeEnd.'
    between
    schedule.showstart and schedule.showend
    )
    && (
    schedule.showend != '.$timeStart.' && schedule.showstart != '.$timeEnd.'
    )
    order by
    schedule.showstart asc
    Could it be misuse of the parenthesis?
    Here is the error message also:

    You have an error in your SQL syntax. Check the manual [blah blah...] for the right syntax to use near ':00:00 between schedule.showstart and schedule.sho' in...

    This message shows the 'and' keyword. I bolded the section of the query that has the only two uses of the word 'and' in it. Hope this is enough information, and that you guys can help me out. Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try replacing the && and || to proper sql keywords AND and OR (or OR and AND, as the case may be)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    New York
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm running MySQL. But since that is standard I will use them from now on. However that did not solve my problem.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, great, thanks

    now let's move on to the problem

    what does the query look like now, and what is the error message it produces?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    New York
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Same error message. Here is the query string:

    select
    schedule.schedule_id,
    schedule.radioshow_id,
    radioshow.name
    from
    schedule
    left join
    radioshow
    on
    radioshow.radioshow_id = schedule.radioshow_id
    where
    schedule.day = sunday and
    (
    01:00:00
    between
    schedule.showstart and schedule.showend or
    02:00:00
    between
    schedule.showstart and schedule.showend
    )
    and
    (
    schedule.showend != 01:00:00 and
    schedule.showstart != 02:00:00
    )
    order by
    schedule.showstart asc

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    schedule.day = sunday

    needs quotes around sunday if schedule.day is actually a character datatype, if not, it needs a different value

    all times need quotes, e.g. '01:00:00'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    New York
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I venture into new things with database queries, and I forget to add quotes, heh.

    Thank you for your patience.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •