php - Search the database using 4 different select boxes -
i have rather complex "where" statement create. have searched many sites find answers , yet page still shows nothing.
business requirement: use 4 different drop downs results database either selecting one, all, or combination of four.
here php code file searchinstructors.php:
<?php $dsn = 'mysql: host=localhost; dbname=name'; $user = 'user'; $password = 'pass'; try { $pdo = new pdo($dsn, $user, $password); $pdo ->setattribute(pdo::attr_errmode, pdo::errmode_exception); } catch (pdoexception $e) { echo 'connection failed: ' . $e->getmessage(); } if(isset($_get['semester'])){ $semester = $_get['semester']; }else{ $semester = "not selected"; } if(isset($_get['year'])){ $year = $_get['year']; }else{ $year = "not selected"; } if(isset($_get['courseprefix'])){ $courseprefix = $_get['courseprefix']; }else{ $courseprefix = "not selected"; } if(isset($_get['coursenumber'])){ $coursenumber = $_get['coursenumber']; }else{ $coursenumber = "not selected"; } echo "<table class='tg' width='100%'>"; echo "<tr> <th><strong>course</strong></th> <th><strong>name</strong></th> <th><strong>email/phone</strong></th> <th><strong>semester</strong></th> <th><strong>institution</strong></th> </tr>"; $sql="select courses.courseprefix courses.coursenumber, facultycontact.firstname, facultycontact.lastname, facultycontact.email, facultycontact.phone, facultycontact.institution, courses.semester, courses.year courses left join facultycontact on courses.id=facultycontact.id (courses.semester :semester , courses.year :year) or (courses.semester :semester , courses.courseprefix :courseprefix) or (courses.semester :semester , courses.coursenumber :coursenumber) or (courses.year :year , courses.courseprefix :courseprefix) or (courses.year :year , courses.coursenumber :coursenumber) or (courses.courseprefix :courseprefix , courses.coursenumber :coursenumber) or (courses.semester :semester , courses.year :year , courses.courseprefix :courseprefix) or (courses.semester :semester , courses.year :year , courses.coursenumber :coursenumber) or (courses.semester :semester , courses.year :year , courses.courseprefix :courseprefix , courses.coursenumber :coursenumber) order facultycontact.lastname"; $stmt = $pdo->prepare($sql); $stmt->bindparam(':semester', $semester, pdo::param_str); $stmt->bindparam(':year', $year, pdo::param_int); $stmt->bindparam(':courseprefix', $courseprefix, pdo::param_int); $stmt->bindparam(':coursenumber', $coursenumber, pdo::param_int); $stmt->execute(); $total = $stmt->rowcount(); while ($row = $stmt->fetchobject()) { echo "<tr> <td>{$row->courseprefix}<br /> {$row->coursenumber} </td> <td>{$row->firstname} {$row->lastname}</td> <td>{$row->email} <br />{$row->phone}</td> <td>{$row->semester} {$row->year} </td> <td>{$row->institution} </td>"; } if (empty($total)) { echo '<td colspan="5">no results found selection<br>' . '<strong>'. $semester . ' - ' . $year. ' - ' . $courseprefix. ' - ' . $coursenumber . '</strong></td>'; } else { echo implode($total); } $pdo = null; echo "</tr></table>"; ?> here html code in search_form.php:
<div class="search-wrapper"> <form id="form2" method="get" action="searchinstructors.php"> <p> <label for="semester">semester</label> <select name="semester" id="semester"> <option selected="selected" value="not selected">select one</option> <option value="spring">spring</option> <option value="summer">summer</option> <option value="fall">fall</option> </select> </p> <p> <label for="year">year</label> <select name="year" id="year"> <option selected="selected" value="not selected">select one</option> <option value="2015">2015</option> <option value="2014">2014</option> <option value="2013">2013</option> </select> </p> <p> <label for="courseprefix">course subject</label> <select name="courseprefix" id="courseprefix"> <option selected="selected" value="not selected">select one</option> <option value="aced">aced</option> <option value="crju">crju</option> <option value="engl">engl</option> <option value="fren">fren</option> <option value="hadm">hadm</option> <option value="leas">leas</option> <option value="orgl">orgl</option> <option value="pla">pla</option> <option value="pols">pols</option> <option value="psyc">psyc</option> <option value="soci">soci</option> <option value="span">span</option> </select> </p> <p> <label for="coursenumber">course number</label> <select name="coursenumber" id="coursenumber"> <option selected="selected" value="not selected">select one</option> <option value="1100">1100</option> <option value="1101">1101</option> <option value="1102">1102</option> <option value="2000">2000</option> <option value="2010">2010</option> <option value="2050">2050</option> <option value="2100">2100</option> <option value="2200">2200</option> <option value="2300">2300</option> <option value="2400">2400</option> <option value="2700">2700</option> <option value="3000">3000</option> <option value="3001">3001</option> <option value="3002">3002</option> <option value="3010">3010</option> <option value="3030">3030</option> <option value="3050">3050</option> <option value="3100">3100</option> <option value="3101">3101</option> <option value="3110">3110</option> <option value="3150">3150</option> <option value="3160">3160</option> <option value="3200">3200</option> <option value="3201">3201</option> <option value="3210">3210</option> <option value="3220">3220</option> <option value="3230">3230</option> <option value="3240">3240</option> <option value="3250">3250</option> <option value="3260">3260</option> <option value="3300">3300</option> <option value="3301">3301</option> <option value="3302">3302</option> <option value="3303">3303</option> <option value="3304">3304</option> <option value="3350">3350</option> <option value="3400">3400</option> <option value="3500">3500</option> <option value="3501">3501</option> <option value="3600">3600</option> <option value="3610">3610</option> <option value="3700">3700</option> <option value="3710">3710</option> <option value="3800">3800</option> <option value="3810">3810</option> <option value="4000">4000</option> <option value="4001">4001</option> <option value="4002">4002</option> <option value="4011">4011</option> <option value="4020">4020</option> <option value="4050">4050</option> <option value="4070">4070</option> <option value="4110">4110</option> <option value="4160">4160</option> <option value="4200">4200</option> <option value="4210">4210</option> <option value="4220">4220</option> <option value="4230">4230</option> <option value="4240">4240</option> <option value="4250">4250</option> <option value="4251">4251</option> <option value="4260">4260</option> <option value="4300">4300</option> <option value="4301">4301</option> <option value="4350">4350</option> <option value="4401">4401</option> <option value="4402">4402</option> <option value="4500">4500</option> <option value="4600">4600</option> <option value="4610">4610</option> <option value="4620">4620</option> <option value="4650">4650</option> <option value="4690">4690</option> <option value="4700">4700</option> <option value="4800">4800</option> <option value="4802">4802</option> <option value="4820">4820</option> <option value="4860">4860</option> <option value="4900">4900</option> <option value="4950">4950</option> <option value="4960">4960</option> <option value="4980">4980</option> <option value="4991">4991</option> </select> </p> <div class="clear"></div> <input type="submit" name="search" id="search" value="search" class="btn" /> </form> </div> this doesn't work. tried this:
if(isset($_get['semester'])){ $semester = $_get['semester']; $query="select courses.courseprefix, courses.coursenumber, facultycontact.firstname, facultycontact.lastname, facultycontact.email, facultycontact.phone, facultycontact.institution, courses.semester, courses.year courses left join facultycontact on courses.id=facultycontact.id courses.semester :semester or courses.year :year or courses.courseprefix :courseprefix or courses.coursenumber :coursenumber order facultycontact.lastname"; }else{ $semester = "not selected"; } if(isset($_get['year'])){ $year = $_get['year']; $query="select courses.courseprefix, courses.coursenumber, facultycontact.firstname, facultycontact.lastname, facultycontact.email, facultycontact.phone, facultycontact.institution, courses.semester, courses.year courses left join facultycontact on courses.id=facultycontact.id courses.semester :semester or courses.year :year or courses.courseprefix :courseprefix or courses.coursenumber :coursenumber order facultycontact.lastname"; }else{ $year = "not selected"; } if(isset($_get['courseprefix'])){ $courseprefix = $_get['courseprefix']; $query="select courses.courseprefix, courses.coursenumber, facultycontact.firstname, facultycontact.lastname, facultycontact.email, facultycontact.phone, facultycontact.institution, courses.semester, courses.year courses left join facultycontact on courses.id=facultycontact.id courses.semester :semester or courses.year :year or courses.courseprefix :courseprefix or courses.coursenumber :coursenumber order facultycontact.lastname"; }else{ $courseprefix = "not selected"; } if(isset($_get['coursenumber'])){ $coursenumber = $_get['coursenumber']; $query="select courses.courseprefix, courses.coursenumber, facultycontact.firstname, facultycontact.lastname, facultycontact.email, facultycontact.phone, facultycontact.institution, courses.semester, courses.year courses left join facultycontact on courses.id=facultycontact.id courses.semester :semester or courses.year :year or courses.courseprefix :courseprefix or courses.coursenumber :coursenumber order facultycontact.lastname"; }else{ $coursenumber = "not selected"; } it didn't work either. can make work when statement , and or. work 2 drop down , 1 or this:
courses.semester :semester , courses.year :year or courses.semester :semester , courses.courseprefix :courseprefix or courses.semester :semester , courses.coursenumber :coursenumber i had thought creating views , making each drop down pull view, there many combinations , when new courses added have redo views. possible create statement or query take input 4 drop downs in combination?
instead of manually creating sql every possible combination of semester, year, prefix, , number, let php build dynamically.
define variables you'll want use in sql.
$inputs = array( 'semester' => false, 'year' => false, 'courseprefix' => false, 'coursenumber' => false ); check presence of variables in $_get array.
foreach ( $inputs $k => $v ) { if ( isset($_get[$k]) ) { $inputs[$k] = $_get[$k]; } } dynamically build clause in sql. note: code starting point , doesn't need, such bindings. you'll have yourself.
$sql = 'select your_columns courses left join your_joins '; $where = array(); foreach ( $inputs $k => $v ) { if ( $v !== false ) { $where[$k] = 'courses.'.$k.' = :'.$k; } } if ( sizeof($where) > 0 ) { $sql .= 'where '.implode(' , ', $where); } $sql .= ' order some_columns'; now play few urls, e.g.
semester=summer&year=2009&courseprefix=pla semester=summer&year=2009 semester=summer and note how code doesn't know or care combination of parameters given.
once work out, adding option allow user specify and/or between parameters - or allowing them select multiples (e.g. checkboxes) super easy.
Comments
Post a Comment