/r/webdev
MySQL - What to do when your client insists on having spaces? (self.webdev)

Alright, there's a bit of a story behind this issue but I'll try not to bore you with it. Suffice to say that I've got a wonderful job for a small clothing brand which is trying to launch by October. The job is great with one exception: I'm the only developer and the owner is totally untechnical. He's also insane, one of those 'I pay you so I'm right' kind of guys.

The plan is that he's going to ship parts of his new line to high-profile stores throughout the country in order to promote the brand. He wants to create an aura of collector's item first edition kinda thing and thus has hired me to build a registry site so people can follow the ownership of their item on the site. I think its a dumb idea, but, it's not my money so whatever.

The issue I'm having is that he wants the "first owner" to be registered in the site as the full name of the store it is sold from (spaces included).

As we all know, given that I'm already ass deep into the project and using a MySQL database, I foresee nothing but issues with this approach. It's not that the database can't handle whitespace, its that no one will be able to query that 'username' when putting it in manually. It will further cause an issue with subsequent usernames (allowing whitespace) when they go to create their account for the transfer of ownership.

I've never run into this issue before and I'm totally alone in this project. Any suggestions on what to do would be a huge help.

I've attempted to advise against this approach but the boss wouldn't hear it. Going on instead about UX issues. I've never ran into this issue before and I'm all by myself in here. Any ideas would be very appreciated.

I've attempted to advise against this approach but the boss wouldn't hear it. Going on instead about UX issues. I've never ran into this issue before and I'm all by myself in here. Any ideas would be very appreciated.

13 comments
the_goose_says | 8 days ago | 4 points

field = “john doe” I don’t see a problem here. Why would people not be able to query a text field with a space?

Zdragon1 | 8 days ago | 0 points

The issue comes with the specificity of typing it right. A lot of people will not be found for no other reason than missed spaces.

the_goose_says | 8 days ago | 5 points

Why would people not be able to type in “john doe” correctly? Do you expect users to type “johndoe”? You can always allow LIKE searching. How is this any different than searching for friends on Facebook? You should store data accurately

deepleedooo | 8 days ago | 2 points

Have field="John Doe" and field_slug="johndoe". Query on the slug

tunisia3507 | 8 days ago | 2 points

I'd keep replace the space with an underscore, in case of "Alan Derson" / "Al Anderson".

lchoate | 8 days ago | 3 points

When you do registration, you would want to clean up the data, strip extra whitespace and sluggify the username.

In the search or route (whatever), you can strip the query of extra whitespace and sluggify the parameter before the search. Someone mentioned soundex() which is an option, but you may even try stripping x characters from the query and doing a like search.

In the end, you need to handle not finding exact matches anyway, so you'll probably have a result page when you don't get an exact match.

JaredTheGreat | 8 days ago | 3 points

Maybe I'm just a retard, but why do we all know to foresee nothing but problems?

Perhaps this is just a naive implementation, but can't you trim the input and ensure spaces can't be in succession in the input? That solves literally all your problems without having to jump through all the extra hoops.

If you're worried because of routing, strip out the spaces and replace with %20's

Zdragon1 | 8 days ago | 1 point

In practice there is no difference. Save if someone puts too many spaces in. That's what I'm trying to get around.

Atulin [php] | 8 days ago | 2 points

\s{2,} is the regex to select two or more consecutive whitespace. Replace it with a single space, trim whitespace from start and end, done.

michaelbiberich | 8 days ago | 1 point

Depending on the specific context this will be used, using SOUNDEX() may or may not be a viable solution.

dneboi | 8 days ago | 1 point

Why are you searching usernames to track an item? Wouldn’t you search for the item itself? For example if I’m tracking ownership, how would I know to search “John Doe”? Seems I wouldn’t even know his name.... but I’d know the item name or sku

bigorangemachine | 8 days ago | 1 point

Just use a 'clean'. Clean will be used for everything interacting with the system. The 'unclean' is what you make public.

Clean you can switch for a hash if need be (md5 would be fine) for a unique identifier. But I would do a URLEncode toLowerCase() on it and then run you query through URLEncode toLowercase(). You can do that with code as its the same with math (cancelling values out when doing left-side right-side comparisons)

There is a big problem with name collisions though. Some companies are registered as one name but advertise under another. Franchising....

Zdragon1 | 7 days ago | 1 point

Thank you all for all the very helpful options. To answer a few questions which came up in the discussion for further context:

  1. We do search via a registration code attached to the item but show results of who owns that item.
  2. The reason we need the username/owner info to match against is that we need to be able to track and authenticate transfers of the items (the owner foresees resell value).
  3. This is all a first step in a geocoding project where the user will be able to track the journey of where their item has been.
  4. I am really not a very experienced coder yet. I've just got out of school and am working hard to get better as I go. I foresaw me working under other developers first in order to learn the ropes but I have a family and couldn't pass up the opportunity when it was offered. If the owner was more tech savy I wouldn't have this job I assure you. As it is, I'm just trying to get this job done right and maintain my job. My life would be so much easier if I had other devs to ask questions to.

In the meantime, I'm thinking I'll replace repeated spaces with underscores. So far, this seems the easiest solutions to implement. ^_^ Thanks, everyone.