首页 \ 问答 \ 使用4个不同的选择框搜索数据库(Search the database using 4 different select boxes)

使用4个不同的选择框搜索数据库(Search the database using 4 different select boxes)

我有一个相当复杂的“WHERE”语句来创建。 我搜索了许多网站以找到答案,但我的页面仍然没有显示任何内容。

业务需求:使用4个不同的下拉列表从数据库中获取结果,方法是选择一个,全部或全部四个的任意组合。

这是文件searchInstructors.php的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
        FROM courses
        LEFT JOIN facultyContact 
        ON courses.ID=facultyContact.ID
        WHERE (courses.semester LIKE :semester AND courses.year LIKE :year)
OR (courses.semester LIKE :semester AND courses.coursePrefix LIKE :coursePrefix)
OR (courses.semester LIKE :semester AND courses.courseNumber LIKE :courseNumber) 
OR (courses.year LIKE :year AND courses.coursePrefix LIKE :coursePrefix)
OR (courses.year LIKE :year AND courses.courseNumber LIKE :courseNumber)
OR (courses.coursePrefix LIKE :coursePrefix AND courses.courseNumber LIKE :courseNumber)
OR (courses.semester LIKE :semester AND courses.year LIKE :year AND courses.coursePrefix LIKE :coursePrefix)
OR (courses.semester LIKE :semester AND courses.year LIKE :year AND courses.courseNumber LIKE :courseNumber)
OR  (courses.semester LIKE :semester AND courses.year LIKE :year AND courses.coursePrefix LIKE :coursePrefix AND courses.courseNumber LIKE :courseNumber)
        ORDER BY 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 for your selection<br>' .
        '<strong>'. $semester . ' - ' . $year. ' - ' .  $coursePrefix. ' - ' .     $courseNumber . '</strong></td>'; 
        } else {
            echo implode($total);
        }

        $pdo = null;
        echo "</tr></table>";
           ?>

这是search_form.php中的HTML代码:

                <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>

这不起作用。 所以我尝试了这个:

 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR  courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY facultyContact.lastName";
 }else{  
   $courseNumber = "Not Selected";
 }

它也没用。 当WHERE语句是全部AND和所有OR时,我可以使它工作。 它将使用两个下拉和一个或像这样:

courses.semester LIKE :semester AND courses.year LIKE :year OR 
courses.semester LIKE :semester AND courses.coursePrefix LIKE :coursePrefix OR
courses.semester LIKE :semester AND courses.courseNumber LIKE :courseNumber

我曾考虑创建视图,只是让每个下拉拉视图,但有太多组合,当添加新课程时,我将不得不重做视图。 是否可以创建一个WHERE语句或查询,从任何组合中获取4个下拉输入?


I have a rather complex "WHERE" statement to create. I have searched many sites to find answers and yet my page still shows nothing.

Business requirement: Use 4 different drop downs to get results from the database either by selecting one, all, or any combination of all four.

Here is the php code for the 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
        FROM courses
        LEFT JOIN facultyContact 
        ON courses.ID=facultyContact.ID
        WHERE (courses.semester LIKE :semester AND courses.year LIKE :year)
OR (courses.semester LIKE :semester AND courses.coursePrefix LIKE :coursePrefix)
OR (courses.semester LIKE :semester AND courses.courseNumber LIKE :courseNumber) 
OR (courses.year LIKE :year AND courses.coursePrefix LIKE :coursePrefix)
OR (courses.year LIKE :year AND courses.courseNumber LIKE :courseNumber)
OR (courses.coursePrefix LIKE :coursePrefix AND courses.courseNumber LIKE :courseNumber)
OR (courses.semester LIKE :semester AND courses.year LIKE :year AND courses.coursePrefix LIKE :coursePrefix)
OR (courses.semester LIKE :semester AND courses.year LIKE :year AND courses.courseNumber LIKE :courseNumber)
OR  (courses.semester LIKE :semester AND courses.year LIKE :year AND courses.coursePrefix LIKE :coursePrefix AND courses.courseNumber LIKE :courseNumber)
        ORDER BY 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 for your selection<br>' .
        '<strong>'. $semester . ' - ' . $year. ' - ' .  $coursePrefix. ' - ' .     $courseNumber . '</strong></td>'; 
        } else {
            echo implode($total);
        }

        $pdo = null;
        echo "</tr></table>";
           ?>

Here is the 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. So I 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR  courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY 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
FROM courses
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID
WHERE courses.semester LIKE :semester OR courses.year LIKE :year OR courses.coursePrefix LIKE :coursePrefix OR courses.courseNumber LIKE :courseNumber
ORDER BY facultyContact.lastName";
 }else{  
   $courseNumber = "Not Selected";
 }

It didn't work either. I can make it work when the WHERE statement is all AND and all OR. It will work with two drop down and one or like this:

courses.semester LIKE :semester AND courses.year LIKE :year OR 
courses.semester LIKE :semester AND courses.coursePrefix LIKE :coursePrefix OR
courses.semester LIKE :semester AND courses.courseNumber LIKE :courseNumber

I had thought about creating views and just making each drop down pull the view, but there are too many combinations and when new courses are added I will have to redo the views. Is it possible to create a WHERE statement or query that will take input from 4 drop downs in any combination?


原文:https://stackoverflow.com/questions/31885749
更新时间:2020-10-17 15:10

最满意答案

不要为每个可能的学期,年份,前缀和数字组合手动创建SQL,而是让PHP动态构建它。

定义要在SQL中使用的所有变量。

$inputs = array(
    'semester'      => false,
    'year'          => false,
    'coursePrefix'  => false,
    'courseNumber'  => false
);

检查$ _GET数组中是否存在变量。

foreach ( $inputs as $k => $v ) {
    if ( isset($_GET[$k]) ) {
        $inputs[$k] = $_GET[$k];
    }
}

在SQL中动态构建where子句。 注意:我的代码是一个起点,并不能完成您需要的所有操作,例如绑定。 你必须自己做。

$sql = 'SELECT your_columns FROM courses LEFT JOIN your_joins ';
$where = array();
foreach ( $inputs as $k => $v ) {
    if ( $v !== false ) {
        $where[$k] = 'courses.'.$k.' = :'.$k;   
    }
}
if ( sizeof($where) > 0 ) {
    $sql .= 'WHERE '.implode(' AND ', $where);  
}
$sql .= ' ORDER BY some_columns';

现在玩一些URL,例如

semester=Summer&year=2009&coursePrefix=PLA
semester=Summer&year=2009
semester=Summer

并注意代码如何不知道或不确切地关注哪个参数组合。

一旦你解决了这个问题,添加选项以允许用户指定和/或在参数之间 - 或允许他们选择多个(例如复选框)是非常容易的。


So after many more hours spent researching I have code that works. So this is for everyone else that has to do something complex.

<?php
$dsn = 'mysql: local host; dbname=dbName';
$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();
}
try{
    $query = "SELECT courses.coursePrefix, courses.courseNumber, facultyContact.firstName, facultyContact.lastName, facultyContact.email, facultyContact.phone, facultyContact.institution, courses.semester, courses.year
FROM courses 
LEFT JOIN facultyContact 
ON courses.ID=facultyContact.ID ";
$cond = array();
$params = array();

 if( isset($_GET['semester']) && !(empty($_GET['semester'])) ){  
    $cond[] = " courses.semester LIKE '". $_GET['semester']."'";
    $params[] = $semester;

}
if(isset($_GET['year']) && !(empty($_GET['year'])) ){  
    $cond[] = " courses.year LIKE '". $_GET['year']."'";
    $params[] = $year;
}
if(isset($_GET['coursePrefix']) && !(empty($_GET['coursePrefix'])) ){
    $cond[] = " courses.coursePrefix LIKE '". $_GET['coursePrefix']."'";
    $params[] = $coursePrefix;
}
if(isset($_GET['courseNumber']) && !(empty($_GET['courseNumber'])) ){
    $cond[] = " courses.courseNumber LIKE '". $_GET['courseNumber'] ."'";
    $params[] = $courseNumber;
}
if (count($cond)) {
    $query .= ' WHERE ' . implode(' AND ', $cond);
}
$query.= "ORDER BY facultyContact.lastName, facultyContact.firstName";

$stmt = $pdo->prepare($query);
$stmt->execute($params);

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>";

$total = $stmt->rowCount();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) {

   echo "<tr>    
        <td>" . $row['coursePrefix'] . " ". $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></tr>";
}
//user error message for no results
if (empty($total)) { 
    echo '<td colspan="5">No results found for your selection<br>' .
'<strong>'. $_GET['semester'] . ' - ' . $_GET['year']. ' - ' .  $_GET['coursePrefix'] . ' - ' . $_GET['courseNumber'] . '</strong></td>'; 
} else {
    echo implode($total);
}
} catch (PDOException $e) {
    $error_messageALL = $e->getMessage();
    echo "DB error: " . $error_messageALL;  

    }

    $pdo = null;
    echo "</tr></table>";
       ?>
2015-08-24

相关问答

更多

添加到多个文本框的数据库(Adding to database with multiple text boxes)

我能够通过一些反复试验解决问题,劳伦斯帮助通知我使用foreach声明。 这就是我最终的结果。 <?php include("config.php"); include("header.php"); include("functions.php"); if(!isset($_SESSION['username']) && !isset($_SESSION['password'])){ header("Location: pubs.php"); } $getmember = my ...

搜索字符串通配符并显示到文本框和组合框数据(search string wildcard and display to textbox and combo boxes data)

您不能将SqlConnection用作OldDbDataAdapter创建的第二个参数。 Dim sqlconn As New SqlClient.SqlConnection .... Dim da As New OleDbDataAdapter .... da = New OleDbDataAdapter("Select .....%'", sqlconn) 你不应该将OleDb与SQL .Net对象混合使用......要么使用a SqlDataAdapter与SqlConnection或 O ...

链接选择框与jquery / php /数据库无法正常工作(Chained select boxes with jquery/php/database not working)

好的,所以我想出了这个。 希望这可以帮助将来遇到同样问题的人。 对文件的调用select_type.php不正确。 呼叫必须是绝对的而不是相对的。 由于我正在使用wordpress,我只是将行改为: $.get("<?php echo get_template_directory_uri() . '/includes/select-model.php'; ?>", {id:id}, function(data){ 您会注意到我将$ .post更改为$ .get。 我不确定为什么帖子不起作用,但似 ...

sql数据库设计 - 选中和复选框[关闭](sql database design - select & check boxes [closed])

如果您希望表单元素(复选框,列表)可能经常更改,或者是有条件的(基于可配置的权限/角色),那么它们应该来自数据库。 但是,如果它们大部分是静态的(很少更改,不依赖于可配置的权限),那么您应该对它们进行硬编码。 对其进行硬编码的最大好处是可以降低数据库的流量。 这将产生最佳性能。 If you expect the form elements (checkboxes, lists) are likely to change often, or are conditional (based on co ...

如何通过数据库搜索组合框(how to search in combobox by database)

您可以在这里使用选择的jquery插件harvesthq.github.io/chosen/ 所以你可以在下拉列表中搜索关键字试试这个插件。 这可以帮助您使用如何在我的html文件中使用选择的jQuery? 添加所需的js和css文件到页面分配类到下拉列表如下: - <head> <link rel="stylesheet" href="http://code.jquery.com/ui/1.9.2/themes/base/jquery-ui.css"> <script s ...

如何从数据库中填充谷歌地图信息框?(How to populate google maps info boxes from database?)

换行: marker.content = "some words"; 有: marker.content = "@row.Description"; Change the line: marker.content = "some words"; with: marker.content = "@row.Description";

使用不同数量的文本框更新mysql数据库(Updating the mysql database with varying number of text boxes)

正如Prowla所提到的,你应该使用PDO而不是mysql_函数。 无论如何,我改进了你的代码,它假设现在工作: <form action="<?php $self ?>" form method="post"> <?php $query = "SELECT * FROM `table` ORDER BY table.ID DESC"; $result = @mysql_query($query) or die('Query error:'.mysql_error()); $num=m ...

从另一个选择框填充选择框并使用数据库连接(populate a select box from a another select box and using database connection)

尝试这个: 在您的模板中 $('#country').change(function() { var value = $(this).attr('value'); var request = $.ajax({ url: "/getcities/", type: "GET", data: {country : value}, dataType: "json", success: function( ...

根据选择框值选择数据库表(php + Ajax)(Select database table depending on select box value (php + Ajax))

$("#Testid").change(function () { var val = $("#Testid option:selected").text(); // here your ajax request // Fill second select here: var deletemisc = $("#deletemisc"); $("#deletemisc > option").remove(); $.post("/path/to/scri ...

使用4个不同的选择框搜索数据库(Search the database using 4 different select boxes)

不要为每个可能的学期,年份,前缀和数字组合手动创建SQL,而是让PHP动态构建它。 定义要在SQL中使用的所有变量。 $inputs = array( 'semester' => false, 'year' => false, 'coursePrefix' => false, 'courseNumber' => false ); 检查$ _GET数组中是否存在变量。 foreach ( $inputs as $k => $v ) { ...

相关文章

更多

最新问答

更多
  • 一个Web角色上的两个Web应用程序 - 配置问题(Two web applications on one Web Role - config issue)
  • Chrome扩展程序:在后台页面中存储变量是否安全?(Chrome extension : storing variables in background page is secure?)
  • C ++ 11中的atomics是否阻止编译器重新读取共享变量?(Do atomics in C++11 prevent compiler to re-read from shared variables?)
  • Java代码重组(Java code restructuring)
  • 从video.js获取currentTime(Get currentTime from video.js)
  • 3列 - 带溢流元件的中心柱流体(3 columns - center column fluid with an overflow element)
  • JSX节点中的TypeScript类型参数(TypeScript type arguments in JSX nodes)
  • .NET自定义事件组织帮助(.NET custom event organization assistance)
  • 如何将内存地址(如0xc20803a000)转换为字符串?(How to convert memory address (like 0xc20803a000) to string? Golang)
  • 从python中的列表创建新列表的最佳方法(Best way to create a new list from a list in python)
  • 为什么RabbitMQ会从破坏的持久性日志文件中脱颖而出?(Why does RabbitMQ keep breaking from a corrupt persister log file?)
  • 将对象添加到模型中,不更新dom(Adding object to model, doesn't update dom)
  • 将array.count转换为String(Convert array.count to String)
  • Chrome中的HTML (HTML in Chrome)
  • Sklearn:分类Imputer?(Sklearn: Categorical Imputer?)
  • 为什么iOS 9方法仍适用于iOS 8.4?(Why Do iOS 9 Methods Still Work on iOS 8.4?)
  • 输入类型文件无法获取angularjs中文件类型的文件名(input type file is unable to get the file name with file type in angularjs)
  • 为什么SplFixedArray的json_encode转储对象而不是数组?(Why does json_encode of a SplFixedArray dump an object instead of an array?)
  • ORA-01858:找到一个非数字字符,其中xp.executenonquery()上的数字是预期的(ORA-01858: a non-numeric character was found where a numeric was expected on xp.executenonquery())
  • 通过使用sscanf提取数据在BST插入函数中传递参数(Passing arguments in a BST insert function by extracting data using sscanf)
  • 混合c和c ++的问题(problem mixing c and c++)
  • 检查几个选项的文本(Checking text against several options)
  • 在Flex中创建Captch组件(Create Captch Component in Flex)
  • 查询非常复杂,不确定是否可能。(Very complicated query, not sure if it possible. Involves not adding to a running sum)
  • AOSP - networkAttributes中CSV的含义(AOSP - Meaning of CSV in networkAttributes)
  • 将不同表中的数据拖入单个视图中(Pulling data from different tables into a single view)
  • 从Enum Java调用随机值(更新)(Calling Random Value from an Enum Java)
  • jwplayer与vimeo插件(jwplayer with vimeo plugin)
  • CSS依赖于ActiveX?(CSS dependent on ActiveX?)
  • TPL Dataflow没有完成多个目标(TPL Dataflow not completing with multiple targets)