Mysql syntax for a search engines with names

Question

I want to make a "search engine" with people in php. I have two columns. The first is with first_name and the secon is with last name i use this sql syntax:

SELECT * 
FROM users 
WHERE first_name OR last_name LIKE '$search_term%'

I wand the sql to search for first name and for last name the same time with out having one column with first_name and last_name together. Please Help !!!!

(And many other questions, no idea how often this exact code and question came up. Try [our search function](http://www.google.com/search?q=site:stackoverflow.com+php%20search%20database%20first_name%20last_name%20LIKE)!) — mario, Feb 24 '13 at 15:16

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

it should be

SELECT * 
FROM   users 
WHERE  first_name LIKE '$search_term%' AND 
       last_name LIKE '$search_term%'

But the query above performs full table scan because it doesn't use index. For better performance, search about FULL TEXT SEARCH.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:33

Community

1
1

answered Feb 24 '13 at 15:07

John Woo

258,903
69
498
492

Thanks men worked! but when i am typing the first name (works) and then th last name doesn't work – alexalikiotis Feb 24 '13 at 15:11
and doesn't work means? do you want that searching must be true for both condition? – John Woo Feb 24 '13 at 15:12
if that's the case, use `AND` not `OR` see my update – John Woo Feb 24 '13 at 15:20
I think that i have to use this in my syntax GROUP CONCAT – alexalikiotis Feb 24 '13 at 15:24
why `GROUP_CONCAT` how does your records look like? – John Woo Feb 24 '13 at 15:28
i have one column with first_name and one column with last_name – alexalikiotis Feb 24 '13 at 16:19
if the columns in just in a row, you need to use `CONCAT` not `GROUP_CONCAT`. eg `CONCAT(firstname, ' ', last_name) LIKE '$search_term%'` – John Woo Feb 24 '13 at 16:50
thanks me your last example works as i want it thanks! – alexalikiotis Feb 25 '13 at 16:58

Mysql syntax for a search engines with names

1 Answers1