Page 2 of 2
Re: Build results page 1 search field multiple column searc
Posted: Thu Apr 25, 2013 3:37 am
by requinix
It's alright, no harm caused. Just made things take a little longer.
So what's this about testing columns returned?
Re: Build results page 1 search field multiple column searc
Posted: Thu Apr 25, 2013 3:55 am
by jonnyfortis
So what's this about testing columns returned?
right the item am testing has the following values
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
Posted: Thu Apr 25, 2013 4:05 am
by requinix
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.
Re: Build results page 1 search field multiple column searc
Posted: Thu Apr 25, 2013 4:23 am
by jonnyfortis
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
Posted: Thu Apr 25, 2013 12:45 pm
by requinix
Just an index. Add it with
Code: Select all
ALTER TABLE LOTTIE_products ADD FULLTEXT INDEX (`Product`, `Description`)
then your query becomes
Code: Select all
SELECT * FROM LOTTIE_products WHERE MATCH (Product, Description) AGAINST (%s IN NATURAL LANGUAGE MODE)
Re: Build results page 1 search field multiple column searc
Posted: Thu Apr 25, 2013 3:11 pm
by jonnyfortis
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
Posted: Thu Apr 25, 2013 4:14 pm
by requinix
It's an
index, not a column. Think of it like table metadata.
Re: Build results page 1 search field multiple column searc
Posted: Thu Apr 25, 2013 4:50 pm
by jonnyfortis
It's an index, not a column. Think of it like table metadata.
ok a table metadata is new to me so will have to do some research