MySQL compare a number with string returns true if string starts with number

Navigation
1

I have the following table in the database:

idUser - Username - Group

1 - John - 5GR

2 - Doe - 1GR

And the following query: 

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

$ name is the variable that contains the value that the user enters.

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

When the user enters 2, only the user with idUser = 2 is displayed. 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 user John is also presented because his idUser = 1 and the string entered by the user contains 1. The problem is of the comparison with the idUser, I will check if removing this comparison of the query it presents correctly (however the user can no longer search for id user and this is essential).

Is there any way to arrange this?

    
asked by anonymous 24.05.2018 / 11:18

1 answer

2

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

CAST('idUser' AS CHAR)

Note that there is also an error in query , a ' is missing in Group .

Applying the adjustments to your case: 

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

Do not forget to use mysqli_real_escape_string() on the PHP side to avoid SQL injection if the user types 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 any of the two fields

    
24.05.2018 / 11:58
Create a pdf on android Javascript find part of text and replace (with variable)