It's alright, no harm caused. Just made things take a little longer.
So what's this about testing columns returned?
Build results page 1 search field multiple column search
Moderator: General Moderators
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Build results page 1 search field multiple column searc
right the item am testing has the following valuesSo what's this about testing columns returned?
the Product is "White Cupcake Box (6)"
the description is "Top quality plain white box with window to hold 6 cupcakes.This come flat packed, very easy to assemble"
if i type any exact phrases in from example "white box", or "white", or "box" then it returns the value, if i however input "boxes" it returns no results
Re: Build results page 1 search field multiple column searc
Right. Your query is literally "does the product name or description contain this string?" Fuzzy searches won't work.
Try using Full-text searches, which may or may not allow for some fuzzy searching I don't know. Add a FULLTEXT index on the columns you want to search, then try using MATCH...AGAINST (which doesn't need % wildcards) instead of a LIKE.
If that doesn't work or isn't good enough then you may have to break out bigger tools: third-party search engines/databases. Sphinx is the only name I can drop though. Basically you have that thing running in the background, kinda like how MySQL runs, then you (1) tell it about your data to index and (2) send search queries to it. Then it returns the search results.
Also something to consider is a Google custom search engine. Or whatever it's called. After a bit of setup, Google does all the work. It's less professional but very simple to do - as long as your site is publicly accessible and reasonable with SEO.
Try using Full-text searches, which may or may not allow for some fuzzy searching I don't know. Add a FULLTEXT index on the columns you want to search, then try using MATCH...AGAINST (which doesn't need % wildcards) instead of a LIKE.
If that doesn't work or isn't good enough then you may have to break out bigger tools: third-party search engines/databases. Sphinx is the only name I can drop though. Basically you have that thing running in the background, kinda like how MySQL runs, then you (1) tell it about your data to index and (2) send search queries to it. Then it returns the search results.
Also something to consider is a Google custom search engine. Or whatever it's called. After a bit of setup, Google does all the work. It's less professional but very simple to do - as long as your site is publicly accessible and reasonable with SEO.
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Build results page 1 search field multiple column searc
hmm....dont really want to go to google or anything like that, so do you mean I need add another column to Product and description and any other columns i need to search then and to the query?..
Re: Build results page 1 search field multiple column searc
Just an index. Add it with
then your query becomes
Code: Select all
ALTER TABLE LOTTIE_products ADD FULLTEXT INDEX (`Product`, `Description`)Code: Select all
SELECT * FROM LOTTIE_products WHERE MATCH (Product, Description) AGAINST (%s IN NATURAL LANGUAGE MODE)-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Build results page 1 search field multiple column searc
ALTER TABLE LOTTIE_products ADD FULLTEXT INDEX (`Product`, `Description`)
sorry i am being very dim.
so i need to add another column in the LOTTIE_products called index the a type of FULLTEXT?
And there isnt a FULLTEXT type if you mean that?
or have i got this totally wrong?
sorry for all the questions
Re: Build results page 1 search field multiple column searc
It's an index, not a column. Think of it like table metadata.
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: Build results page 1 search field multiple column searc
ok a table metadata is new to me so will have to do some researchIt's an index, not a column. Think of it like table metadata.