Hello,
I have a search query to build on two table below is the table structure, and I
need to build a query to search on specific cities(or all cities),specific
subjects(or all subjects) and specific levels(or all levels)
# Table structure for tt_jobs and tt_jobbids
# tt_jobs
pid int(6) unsigned NOT NULL auto_increment,
ttid int(6) unsigned NOT NULL default '0',
jobid varchar(10) NOT NULL default '',
title varchar(60) NOT NULL default '',
subjects smallint(4) NOT NULL default '0',
levels smallint(4) NOT NULL default '0',
cities smallint(4) NOT NULL default '0',
location varchar(250) NOT NULL default '',
day_required varchar(4) NOT NULL default '',
tuition_place tinyint(2) NOT NULL default '0',
duration smallint(4) NOT NULL default '0',
students varchar(6) NOT NULL default '',
tuition_description text,
status enum('open','closed','tempclosed') NOT NULL default 'open',
PRIMARY KEY (pid),
KEY ttid (ttid),
KEY searchidx (cities,levels,subjects)
# tt_jobbids
pid int(6) unsigned NOT NULL default '0',
ttid int(6) unsigned NOT NULL default '0',
bidamt decimal(4,2) NOT NULL default '0.00',
bidtime varchar(10) NOT NULL default '',
bidstatus enum('pending','tutoraccept','tuteeaccept','reject') NOT NULL
default 'pending',
bidexpires varchar(20) NOT NULL default '',
KEY pid (pid),
KEY ttid (ttid),
KEY bidexpires (bidexpires)
Now the search can be on all cities,
levels and subjects which is no problem, it just extracts all open jobs.
# Query to display all open jobs and works fine
SELECT js.pid,js.title,js.subjects,js.levels,js.cities,COUNT(jb.ttid) AS
totalbid FROM tt_jobs js LEFT JOIN tt_jobbids jb ON js.pid = jb.pid
WHERE js.status = 'open' GROUP BY js.pid ORDER BY js.pid DESC
Now I need to be able to perform a search on these two tables but with
specific queries on cities, and subjects and levels. The cities,subjects and
levels columns data are all numerical values(IE 1 - 100).
Now when the search is submitted it can be performed in various ways
# (X = a numerical value)
"all cities" and "subjects = X" and "levels = X"
"all subjects" and "all cites" and "subject = X"
"all cities" and "level = X" and "subject = X"
"all levels" and "all subjects" and "cities = X"
"subject = X" and "cities = X" and "all levels"
"subject = X" and "cities = X" and "levels = X"
....And so on, in various combinations. And I haven't come up with
a workable solution to do this. And was hoping someone could suggest
a feasible query to do this or do I need to restructure the tables that
are being searched on??
Hope this is clear :)
TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
