[alane]

Hi
I have a working database and search facility that I need to refine a bit but am having problem with
.

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 "; 

The field 'models' might contain an entry such as "Potterton Puma 80e" and if the site visitor enters a search string ($entry) enters say "puma 80e" the select statement returns the correct entry. If they enter "puma 80 e" it doesn't obviously.

I can easily modify the search string $entry to remove all the spaces but how do I modify the WHERE statement so that the database entry 'model' is also minus any spaces?

Using the above example then 'model' would end up as "PottertonPuma80e" and the search string would be "puma80e". Then if site visitor added extra spaces (or left any out) the select statement would find the entry?

Hope that makes sence!!

[job0107]

Let's assume that you have already used str_replace() on $entry to remove all the spaces.

PHP Code:

$entry = str_replace(" ","",$entry); 

And now you want to remove all the spaces from the model field before you use it in the query.
Sounds funny, but that's kinda what you have to do, only you can do it right in the query

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
    (REPLACE(model,' ','') like '%$entry%') AND class = 1
ORDER BY
    brand, model "; 

[alane]

Thanks for that. It was neat and really works a treat.

My site has several different search facilities and having seen your reply would like to ask is there a way that I can include in a select statement a way of only comparing the alpha numeric part of a columns content (which includes - & / symbols) such as the mysql equivilent of the following in php?

PHP Code:

$entry = ereg_replace("[^A-Za-z0-9]", "", $entry); 

[job0107]

I am sorry, but there is no easy way to do a regular expression replace with MySql.
Maybe they will add it in the future.

[alane]

Thanks for taking time to help me here.

[job0107]

I suppose that if you have only a few symbols like " - , / , & , space",
then you could use a nested REPLACE and remove each symbol one at a time.

Example:

This example will remove all dashes ( - ), forward slashes ( / ), and-signs ( &) and spaces " ".

The inner REPLACE replaces all spaces.
The next outer REPLACE replaces all and-signs.
The next outer REPLACE replaces all forward slashes.
And the last outer REPLACE replaces all dashes.

Each preceding REPLACE is used as the object for each successive REPLACE.
I am not sure how many levels can be nested, but I am sure it's quite a few.

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
    (REPLACE(REPLACE(REPLACE(REPLACE(model,' ',''),'&',''),'/',''),'-','') like '%$entry%') AND class = 1
ORDER BY
    brand, model "; 

[carters-site]

Code:

(REPLACE(REPLACE(REPLACE(REPLACE(model,' ',''),'&',''),'/',''),'-','') like '%$entry%')

I think parts of my brain just melted. Had never seen anything done in MySQL that gross before. Course it is a solution.

[alane]

Had never seen anything done in MySQL that gross before. Course it is a solution.

That may be true, but tried it out last evening and the best bit is IT WORKS!!

I might over the next few days change the enteries in the actual table so that I only have to nest the REPLACE 2 levels but this use of the REPLACE achives exactly what I wanted - Thanks.

It certainly helps solve a problem that doesn't seem otherwise possible in MYsql.

[job0107]

Originally Posted by carters-site

Code:

(REPLACE(REPLACE(REPLACE(REPLACE(model,' ',''),'&',''),'/',''),'-','') like '%$entry%')

I think parts of my brain just melted. Had never seen anything done in MySQL that gross before. Course it is a solution.

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.

[alane]

I don't care what others think.

I came with a problem that couldn't think of a way of solving and came here as I have done previously when I hit problems with various scripts. By trade I repair household boilers and whilst I might be good at that I struggle with PHP, databases etc.

job came up with a solution that works, also a welcome explaination of how, and has helped improve the functionality of my site no end. To say I am happy is an understatement.

Maybe 'carters-site' might consider it gross but just maybe they are just a tad jelous they never thought of the idea.

Again job0107 (Jerry?) my thanks.