In that case, the regular expression needs to allow the entered string to be more tolerant.
Thus - if you modify the regular expression to allow characters between those entered, it should match.
This code is for phone numbers, but it may be helpful here, too. Instead of replacing all the digits in the string, you could replace every character.
PHP Code:
<?php
/* A list of phone numbers to test for matches */
$aPhoneNumbers=array(
'7085556232',
'1(708)555-6232',
'(708)555-6232',
'708.555.6232',
'1.708.555.6232',
'1.708.555.6232 ext. 123',
'17085556232',
'7215556232',
'1(721)555-6232',
'(721)555-6232',
'721.555.6232',
'1.721.555.6232',
'17215556232'
);
/* sPhone is the phone number */
$sPhone='7085556232';
/* Pattern is all digits */
$rPattern='/(\d)/';
/* Replace all non-digits */
$rReplace='\D*${1}';
/* Create the regular expression */
$sRegExp='/'.preg_replace($rPattern,$rReplace,$sPhone).'/';
/* Display it */
echo 'Regular Expression: '.$sRegExp."\n";
/* Run the test on all the input */
foreach ($aPhoneNumbers as $k => $v)
check($sRegExp,$v);
/* Test function */\
function check($sRegExp,$sPhone)
{
echo '$sPhone: '.$sPhone.' ? '.preg_match($sRegExp,$sPhone)."\n";
}
?>
Although I used PHP to develop the regexp logic, I was able to convert it to use the MySQL equivalents - like :digit:, etc.
In that case, the regular expression needs to allow the entered string to be more tolerant.
Thus - if you modify the regular expression to allow characters between those entered, it should match.
This code is for phone numbers, but it may be helpful here, too. Instead of replacing all the digits in the string, you could replace every character.
PHP Code:
<?php /* A list of phone numbers to test for matches */ $aPhoneNumbers=array( '7085556232', '1(708)555-6232', '(708)555-6232', '708.555.6232', '1.708.555.6232', '1.708.555.6232 ext. 123', '17085556232', '7215556232', '1(721)555-6232', '(721)555-6232', '721.555.6232', '1.721.555.6232', '17215556232' );
/* sPhone is the phone number */ $sPhone='7085556232';
/* Pattern is all digits */ $rPattern='/(\d)/';
/* Replace all non-digits */ $rReplace='\D*${1}';
/* Create the regular expression */ $sRegExp='/'.preg_replace($rPattern,$rReplace,$sPhone).'/';
/* Display it */ echo 'Regular Expression: '.$sRegExp."\n";
/* Run the test on all the input */ foreach ($aPhoneNumbers as $k => $v) check($sRegExp,$v);
/* Test function */\ function check($sRegExp,$sPhone) { echo '$sPhone: '.$sPhone.' ? '.preg_match($sRegExp,$sPhone)."\n"; } ?>
Although I used PHP to develop the regexp logic, I was able to convert it to use the MySQL equivalents - like :digit:, etc.
Output
The .? indicates any single character. You may want to adjust that to [.*]? - meaning any string of characters, or something more specific.
Output doesn't match the code - but - the general idea is to intersperse the target characters with others such that it will match.
In the above code $v would represent the value from the model column in the database table.
So how do you plan to insert the value from the model column into your check() function?
You still haven't showed us how you used it in the SELECT query.
According to wirehopper, this should work pretty good:
PHP Code:
$sRegExp = preg_replace('/\W/', '[[:punct:][:print:]]*', $entry); $query = "SELECT model, brand, gc_number, dir, manuals_pdffiles.id, pdf_file, category, pdf_info, DATE_FORMAT(date_available, '%d %b %y') AS Fdate, safety, asbestos, class FROM manuals_models LEFT JOIN manuals_pdffiles ON manual_id = manuals_pdffiles.id LEFT JOIN manuals_brands ON brand_id = manuals_brands.id WHERE model REGEXP '$sRegExp' AND class = 1 ORDER BY brand, model ";
Well, if you think you can come up with a better solution for this particular situation, I would be very interested in seeing it.
Otherwise your criticism is just so much hot air.
Thanks for nothing.
I think it's a fine solution, and like Alane said, it works. What more could anyone want?
I'll take working code over pretty code any day of the week.
Also, FWIW, I don't think carters-site was really criticizing the solution. He may have been a tad inelegant in his response with the word "gross", but I didn't read it as outright criticism. I may be wrong.
Finally, a little late to the party, but I did find this, from Hartmut Holzgraefe:
My Regular Exrepssion UDFs for MySQL have been available on MySQL Forge for a while already, now i've taken the time to create a Trac project page for them. I've implemented the following four functions:
Also, FWIW, I don't think carters-site was really criticizing the solution. He may have been a tad inelegant in his response with the word "gross", but I didn't read it as outright criticism. I may be wrong.
^^^ Yeah my tone was lost a little (happens when you only have text). Just because I use the word "gross" does not mean I was saying I did not respect your solution Job it worked and required some thought so do not take offense.
I chose the term "gross" because I use it a work a lot when I write something that is visually crypitc or (not pretty) I term it as "gross" it does not mean it is not functional or not a good solution to the problem.
I have used plenty of gross solutions to problems in MySQL because lets face it...MySQL forces us treat it like the cheap dirty girl it is. *cough* like creating a 65 column table to handle scaling issues with large stat databases instead of storing a date field like a normal human being.
So Job my apologies if my context came off rougher then I intended. My major was in Computer Science, my english skills have always and will always be terrible.
Sorry for delay replying but I wanted to use weekent to try out REGEXP suggestion but couldn't get it to work.
Do I just have to replace my original code
PHP Code:
$query = "SELECT
model,
brand,
gc_number,
dir,
manuals_pdffiles.id,
pdf_file,
category,
pdf_info,
DATE_FORMAT(date_available, '%d %b %y') AS Fdate,
safety,
asbestos,
class
FROM
manuals_models
LEFT JOIN
manuals_pdffiles ON manual_id = manuals_pdffiles.id
LEFT JOIN
manuals_brands ON brand_id = manuals_brands.id
WHERE
(model like '%$entry%') AND class = 1
ORDER BY
brand, model ";
with this or do I have to change anything else?
PHP Code:
$sRegExp = preg_replace('/\W/', '[[:punct:][:print:]]*', $entry);
$query = "SELECT
model,
brand,
gc_number,
dir,
manuals_pdffiles.id,
pdf_file,
category,
pdf_info,
DATE_FORMAT(date_available, '%d %b %y') AS Fdate,
safety,
asbestos,
class
FROM
manuals_models
LEFT JOIN
manuals_pdffiles ON manual_id = manuals_pdffiles.id
LEFT JOIN
manuals_brands ON brand_id = manuals_brands.id
WHERE
model REGEXP '$sRegExp' AND class = 1
ORDER BY
brand, model ";
Linear Mode
