CODEWITHSUNDEEP
phpmysql
user866339
user866339

Reputation: 439

PHP MySQL Select no spaces

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?

Upvotes: 0

Views: 1873

Answers (3)

Rahul
Rahul

Reputation: 77866

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'

Upvotes: 1

TheDuncan
TheDuncan

Reputation: 50

$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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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).

Upvotes: 5

Related Questions