Mysql comparing a number with string returns true if the string starts with the number

Question

I have the following table in the database:

idUser - Username - Group

1 - John - 5GR

2 - Donate - 1GR

And the following query:

    SELECT * FROM `table` WHERE `idUser` = '$name' OR `Group = '$name'

$name is the variable that contains the value you enter.

When the user enters 1, only the user with idUser=1 is shown. This is correct.

When the user enters 2, only the user with idUser=2 is shown. This is also correct.

The problem is when the user enters '1GR'.

The correct would be to display only the user with Group = 1GR, in this case the user Doe.

However the John user is also shown because his idUser = 1 and the string entered by the user contains 1. The problem is the comparison with idUser, I checked whether to remove this comparison from the query it presents correctly (however the user can no longer search for user id and this is essential).

Is there any way to fix this?

Answer 1

The problem is that there is an implicit cast of string sought, because you are comparing a number with a string. You can force the interpretation of idUser as string in this way:

CAST(`idUser` AS CHAR)

Note that there is also a little mistake in query, missing a ` in the Group.

Applying adjustments to your case:

SELECT * FROM `table` WHERE CAST(`idUser` AS CHAR) = '$name' OR `Group` = '$name'

Don’t forget to use the mysqli_real_escape_string() on the PHP side to avoid SQL injection if the user type the character ' (or other special characters) in the search.

Simplifying the query

As you search for the same value in more than one column, you can write this way:

SELECT * FROM `table` WHERE '$name' IN ( `Group`, CAST(`idUser` AS CHAR) )

More details here:

Check if the same value is in one of the two fields