CODEWITHSUNDEEP
phpmysql
Keith Power
Keith Power

Reputation: 14141

mysql search without spaces

I have a list of names in my database and I have to find the id of the name that I pass in the url.

My problem is that the names I pass in the url will not have a space in them while the saved record will in the database. When I search the database I get no records found.

e.g database record is "My Name" while what I will be passing in the url and searching with is "myname"

if(isset($_GET["name"])) $name = $_GET["name"];

SELECT id
FROM table
WHERE name Like '%$name%'

Thanks for any help.

Upvotes: 2

Views: 10313

Answers (3)

djot
djot

Reputation: 2947

// id don't know the exact syntax, but this is what you are searching for I guess:

// replace spaces with nothin temporarily, then check equal (=) not like (%%) if name is exactly the same (except the spaces)

SELECT id, REPLACE(name, ' ', '') AS tmp FROM table WHERE tmp='%$name%'

Upvotes: 6

GDP
GDP

Reputation: 8178

Obviously not the right way to store or search for, but if you must, try replacing the spaces with blanks like this:

SELECT id
FROM table
WHERE REPLACE(`name`,' ','') LIKE '%$name%'

Upvotes: 4

King Skippus
King Skippus

Reputation: 3826

I think best practice is to store another column, called something like name_nospaces and insert myname as a calculated value into it when you insert the My Name record. You could also create a view that contains myname as a column and query for it, but the advantage of another column in the original table is that it can be indexed for fast retrieval.

Upvotes: 2

Related Questions