2

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.

Keith Power
  • 13,891
  • 22
  • 66
  • 135
  • How are you setting your value in the URL, you could give a delimiter and then explode it using that delimiter, effectivly separating your name. Or something like that – Bono May 18 '12 at 14:17
  • So you call your script like script.php?name=myname or script.php?name=my%20name? ... and why you even use GET and not POST? – djot May 18 '12 at 14:17
  • 4
    You have a mass security hole here. Read about SQL injection. – Madara's Ghost May 18 '12 at 14:18
  • 1
    I can't see why you can't just pass it with spaces. – Madara's Ghost May 18 '12 at 14:19
  • yes this is just for testing, I just used get in this example, I use script.php?name=myname – Keith Power May 18 '12 at 14:19
  • each business will have there own url such as mydomain.com/businessname, I am not sure it is good practice to give them mydomain.com/business name – Keith Power May 18 '12 at 14:23
  • Regarding the security hole, I'm hoping this is pseudocode, since the SQL statement isn't actually written in PHP code. But yeah, you need to use a parameterized query (WHERE name LIKE ?) or else manually escape the string ($name = mysql_real_escape_string($_GET["name"])). I don't know why someone downvoted this question, I've run into the situation many times myself, especially when I want to maintain, for example, a user table of usernames that are space-insensitive so that "johnpublic" and "john public" are considered duplicates. – King Skippus May 18 '12 at 14:24
  • why your are not passing name as "script.php?name=my name" ??? You can pass name with spaces... – Fahim Parkar May 18 '12 at 14:31
  • so you have to replace all spaces in the table column with nothing (so in table.colName "My Name" will get "MyName") and then compare to "myname" – djot May 18 '12 at 14:38

3 Answers3

6
// 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%'
djot
  • 2,952
  • 4
  • 19
  • 28
4

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%'
GDP
  • 8,109
  • 6
  • 45
  • 82
2

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.

King Skippus
  • 3,801
  • 1
  • 24
  • 24
  • Also, I don't think that MySQL supports calculated columns directly. There are clever workarounds such as creating a trigger to calculate the value on insert. If you're not using triggers for anything else, though, to ensure that your database isn't overly complicated, I personally think it's easier just to insert the calculated values with the rest of the data. (INSERT INTO blah VALUES ('My Name', 'myname');) If you want to use triggers, reference this: http://stackoverflow.com/questions/660417/how-do-you-assign-a-column-default-in-mysql-to-another-columns-value – King Skippus May 18 '12 at 14:28
  • thanks, seems the beast way, I was hoping that there might be something in mysql to tell it ignore spaces when searching, but that is not the case – Keith Power May 18 '12 at 14:39