How to Search for names in MySQL

Jassim Rahma

Hi, I have mySQL with first_name, middle_name and last_name fields. How can I pass a string from my php like param_name so when the user pass single name or multiple name it will split and search for the name in every fields.. For example if the user search for param_name = ""John Mathew Clark" it will do: first_name or middle_name or last_name LIKE "%John%" first_name or middle_name or last_name LIKE "%Mathew%" first_name or middle_name or last_name LIKE "%Clark%" Thanks, [Jassim](https://www.softnames.com)

Technology News @ www.JassimRahma.com

Mycroft Holmes

I don't know about MySQL but in SQL Server I have done the following in the past. Build a function that takes a string and splits it on a delimiter (space) and return a table. In your query left outer join the function, passing in your name field, on your name field with Name = or LIKE '%' + itemFromFunction + '%' Make the result set DISTINCT. Not sure if = or like is needed in the LOJ. This is an old SQL Server split function I dug up.

ALTER FUNCTION [dbo].[fn_Split]
(@List varchar(8000), @Delimiter char(1))

RETURNS @Results table
(Item varchar(8000),ID int Identity(1,1))

AS

begin
declare @IndexStart int
declare @IndexEnd int
declare @Length int
declare @Word varchar(8000)

set @IndexStart = 1
set @IndexEnd = 0

set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','

--Get rid of any tabs or returns
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')

while @IndexStart <= @Length
begin
set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)

If @Delimiter = char(32) 
	set @IndexEnd = charindex(Space(1), @List, @IndexStart)

if @IndexEnd = 0
	set @IndexEnd = @Length + 1

set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1

INSERT INTO @Results(Item)
SELECT @Word

end

return
end

Never underestimate the power of human stupidity RAH