Searching on AlphaNumeric Content Only
in [General]
|
From: sono-io on 2 Sep 2009 23:47 Is there is a way to search only for the alphanumeric content of field in a db? I have an itemID field that contains item #'s that include dashes, forward slashes, etc, and I want people to be able to search for an item # even if they don't enter the punctuation exactly. Here's an example: let's say there is an itemID of 4D-2448-7PS but someone omits the dashes and searches on 4D24487PS. Is it possible in PHP to have the find be successful, even if the search criteria doesn't exactly match what's stored in the field? If this is possible, I'd appreciate it if someone could just point me in the right direction so I can read up on it. Thanks, Frank
From: German Geek on 3 Sep 2009 00:26 Hi, It's definitely possible to do when you do it in PHP, but not sure about on the database side. You could read all records into memory and then iterate over it with something like: $toSearch = "4D24487PS" $charsToIgnore = array('-','+',...); foreach ($items as $k=>$item) { $itemVal = str_replace($charsToIgnore, '', $item); if (strcmp(str_replace($charsToIgnore, '', $toSearch), $itemVal) == 0) { $return = $item; break; } } This however might use a lot of memory, but if your DB is a manageable size it should be ok. You can probably optimise it by iterating over a db result set instead of reading everything into an array. Cheers, Tim ++Tim Hinnerk Heuer++ http://www.ihostnz.com 2009/9/3 <sono-io(a)fannullone.us> > Is there is a way to search only for the alphanumeric content of > field in a db? I have an itemID field that contains item #'s that include > dashes, forward slashes, etc, and I want people to be able to search for an > item # even if they don't enter the punctuation exactly. > > Here's an example: let's say there is an itemID of 4D-2448-7PS but > someone omits the dashes and searches on 4D24487PS. Is it possible in PHP > to have the find be successful, even if the search criteria doesn't exactly > match what's stored in the field? > > If this is possible, I'd appreciate it if someone could just point > me in the right direction so I can read up on it. > > Thanks, > Frank > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
From: Ben Dunlap on 3 Sep 2009 00:30 > Is there is a way to search only for the alphanumeric content of > field in a db? I have an itemID field that contains item #'s that include > dashes, forward slashes, etc, and I want people to be able to search for an > item # even if they don't enter the punctuation exactly. Not sure if there's anything specifically PHP-ish that will help you here, but I would be inclined to start by storing a stripped-down version of the item ID (alphanumeric characters only) in a separate column in the database table. Then, when a user enters some search data, I would remove non-alphanumeric characters, if any, from the user's input, and then search the stripped column with this normalized version of the input. If you want even fuzzier matching (inadvertent transpositions or an omitted character or two OK, for example), you might read about Levenshtein distance: http://en.wikipedia.org/wiki/Levenshtein_distance PHP has a levenshtein function but you'll have to figure out a way to use it efficiently with your data set. Or, if Levenshtein isn't quite right for your needs, the article above might at least point you in a useful direction. Ben
From: Ashley Sheridan on 3 Sep 2009 04:25 On Wed, 2009-09-02 at 21:30 -0700, Ben Dunlap wrote: > > Is there is a way to search only for the alphanumeric content of > > field in a db? I have an itemID field that contains item #'s that include > > dashes, forward slashes, etc, and I want people to be able to search for an > > item # even if they don't enter the punctuation exactly. > > Not sure if there's anything specifically PHP-ish that will help you > here, but I would be inclined to start by storing a stripped-down > version of the item ID (alphanumeric characters only) in a separate > column in the database table. > > Then, when a user enters some search data, I would remove > non-alphanumeric characters, if any, from the user's input, and then > search the stripped column with this normalized version of the input. > > If you want even fuzzier matching (inadvertent transpositions or an > omitted character or two OK, for example), you might read about > Levenshtein distance: > > http://en.wikipedia.org/wiki/Levenshtein_distance > > PHP has a levenshtein function but you'll have to figure out a way to > use it efficiently with your data set. Or, if Levenshtein isn't quite > right for your needs, the article above might at least point you in a > useful direction. > > Ben > What's wrong with using the wildcards that are built into most SQL variants? SELECT * FROM table WHERE item_id LIKE '%#abcdef' Will select all records where the item_id field ends in '#abcdef' Thanks, Ash http://www.ashleysheridan.co.uk
From: Andrea Giammarchi on 3 Sep 2009 04:56 Which DB? If it is MySQL, as example, you can simply use REGEXP syntax "^[a-zA-Z0-9]+$" via SELECT Regards > From: sono-io(a)fannullone.us > To: php-general(a)lists.php.net > Date: Wed, 2 Sep 2009 20:47:15 -0700 > Subject: [PHP] Searching on AlphaNumeric Content Only > > Is there is a way to search only for the alphanumeric content of > field in a db? I have an itemID field that contains item #'s that > include dashes, forward slashes, etc, and I want people to be able to > search for an item # even if they don't enter the punctuation exactly. > > Here's an example: let's say there is an itemID of 4D-2448-7PS but > someone omits the dashes and searches on 4D24487PS. Is it possible in > PHP to have the find be successful, even if the search criteria > doesn't exactly match what's stored in the field? > > If this is possible, I'd appreciate it if someone could just point me > in the right direction so I can read up on it. > > Thanks, > Frank > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > _________________________________________________________________ Drag n dropGet easy photo sharing with Windows Live Photos. http://www.microsoft.com/windows/windowslive/products/photos.aspx
|
Next
|
Last
Pages: 1 2 3 4 5 6 Prev: parse_ini_file problem [SOLVED] Next: Magento shows blank page. |