CODEWITHSUNDEEP
phpmysqlsql
Jethro Hazelhurst
Jethro Hazelhurst

Reputation: 3285

MySql exclude a result with a certain ID?

Here is my SQL statement

            SELECT * FROM schedule
            WHERE teacher_id = '$teacher_id'
            AND event_id <> '$event_id'
            AND seconds_start = '$start_time'
            OR (seconds_start < '$start_time' AND seconds_end > '$start_time')
            OR (seconds_start < '$end_time' AND seconds_end >= '$end_time')

As you can see, I want to exclude any results with a certain ID, however this seems to not be working because...

enter image description here

Shouldn't this behave as expected??

Upvotes: 0

Views: 251

Answers (3)

Thomas G
Thomas G

Reputation: 10206

You obviously don't understand Operator Precedence

You can rarely mix AND and OR operators in the same instruction, without specifying their scope with parenthesis.

Properly indenting your code following the parenthesis also help to understand your query:

    SELECT * FROM schedule
    WHERE teacher_id = '$teacher_id'
      AND event_id <> '$event_id'
      AND ( 
             seconds_start = '$start_time'
         OR (seconds_start < '$start_time' AND seconds_end > '$start_time')
         OR (seconds_start < '$end_time' AND seconds_end >= '$end_time')
        )

Upvotes: 3

Mike Scotty
Mike Scotty

Reputation: 10782

Your OR part makes your statement ignore your limitation of the event_id.

As Jon Stirling pointed out, you need to set your parenthesis accordingly.

    SELECT * FROM schedule
    WHERE teacher_id = '$teacher_id'
    AND event_id <> '$event_id'
    AND (
        seconds_start = '$start_time'
        OR (seconds_start < '$start_time' AND seconds_end > '$start_time')
        OR (seconds_start < '$end_time' AND seconds_end >= '$end_time')
    )

Upvotes: 1

follio
follio

Reputation: 547

Maybe you need to group your second_start

    SELECT * FROM schedule
    WHERE teacher_id = '$teacher_id'
    AND event_id <> '$event_id'
    AND (seconds_start = '$start_time'
    OR (seconds_start < '$start_time' AND seconds_end > '$start_time')
    OR (seconds_start < '$end_time' AND seconds_end >= '$end_time'))

Upvotes: 1

Related Questions