Build results page 1 search field multiple column search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post by requinix »

It's alright, no harm caused. Just made things take a little longer.

So what's this about testing columns returned?
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post 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.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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?..
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post 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)
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: Build results page 1 search field multiple column searc

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Build results page 1 search field multiple column searc

Post by requinix »

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

Post 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
Post Reply