CODEWITHSUNDEEP
mysqlsql
Joe
Joe

Reputation: 53

How to check if a variable equals to part of one of the columns in a table in the Database?

For example I have a variable $num = 123; and another one called `$name=joe;' , and there is a Database that contains a table called "data" and inside this table there are two columns (num [type=varchar(255)] - name[type=varchar(255)]) .

For example these query exists in the DB :
num = 123456 , name = joe

How to make a check that the first "3" numbers equals the $num variable and the name equals variable $name ?

I tried this but it didn't work:
SELECT * FROM data Where SUBSTRING(num , 0 , 3) = '123' AND name = 'joe'

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

In MySQL, substring indexing starts at 1:

WHERE SUBSTRING(num , 1 , 3) = '123' AND name = 'joe'

But LEFT() or LIKE would more commonly be used:

WHERE LEFT(num , 3) = '123' AND name = 'joe'
WHERE num = '123%' AND name = 'joe'

The advantage of LIKE is that it can make use of an index . . . even one on (name, num).

Upvotes: 1

Alex Howansky
Alex Howansky

Reputation: 53573

The MySQL substring() function is 1-based, not 0-based, so this should work for you:

SELECT * FROM data Where SUBSTRING(num , 1 , 3) = '123' AND name = 'joe'

Upvotes: 0

Related Questions