Search code examples
phpmysqlsqlcomparesql-like

PhP MySQL NOT LIKE does not return the correct value

I'm trying to query result using LIKE and NOT LIKE in PHP & MySQL.

I have a table BeaconEvent with 2 columns: BeaconID and EventID;

Table BeaconEvent:

BeaconID        EventID
-------------------------
5DBA-BC5B       Time0003
5DBA-BC5B       NLB0001
5264-9028       NLB0004
1F8B-35E6       NLB0005

When I run the MySQL command to select all BeaconID with EventID is a specific value, the query works fine but when I run the query to select BeaconID with EventID is not equal to specify value, it return all the BeaconID (which is not right, should exclude the BeaconID which has EventID equal to specific value)

Example query that works:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID = "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID LIKE "Time0003"

And these queries do not work:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID != "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID <> "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID NOT LIKE "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID NOT LIKE "%Time0003%"

Any suggestion to compare value which is not equal to specific one? Thanks in advance

Solution

  • Your data has the same BeaconID for two values:

    BeaconID        EventID
5DBA-BC5B       Time0003       
5DBA-BC5B       NLB0001

    Your queries work perfectly as they should: 

    SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID <> "Time0003";

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID = "Time0003";

    SqlFiddleDemo

    Output:

    ╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5DBA-BC5B ║
║ 5264-9028 ║
║ 1F8B-35E6 ║
╚═══════════╝

╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5DBA-BC5B ║
╚═══════════╝

    If you want to exclude it(and you are sure your data is correct) you can use:

    SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.BeaconID <> (SELECT BeaconID 
                               FROM BeaconEvent
                               WHERE EventID = 'Time0003')

    SqlFiddleDemo2

    Output:

    ╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5264-9028 ║
║ 1F8B-35E6 ║
╚═══════════╝