Justtechjobs.com Find a programming school near you






Online Campus Both


php3-list | 199901

Re: [PHP3] querying multiple fields of database From: Colin Viebrock (cmv <email protected>)
Date: 01/23/99

> How about this then:
> I can't believe selecting from _eighty fields - some fields filled in, most
> not -
> is as easy as a select statement, is it? (guess it also needs an "and /
> or" switch.)
>
> Is it still:
> >Nothing real tricky?
> >until you want to start ranking results by number of hits or something...
> Well yeah, wouldn't mind it...

Okay, I think you need to think about what kind of information you have, and how your users
are going to want to search it.

I think you said it was for an online music site. If you have 80 fields, like "artist",
"album", "song", "length", etc., how many of them are you going to realistically search on?
If you just want to let people type in:

        Alison

And out comes songs by Alison Moyet, and the song Alison by Elvis Costello, and any albums
with "alison" in the title, you can do it two ways. One one hand, you can do it with a mysql
query:

        SELECT DISTINCT * from database where (artist like '%alison%')
        OR (song like '%alison%') OR (album like '%alison%');

Then, you need to parse out the results of search and display them prettily.

(This is by no means a complex query. For instance, take a look at the travel planner at
http://www.regentholidays.com. I've been tweaking it a bit and the final version isn't
on-line yet, but the average query for the planner looks something like:

select *, if(HighRisk,1,0) as risk, (Rating+Rating+if(RatingPlus,1,0)) as rate,
(if(AreaID in (0,13,16),100,0) + if(AreaID in (0,12),50,0) + if(AreaID in
(0,1,2,3,4,5,6,7,8,9,10,11,14,15,17,18,19),1,0) ) as score1,
elt(Rating,50,100,50,1,1) as score2,
((if(locate('0',AddFeatures),500,0) + if(locate('1',AddFeatures),500,0) +
if(locate('beach',PlanProp),100,0) + if(locate('pool',Legend),200,0) +
if(locate('wsurf',PlanWater),200,0) + if(locate('dfish',PlanWater),200,0) +
if(locate('scuba',Legend),200,0) + if(locate('sless',PlanWater),200,0) +
if(locate('sauna',PlanNear),100,0) + if(locate('massg',PlanNear),100,0))*100/1800) as score3,
(((if(AreaID in (0,13,16),100,0) + if(AreaID in (0,12),50,0) + if(AreaID in
(0,1,2,3,4,5,6,7,8,9,10,11,14,15,17,18,19),1,0)
)*20/100.0)+(elt(Rating,50,100,50,1,1)*60/100.0)+(((if(locate('0',AddFeatures),500,0) +
if(locate('1',AddFeatures),500,0) + if(locate('beach',PlanProp),100,0) +
if(locate('pool',Legend),200,0) + if(locate('wsurf',PlanWater),200,0) +
if(locate('dfish',PlanWater),200,0) + if(locate('scuba',Legend),200,0) +
if(locate('sless',PlanWater),200,0) + if(locate('sauna',PlanNear),100,0) +
if(locate('massg',PlanNear),100,0))*100/1800)*20/100.0)) as totalscore
 from properties natural left join planner
 where Status=1 having totalscore>=50
 order by totalscore DESC, score2 DESC, risk DESC, rate DESC
 limit 0,4;

So don't worry about complexity!)

One the other hand, if you already have hundreds of pages (dynamic or static), you can use
ht://dig to do a full-site search. You'll probably turn up all the same pages you would with
the mysql approach, but you'll also be able to search through static pages (maybe Alison Smith
has a monthly column on your site), and even through PDF files and MS Word documents.

So it's kind of a 6-of-one answer.

- colin 

--
PHP 3 Mailing List   http://www.php.net/
To unsubscribe send an empty message to php3-unsubscribe <email protected>
To subscribe to the digest list:  php3-digest-subscribe <email protected>
For help: php3-help <email protected>  Archive:  http://www.php.net/mailsearch.php3
List administrator:  zeev-list-admin <email protected>