Ben Ben - 2 months ago 5
MySQL Question

Why does mySQL include client_num columns with value zero / 0 in my query

In 2 tables I query for countries or client numbers like this :

SELECT
client_num,
countries.en as country,
FROM clients, countries
WHERE clients.country_id = countries.ID
AND ( countries.en =:keyword
OR client_num =:keyword )


The keyword let's say is
Spain
. For this I get a result of 150 entries. But in the table are only 140 countries named
Spain
. The reaseon why I get 10 more is because those entries have values of
zero / 0
in their
client_num
column. The column is of type
INT
whith 0 as default.

I can exclude those wrong entries like this :

SELECT
client_num,
countries.en as country,
FROM clients, countries
WHERE clients.country_id = countries.ID
AND ( countries.en =:keyword
OR ( client_num != 0 AND client_num =:keyword ) )


Now I get the correct result with 140 entries. But why ?

EDIT :

Adding and refering to @ Kamil G. answer : The 10 more matched entries do not have
Spain
in their respective countries.en column.

EDIT 2 :

I must add that clients and countries come from 2 different DB's. I left it out to make it more simple. So here is the complete query :

SELECT
db_one.clients.client_num ,
db_two.countries.en as country ,
FROM db_one.clients , db_two.countries
WHERE db_one.clients.country_id = db_two.countries.ID
AND ( db_two.countries.en =:keyword
OR ( db_one.client_num != 0 AND db_one.client_num =:keyword ) )

Answer

The problem with your query is that in the test client_num = :keyword, you are testing an integer against a string. The string is then converted to the integer 0, and the test succeeds.
In order to solve this, you have to only compare client_num to :keyword when client_num is non-zero or to convert client_num to a string before doing the comparison (which will work even when :keyword has the value "0"):

SELECT client_num, countries.en as country, 
FROM clients INNER JOIN countries ON clients.country_id = countries.ID
WHERE (countries.en = :keyword OR CAST(client_num AS CHAR) = :keyword ) 

EDIT:
As there seem to be no connection between countries.en and client_num, it would be better to have separate variables for these in the query, like this:

SELECT client_num, countries.en as country, 
FROM clients INNER JOIN countries ON clients.country_id = countries.ID
WHERE (countries.en = :keyword OR client_num = :clientnum )

If :keyword comes from a search form, this involves the addition (and validation) of a new field. The introduction of a new field can be avoided by setting :clientnum in the code prior to executing the search, like this (pseudocode):

IF is_numeric(keyword) THEN
   clientnum = keyword
ELSE
   clientnum = -1

Similar code should be used for validation of a separate clientnum field in order to avoid the original problem (that an empty/non-numeric value is converted to 0).

Comments