PHP MySQL Select no spaces

Question

My current query looks something like this:

$result = mysqli_query($con,"SELECT GroupID FROM guests WHERE FirstName='$FirstName' AND LastName='$LastName'");

However, it is possible that when entering their first name or last name, a guest may decide to put a space in their name (e.g. Anne Marie or annemarie or AnneMarie should all be the same person). What I'd like to do is to be able to execute my above code, but for the where statement to compare only based on letters and disregard spaces and capitalization. I hope this makes sense!

I will be pre-populating the table "guests" with my guest names. I don't want to enter their names with no caps or spaces, since the information will also be used to contact them.

Help?

relevant: http://stackoverflow.com/questions/10432086/removes-all-white-spaces-of-a-string-in-sql-server — cnick, May 24 '14 at 03:16

score 5 · Accepted Answer · answered May 24 '14 at 03:10

5

Capitalization is handled by the collation of the strings, but you can override it.

The where clause that does what you want could be written as:

WHERE lower(replace(FirstName, ' ', '')) ='$FirstName' AND
      lower(replace(LastName, ' ', '')) = '$LastName'

This assumes that $FirstName and $LastName are lower case and have no spaces.

That said, you should switch to a non-deprecated interface such as mysqli_ or PDO. And, you should use parameters instead of embedding the user-input directly into the query strings (this leaves your system susceptible to SQL injection).

answered May 24 '14 at 03:10

Gordon Linoff

1,242,037
58
646
786

Thank you for your answer and suggestions! Could you please further explain your mysqli comment? – user866339 May 24 '14 at 03:19
@user866339 . . . http://stackoverflow.com/questions/13944956/the-mysql-extension-is-deprecated-and-will-be-removed-in-the-future-use-mysqli. – Gordon Linoff May 24 '14 at 03:23

Rahul · Answer 2 · 2014-05-24T03:24:26.940

1

Use TRIM function to remove both leading and trailing spaces from string like below

SELECT GroupID 
FROM guests 
WHERE TRIM(FirstName)=TRIM('$FirstName')
AND TRIM(LastName)=TRIM('$LastName')

TRIM() will help you get rid of spaces at front or at end but per your post, if you have string like Anne Marie then you need to use REPLACE() function to remove all occurrences of spaces in your string (As Gordon have already pointed that in his answer)

REPLACE(LastName, ' ','')='$LastName'

edited May 24 '14 at 03:24

answered May 24 '14 at 03:10

Rahul

76,197
13
71
125

Thank you, but this doesn't answer my question about what to do with my pre-populated names – user866339 May 24 '14 at 03:16
@user866339, try trimming both the column name and input value in where condition. – Rahul May 24 '14 at 03:18

score 0 · Answer 3 · answered May 24 '14 at 03:21

$sql = " SELECT GroupID ". 
"FROM guests ". 
"WHERE FirstName='".TRIM('$FirstName')."' ".
"AND LastName='".TRIM('$LastName')."'";

Is the easiest way.

Another option if you want it: I try and store names, as lower case and then when using them force the first letter to be a capital.

$FirstName = strtolower($FirstName);
$LastName = strtolower($LastName);

when using them, you can use ucfirst($str), to capitalise the first character of the name.

ucfirst($FirstName) ucfirst($LastName)

PHP MySQL Select no spaces

3 Answers3