Re: Multiple search options...
| From: | Richard Lynch | Date: | Fri, 03 Aug 2001 02:05:54 +0000 |
| Subject: | Re: Multiple search options... | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-61052@lists.php.net to get a copy of this message | ||
> Now I can do one or two of these seperate but can anyone suggest a logic
to
> take to allow someone to say: I'd like to search for Computer Programmers
> between 1 and 3 years experience in Kitchener and that have these
keywords.
>
> But the next person may come and say I want Computer Programmers with 2
> years experience in any city and any keywords...
MySQL:
create table programmer(
programmer_id int(11) auto_increment not null primary key,
category int(4),
experience int(2),
location varchar(255),
name text,
email text
);
create table category(
category_id int(11) auto_increment not null primary key,
category varchar(255)
);
insert into category(category) values('Computer Programmer');
insert into programmer(category, experience, location, name, email)
values(1, 5, 'Chicago', 'Richard Lynch', 'ceo@l-i-e.com');
create table skill(
skill_id int(11) auto_increment not null primary key,
skill varchar(255)
);
insert into skill(skill) values('PHP');
insert into skill(skill) values('MySQL');
insert into skill(skill) values('PostgreSQL');
create table programmer_skill(
programmer_id int(11),
skill_id int(11)
);
insert into programmer_skill(programmer_id, skill_id) values(1, 1);
insert into programmer_skill(programmer_id, skill_id) values(1, 2);
insert into programmer_skill(programmer_id, skill_id) values(1, 3);
PHP:
$query = "select id, name, email, (0 ";
if (isset($category)){
$query .= " + category = $category ";
}
if (isset($experience)){
$query .= " + experience between $experience[0] and $experience[1] ";
}
if (isset($location)){
$query .= " + location = '$location' ";
}
if (isset($skills)){
$query .= " + count(skills.id) ";
}
$query .= ") as score ";
$query .= " from programmer, programmer_skill ";
$query .= " where programmer.programmer_id = programmer_skill.programmer_id
";
$query .= " and score > 0 ";
$query .= " order by score desc ";
--
WARNING richard@zend.com address is an endangered species -- Use
ceo@l-i-e.com
Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm