I am selecting a full name from two columns in mysql table but the input takes only one order to return the information.
firstname | lastname
Amaj | Ato
With this data I want to select the full name (Amaj Ato or Ato Amaj) from one text box when the user enters Amaj Ato or vice versa (Ato Amaj). This is what I have tried so far
`SELECT * FROM `table_name` WHERE concat_ws(' ', 'firstname', 'lastname') like '%$fullname%'; `
This is able to select the firstname and lastname in only one order. For instance if the user enters Amaj Ato the query gives the full name but when the user enters the firstname after the lastname ( like Ato Amaj), the query returns empty. I want the user to get the full name in any order he enters the names. Thanks for helping.
You need to "tell" the query what you are looking for, it doesn't magically know that names could be switched. To do this, you can simply add an OR specifying the other valid case:
SELECT * FROM `table_name`
WHERE concat_ws(' ', 'firstname', 'lastname') like '%$fullname%'
OR concat_ws(' ', 'lastname', 'firstname') like '%$fullname%'