i have a large publication database where i need to filter down the results with a series of options such as 'state', 'year', 'affiliation', etc. (9 total) - where might i find a good example of a script that would do something like this?
i'm adequate with simple queries, but i'm not sure if something like this is going to involve JOINs or something... perhaps this is just a bunch of conditions using AND...?
thanks for any help you can offer...
Regards,
GN
filtering results
Moderator: General Moderators
Re: filtering results
Yes AND will work. Make sure that if the condition is left blank that the query condition is set to *
Something like:
That syntax may not be correct but should give you the idea.
Good luck
Something like:
Code: Select all
if (isset($_POST['Year'])){
$Year=mysql_real_escape_string($_POST['Year']);
} else{
$Year="*";
}
Good luck
Last edited by jeaux on Fri Aug 29, 2008 10:24 am, edited 1 time in total.
Re: filtering results
You can indeed filter the results by using more AND x = y sections in the query.
You can get better help if you'd tell how the database table(s) look like and what you want to get out of if. And show what you have tried already.
You can get better help if you'd tell how the database table(s) look like and what you want to get out of if. And show what you have tried already.
Re: filtering results
yeah, that's what i was thinking, but the database is designed as such:
this is why i think i'm going to need to see some example code... i can get simple WHERE results, even using AND...
this is a project that was dropped by someone else and he was using perl/cgi, so i can't really read the code very well... i haven't tried anything at this point, because, as i said, i can handle simple queries.
perhaps if you looked at the form > http://www.virginiaadagency.com/hepinc/ ... istics.php > that would explain what i'm trying to do...
Thanks for your help,
GN
Code: Select all
--
-- Table structure for table 'rel_affiliation'
--
CREATE TABLE rel_affiliation (
affiliation int(3) NOT NULL,
affil_text varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (affiliation)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table 'rel_calendar'
--
CREATE TABLE rel_calendar (
calendar int(1) NOT NULL,
calendar_text varchar(10) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (calendar)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table 'rel_carnegie'
--
CREATE TABLE rel_carnegie (
cc2001 int(3) NOT NULL,
cc_description varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (cc2001)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table 'rel_emphasis'
--
CREATE TABLE rel_emphasis (
emphasis int(255) NOT NULL,
emphasis_text varchar(20) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (emphasis)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table 'rel_inst_type'
--
CREATE TABLE rel_inst_type (
inst_type int(2) NOT NULL,
inst_type_text varchar(30) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (inst_type)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
etc...
this is why i think i'm going to need to see some example code... i can get simple WHERE results, even using AND...
this is a project that was dropped by someone else and he was using perl/cgi, so i can't really read the code very well... i haven't tried anything at this point, because, as i said, i can handle simple queries.
perhaps if you looked at the form > http://www.virginiaadagency.com/hepinc/ ... istics.php > that would explain what i'm trying to do...
Thanks for your help,
GN
matthijs wrote:You can indeed filter the results by using more AND x = y sections in the query.
You can get better help if you'd tell how the database table(s) look like and what you want to get out of if. And show what you have tried already.
Re: filtering results [simplified now]
ok, i see more of what this query should do now.
what i'm trying to do is
Select all Administrators WHERE [in the table Characteristics] carnegie_code = $value AND (state = $value OR $value) AND affiliation = $value AND enrollment = $value ... etc...
i think that would get me moving in the right direction where i could do the rest.
thanks for anyone's help...
GN
what i'm trying to do is
Select all Administrators WHERE [in the table Characteristics] carnegie_code = $value AND (state = $value OR $value) AND affiliation = $value AND enrollment = $value ... etc...
i think that would get me moving in the right direction where i could do the rest.
thanks for anyone's help...
GN
Code: Select all
CREATE TABLE administrators (
fice int(11) NOT NULL,
line varchar(3) collate utf8_unicode_ci NOT NULL,
rank varchar(3) collate utf8_unicode_ci NOT NULL,
mpc varchar(10) collate utf8_unicode_ci NOT NULL,
title varchar(35) collate utf8_unicode_ci NOT NULL,
prefix varchar(6) collate utf8_unicode_ci NOT NULL,
`first` varchar(20) collate utf8_unicode_ci NOT NULL,
init varchar(1) collate utf8_unicode_ci NOT NULL,
`last` varchar(25) collate utf8_unicode_ci NOT NULL,
suffix varchar(10) collate utf8_unicode_ci NOT NULL,
vacant varchar(10) collate utf8_unicode_ci NOT NULL,
phone varchar(10) collate utf8_unicode_ci NOT NULL,
extension varchar(5) collate utf8_unicode_ci NOT NULL,
fax varchar(10) collate utf8_unicode_ci NOT NULL,
email varchar(60) collate utf8_unicode_ci NOT NULL,
exclude varchar(1) collate utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table 'administrators'
--
INSERT INTO administrators (fice, line, rank, mpc, title, prefix, first, init, last, suffix, vacant, phone, extension, fax, email, exclude) VALUES(1005, '317', '180', '36', 'Dir Placement Svcs/Cooperative Educ', 'Mrs', 'Mary', 'K', 'Williams', '', '', '3342294156', '', '', 'kwilliams@alasu.edu', '');
INSERT INTO administrators (fice, line, rank, mpc, title, prefix, first, init, last, suffix, vacant, phone, extension, fax, email, exclude) VALUES(1005, '318', '190', '26', 'Director University Relations', 'Ms', 'Janel', 'E', 'Bell', '', '', '3342294104', '', '', 'jbell@alasu.edu', '');
INSERT INTO administrators (fice, line, rank, mpc, title, prefix, first, init, last, suffix, vacant, phone, extension, fax, email, exclude) VALUES(1005, '319', '200', '49', 'Dean College Arts & Sciences', 'Dr', 'Thelma', '', 'Ivery', '', '', '3342294316', '', '', 'tivery@alasu.edu', '');
INSERT INTO administrators (fice, line, rank, mpc, title, prefix, first, init, last, suffix, vacant, phone, extension, fax, email, exclude) VALUES(1005, '320', '210', '50', 'Dean College Business Admin', 'Dr', 'Percy', 'J', 'Vaughn', 'Jr.', '', '3342294124', '', '', 'pvaughn@alasu.edu', '');
INSERT INTO administrators (fice, line, rank, mpc, title, prefix, first, init, last, suffix, vacant, phone, extension, fax, email, exclude) VALUES(1005, '321', '220', '89', 'Dean University College', 'Dr', 'Evelyn', '', 'Hodge', '', '', '3342294139', '', '', 'ehodge@alasu.edu', '');
CREATE TABLE characteristics (
fice int(11) NOT NULL,
old_fice varchar(11) collate utf8_unicode_ci NOT NULL,
ope_id varchar(8) collate utf8_unicode_ci NOT NULL,
unit_id varchar(10) collate utf8_unicode_ci NOT NULL,
duns_number varchar(9) collate utf8_unicode_ci NOT NULL,
abbrev_inst_name varchar(35) collate utf8_unicode_ci NOT NULL,
full_inst_name varchar(100) collate utf8_unicode_ci NOT NULL,
street varchar(35) collate utf8_unicode_ci NOT NULL,
abbrev_city varchar(20) collate utf8_unicode_ci NOT NULL,
full_city_name varchar(30) collate utf8_unicode_ci NOT NULL,
full_cnty_name varchar(30) collate utf8_unicode_ci NOT NULL,
full_state varchar(20) collate utf8_unicode_ci NOT NULL,
abbrev_state varchar(2) collate utf8_unicode_ci NOT NULL,
zip_code varchar(10) collate utf8_unicode_ci NOT NULL,
main_phone varchar(10) collate utf8_unicode_ci NOT NULL,
main_fax varchar(10) collate utf8_unicode_ci NOT NULL,
toll_free varchar(10) collate utf8_unicode_ci NOT NULL,
url varchar(60) collate utf8_unicode_ci NOT NULL,
carnegie_code varchar(3) collate utf8_unicode_ci NOT NULL,
congress_dist varchar(2) collate utf8_unicode_ci NOT NULL,
affiliation varchar(3) collate utf8_unicode_ci NOT NULL,
calendar varchar(1) collate utf8_unicode_ci NOT NULL,
offering varchar(2) collate utf8_unicode_ci NOT NULL,
prg_occup varchar(1) collate utf8_unicode_ci NOT NULL,
prg_2_year varchar(1) collate utf8_unicode_ci NOT NULL,
prg_lib_arts varchar(1) collate utf8_unicode_ci NOT NULL,
prg_teach_prep varchar(1) collate utf8_unicode_ci NOT NULL,
prg_prof varchar(1) collate utf8_unicode_ci NOT NULL,
emphasis varchar(1) collate utf8_unicode_ci NOT NULL,
est_date int(4) NOT NULL,
enrollment int(10) NOT NULL,
enr_ft_ug varchar(10) collate utf8_unicode_ci NOT NULL,
enr_ft_gr varchar(10) collate utf8_unicode_ci NOT NULL,
enr_pt_ug varchar(10) collate utf8_unicode_ci NOT NULL,
enr_pt_gr varchar(10) collate utf8_unicode_ci NOT NULL,
enr_m varchar(10) collate utf8_unicode_ci NOT NULL,
enr_f varchar(10) collate utf8_unicode_ci NOT NULL,
tuition int(10) NOT NULL,
tuit_in_st varchar(10) collate utf8_unicode_ci NOT NULL,
tuit_out_st varchar(10) collate utf8_unicode_ci NOT NULL,
inst_type varchar(1) collate utf8_unicode_ci NOT NULL,
sex varchar(1) collate utf8_unicode_ci NOT NULL,
irs_status varchar(1) collate utf8_unicode_ci NOT NULL,
print_sequence varchar(8) collate utf8_unicode_ci NOT NULL,
batch varchar(3) collate utf8_unicode_ci NOT NULL,
`comment` varchar(255) collate utf8_unicode_ci NOT NULL,
footnote text collate utf8_unicode_ci NOT NULL,
date_recvd varchar(100) collate utf8_unicode_ci NOT NULL,
full_inst_name_2 varchar(50) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (fice)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table 'characteristics'
--
INSERT INTO characteristics (fice, old_fice, ope_id, unit_id, duns_number, abbrev_inst_name, full_inst_name, street, abbrev_city, full_city_name, full_cnty_name, full_state, abbrev_state, zip_code, main_phone, main_fax, toll_free, url, carnegie_code, congress_dist, affiliation, calendar, offering, prg_occup, prg_2_year, prg_lib_arts, prg_teach_prep, prg_prof, emphasis, est_date, enrollment, enr_ft_ug, enr_ft_gr, enr_pt_ug, enr_pt_gr, enr_m, enr_f, tuition, tuit_in_st, tuit_out_st, inst_type, sex, irs_status, print_sequence, batch, comment, footnote, date_recvd, full_inst_name_2) VALUES(1002, '', '00100200', '100654', '', 'Alabama A & M University', 'Alabama Agricultural and Mechanical University', 'PO Box 1357, 4900 Meridian Street', 'Normal', 'Normal', 'Madison', 'Alabama', 'AL', '35762-1357', '2563725000', '2563725244', '', 'www.aamu.edu', '18', '05', '12', '1', '08', '0', '0', '1', '1', '0', '0', 1875, 6076, '', '', '', '', '', '', 4290, '', '', '5', '3', '1', '00000100', 'E', '', '', '8/24/2007 0:00:00', '');
INSERT INTO characteristics (fice, old_fice, ope_id, unit_id, duns_number, abbrev_inst_name, full_inst_name, street, abbrev_city, full_city_name, full_cnty_name, full_state, abbrev_state, zip_code, main_phone, main_fax, toll_free, url, carnegie_code, congress_dist, affiliation, calendar, offering, prg_occup, prg_2_year, prg_lib_arts, prg_teach_prep, prg_prof, emphasis, est_date, enrollment, enr_ft_ug, enr_ft_gr, enr_pt_ug, enr_pt_gr, enr_m, enr_f, tuition, tuit_in_st, tuit_out_st, inst_type, sex, irs_status, print_sequence, batch, comment, footnote, date_recvd, full_inst_name_2) VALUES(1034, '', '00103400', '101949', '', 'Alabama Southern Community College', 'Alabama Southern Community College', 'PO Box 2000', 'Monroeville', 'Monroeville', 'Monroe', 'Alabama', 'AL', '36460-2000', '2515753156', '2515755356', '', 'www.ascc.edu', '1', '01', '12', '1', '03', '1', '1', '0', '0', '0', '0', 1965, 1420, '', '', '', '', '', '', 4700, '', '', '5', '3', '1', '00000225', 'E', '', '', '9/6/2007 0:00:00', '');
INSERT INTO characteristics (fice, old_fice, ope_id, unit_id, duns_number, abbrev_inst_name, full_inst_name, street, abbrev_city, full_city_name, full_cnty_name, full_state, abbrev_state, zip_code, main_phone, main_fax, toll_free, url, carnegie_code, congress_dist, affiliation, calendar, offering, prg_occup, prg_2_year, prg_lib_arts, prg_teach_prep, prg_prof, emphasis, est_date, enrollment, enr_ft_ug, enr_ft_gr, enr_pt_ug, enr_pt_gr, enr_m, enr_f, tuition, tuit_in_st, tuit_out_st, inst_type, sex, irs_status, print_sequence, batch, comment, footnote, date_recvd, full_inst_name_2) VALUES(1005, '', '00100500', '100724', '', 'Alabama State University', 'Alabama State University', '915 S Jackson Street', 'Montgomery', 'Montgomery', 'Montgomery', 'Alabama', 'AL', '36101-0271', '3342294200', '3348346861', '', 'www.alasu.edu', '18', '07', '12', '1', '08', '0', '0', '1', '1', '0', '0', 1867, 5565, '', '', '', '', '', '', 4508, '', '', '5', '3', '1', '00000300', 'E', '', '', '6/29/2007 0:00:00', '');
INSERT INTO characteristics (fice, old_fice, ope_id, unit_id, duns_number, abbrev_inst_name, full_inst_name, street, abbrev_city, full_city_name, full_cnty_name, full_state, abbrev_state, zip_code, main_phone, main_fax, toll_free, url, carnegie_code, congress_dist, affiliation, calendar, offering, prg_occup, prg_2_year, prg_lib_arts, prg_teach_prep, prg_prof, emphasis, est_date, enrollment, enr_ft_ug, enr_ft_gr, enr_pt_ug, enr_pt_gr, enr_m, enr_f, tuition, tuit_in_st, tuit_out_st, inst_type, sex, irs_status, print_sequence, batch, comment, footnote, date_recvd, full_inst_name_2) VALUES(666067, '', '', '441034', '', 'American Sentinel University', 'American Sentinel University', '2101 Magnolia Avenue, Suite 20', 'Birmingham', 'Birmingham', 'Jefferson', 'Alabama', 'AL', '35205-2827', '2053236191', '2053282229', '', 'www.americansentinel.edu', '99', '', '25', '1', '06', '0', '0', '0', '0', '0', '7', 1988, 1020, '', '', '', '', '', '', 5400, '', '', '5', '3', '0', '00000350', '268', '', '', '9/6/2007 0:00:00', '');
INSERT INTO characteristics (fice, old_fice, ope_id, unit_id, duns_number, abbrev_inst_name, full_inst_name, street, abbrev_city, full_city_name, full_cnty_name, full_state, abbrev_state, zip_code, main_phone, main_fax, toll_free, url, carnegie_code, congress_dist, affiliation, calendar, offering, prg_occup, prg_2_year, prg_lib_arts, prg_teach_prep, prg_prof, emphasis, est_date, enrollment, enr_ft_ug, enr_ft_gr, enr_pt_ug, enr_pt_gr, enr_m, enr_f, tuition, tuit_in_st, tuit_out_st, inst_type, sex, irs_status, print_sequence, batch, comment, footnote, date_recvd, full_inst_name_2) VALUES(666764, '', '', '420361', '', 'Andrew Jackson University', 'Andrew Jackson University', '2919 John Hawkins Parkway', 'Birmingham', 'Birmingham', 'Jefferson', 'Alabama', 'AL', '35244-1095', '2058719288', '2058719294', '', 'www.aju.edu', '99', '', '25', '5', '06', '0', '0', '1', '0', '0', '0', 1994, 400, '', '', '', '', '', '', 5750, '', '', '5', '3', '0', '00000400', 'E', '', '', '8/10/2007 0:00:00', '');