Forum Moderators: coopster
i need it to return RECORDS from the MYSQL DATABASE on a page in a weighted order.
some searches would be from hyperlinks and some with free text entry and other form elements.
i need it to weight results on:
[free] - gallery description: weighted on keywords
[link] - product range: database weighted 1-5 on product e.g. foo 2, boggle 1, snap 0.
[select] - price range: within price bands e.g. 0-$100 etc.
[link] - geographical 1: region
[link] - geographical 2: city
[link] - geographical 3: other
[radio] - with/without website:
all are represented as MYSQL fields for specific rows.
i'm thinking about buying a book, does anyone know a good one with a CD included (so i don't have to do all the PHP scripting).
SC
With php/mysql you can do 2 types of searching (as far as I know). The best thing is to have php place the most important field results into a field in your table you could call searchterms (or whatever). You could separate them with +
The options are:
Keyword searching (with a wildcard (%)i.e take an input from a search form (e.g. entitled searchterm and append it to the url of a header or whatever:
e.g.
$searchterm = $_POST['searchterm'];
header ("Location: h**p://www.example.com/search.php?searchterm=$searchterm");
In 'search.php' you could have:
if (isset($searchterm)) {
$query = "SELECT your fields FROM your table WHERE searchterms LIKE '%$searchterm%' ORDER BY price ASC";
The '%' is the wildcard, i.e. as long as the term is in the search somewhere, it will be returned.
The other method, much faster, but without the advantage of the wildcard, is full text searching. There are two good articles I know of, one at [zend.com...]
and the other at
[devarticles.com...]
A good way is to put all likely search terms for each record into a field in the mysql table, then do a full text index of the field in sql:
fulltext(searchterm);
Then receive the input from the searchform.
If using an advanced search form, use php to append each term to the url separated by a + or a space e.g. when checking the form, keep adding to $searchterms
$searchterms = "$searchterms" . "+" . "{$_POST[price]}";
Then, on your results page, run a query like:
if (isset($searchterms)) {
$query = "SELECT the fields you want to return, MATCH(searchterms) AGAINST ('$searchterms') AS score FROM joblistings WHERE MATCH(searchterms) AGAINST('$searchterms') ORDER BY score DESC";
Apparently repeating the 'Match' bit makes the search faster.
The above example will return the results according to the relevance to the search query. To change the listing order, you just alter the 'ORDER BY' part. You could offer that as an option to the user, and then use the appropriate query.
I'm sorry all this is a bit garbled, but the articles should also help. The best php/mysql books I know of are Larry Ulman: PHP and MySQL for dynamic web sites and also PHP Advanced for the World Wide Web, but he doesn't go into full-text searching.
Rob
[edited by: ergophobe at 7:23 pm (utc) on Sep. 30, 2004]
[edit reason] snipped commercial URL as per TOS; delinked example url [/edit]
Some more light reading can be found in The Anatomy of a Large-Scale Hypertextual Web Search Engine [www-db.stanford.edu].
