PHP: LIKE after space?

Question

Right now i have a column called full_name where the first and last name gets stored. It is seperated by a normal space, so a name is stored like this: "Firstname Lastname". Now when im making this search users function, i have this:

$query = "SELECT full_name, id, user_name, sex, last_access, bostadsort FROM users WHERE full_name LIKE '$searchUser%'"; 

It works great by finding if you search by the firstname. I wish to make if you e.g type "lastname" then the user also will come up.

Now i find this quite my fault, that i started by having 1 column for the full_name and not firstname lastname columns.

So i wonder if i can do this without making two new columns and change rest of my code to work with firstname lastname new columns.. ?

Im using MySQL

Answer 1

... WHERE full_name LIKE '$searchUser%' OR full_name LIKE '%$searchUser' OR full_name LIKE '%$searchUser%'

will that do?

Answer 2

full_name LIKE '$name%' OR full_name LIKE '% $name'

You should however split this column into two separate columns. What about multiple forenames/surnames?

Answer 3

My recommendation is to get them in 2 separate fields. Otherwise use % wildcard as well in the beginning of the string.

Answer 4

Put a % in front of your variable to denote you want to match items at the end, a % in the back to denote you want to match the items in the front, and a % in front and back to denote the item you want to match is in the middle.