searching multiple fields

[1internet]

I want to search across multiple fields, e.g.

city = London

category = restaurant

name = Acme Steakhouse

Description = the best steaks in town, fillet, rump, t-bone

 

So if I was searching for the above result, I would want it to be displayed with searches like "London restaurant", "London Steakhouse", "t-bone restaurant", "Acme Fillet", you get the idea, I just can't work out how to transfer across multiple search words to each field. In fact I really have no idea how this would be done.

Is there a trick to this?

[Failing_Solutions]

Not sure if I completely understood the question but here are my thoughts...

 

Query = "London Restaurant"

Use a pre processor like PHP to break it up and set it each section to a variable

$piece1='London'

$piece2='Restaurante'

 

 

SELECT * FROM table
WHERE
city RLIKE '$piece1|$piece2'
OR
category RLIKE '$piece1|$piece2'
OR
name RLIKE '$piece1|$piece2'
OR
description RLIKE '$piece1|$piece2'
Order by city, category, name, description

 

Also there is the LIKE (%$piece1%)

and

city IN('london','restaurante','steak) operators

Edited January 24, 2013 by Failing_Solutions

[1internet]

Right, I see, but what if there are 3 or 4 keywords, how can we have $piece1,2,3,4? Would it need to be an array?

Is this the best way to handle a search?

[Failing_Solutions]

There are many ways to handle a string of words in php

 

for example

 

Search = London Steak Resturante

<?php
$string=$_POST['search'];
$array=explode(' ',$string); // Explode on space
$piece1=$array[0]; ///London
$piece2=$array[1]; ///Steak
$piece3=$array[3]; ///Resturante
?>

Sometimes like in the case of a search you may not know how many pieces you'll have so then you would use loops like

foreach($array as $value) {

do something;

}

Searchs can get very complicated because the idea is to return the results that are the most appropriate. I've seen large Content Management Systems with wreched searches. Google on the other hand is probably the gold standard for getting the best search results.

 

All that said it is possible to do this sort of thing

Edited January 24, 2013 by Failing_Solutions

[1internet]

Yea, I want to keep it fairly simple, but just make sure that all the results that should be shown are, e.g. london restaurants search would return all the restaurants in London. I am not too concerned about order just yet, I guess that is going to be another kettle of fish.