Geek Answers Handbook

Using 'OR' between a HAVING clause and a WHERE clause in MySQL?

I am trying to retrieve records in MySQL using a simple used field. More precisely, the user enters a name (first or last name or full name), and the server should return the matched strings.

What I'm doing so far looks something like this:

SELECT * FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%'

This works well, but it (obviously) will not work if the user submits the full name. Is there a way to add OR between integer conditions of type WHERE and conditions of type HAVING? That way I could do something like:

SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%' OR 
   HAVING fullname LIKE '%user_submitted_data%'

I know that I can just break the original line, but this has a negative effect, since you have to deal with names containing spaces such as "De Gaule" and the like.

+5
5

:

SELECT [some fields]
FROM
  SELECT firstname, lastname, CONCAT(firstname, ' ', lastname) as fullname
  FROM people) AS tmp
WHERE firstname LIKE '%user_submitted_data%'
OR lastname LIKE '%user_submitted_data%'
OR fullname LIKE '%user_submitted_data%'
+4

HAVING.

SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
FROM people 
HAVING firstname LIKE '%user_submitted_data%'
OR      lastname LIKE '%user_submitted_data%'
OR      fullname LIKE '%user_submitted_data%

WHERE , , , HAVING, WHERE.

+8

:

John
Smith
John Smith

:

SELECT * FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%'

, , , ""; , "" (, ). , , ""; , "" (, ). ; - ( , , , ), .

, " "; , " ". , , - , , . , .

, 'fullname'. , , . , , , .

SELECT *
  FROM (SELECT firstname || ' ' || lastname AS fullname, ... FROM people) AS t 
 WHERE t.fullname LIKE '%user_submitted_data%'

.

, , " " ( " " ) " " ), , - " " "" , . , , "%". , , - , , . , " ", " "; - ", ", ( ).

, , , . , - (, - "de" ) - , (John vs Johnson), LIKE SQL .

+1

WHERE, :

SELECT p.*
FROM (
  SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
  FROM people
) p
WHERE 
   p.firstname LIKE '%user_submitted_data%' OR 
   p.lastname LIKE '%user_submitted_data%' OR 
   p.fullname LIKE '%user_submitted_data%';

, , , , LIKE - . FULLTEXT:

CREATE FULLTEXT INDEX people_names ON people(firstname, lastname);

SELECT *
FROM people
WHERE MATCH(firstname, lastname) AGAINST( ? );

PS: FULLTEXT MyISAM. , - Sphinx Search .

0

, , - .

(firstname || ' ' || lastname) ? , .

, , ,

WHERE firstname || ' ' || lastname LIKE '%user_submitted_data%'

, OR .

0
source

More articles:


All Articles