php - 允许用户使用 php mysql 在三种类型之间进行选择的搜索页面

Question

我有一个包含三种搜索类型的搜索页面，我想根据选择的类型过滤搜索

• 类型 1:按最新成员
• 类型 2:按特化
• 输入 3:按姓名

特化表:

• specialization_id
• 专业名称

成员表:

• 用户编号
• 名字
• 姓氏
• 特化
• 注册日期

但是第一种类型工作正常但第二种类型显示所有成员而不是所选专业的问题

特化的第一个查询是从下拉列表中选择特化

第二个是在 sepcialization 表和 members 表之间进行连接谁能帮帮我？？？？

搜索.php

//************for specialization droplist***************************//
function specializationQuery(){

$specData = mysql_query("SELECT * FROM specialization");

  while($recordJob = mysql_fetch_array($specData)){

     echo'<option value="' . $recordJob['specialization_id'] .  '">' . $recordJob['specialization_name'] . '</option>';

  }


}
$outputlist = "";
//**********search by new***************************************//
if(isset($_POST['searchbynew']))
{
    $listnew = $_POST['searchbynew'];
    $sql = mysql_query("SELECT * FROM members WHERE registered_date!='' ORDER BY registered_date DESC  ")or die((mysql_error("Error in quering new members List")));

    while($row = mysql_fetch_array($sql))
    {
        $row_id = $row['user_id'];
        $row_first_name =  $row['first_name'];
        $row_last_name =  $row['last_name'];
        $row_birthdate =  $row['birth_date'];
        $row_registered_date = $row['registered_date'];

         ////***********for the upload image*************************//
       $check_pic="members/$row_id/image01.jpg";
       $default_pic="members/0/image01.jpg";
       if(file_exists($check_pic))
       {
           $user_pic="<img src=\"$check_pic\"width=\"120px\"/>";
       }
       else
       {
           $user_pic="<img src=\"$default_pic\"width=\"120px\"/>";
       }

        $outputlist.='
   <table width="100%">
               <tr>
                  <td width="23%" rowspan="3"><div style="height:120px;overflow:hidden;"><a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank">'.$user_pic.'</a></div></td>
                  <td width="14%"><div  align="right">Name:</div></td>
                  <td width="63%"><a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank">'.$row_first_name.' '.$row_last_name.'</a></td>
                  </tr>

                  <tr>
                    <td><div align="right">Birth date:</div></td>
                    <td>'.$row_birthdate.'</td>
                  </tr>
                  <tr>
                   <td><div align="right">Registered:</div></td>
                   <td>'.$row_registered_date.'</td>
                  </tr>
                  </table>
                  <hr />
          ';


    }//close while
}

if(isset($_POST['searchbyspec']))
{
    $selectedSpec = $_POST['specialization'];
    $sql = mysql_query("SELECT user_id,first_name, last_name, birth_date, registered_date, specialization_name 
                        FROM members u INNER JOIN  specialization s 
                        ON u.specialization = s.specialization_id") or die(mysql_error("Error: quering thespecialization"));

    while($row = mysql_fetch_array($sql))
    {
        $row_id = $row['user_id'];
        $row_first_name =  $row['first_name'];
        $row_last_name =  $row['last_name'];
        $row_birthdate =  $row['birth_date'];
        $row_registered_date = $row['registered_date'];
        $row_spec = $row['specialization_name'];

        ////***********for the upload image*************************//
       $check_pic="members/$row_id/image01.jpg";
       $default_pic="members/0/image01.jpg";
       if(file_exists($check_pic))
       {
           $user_pic="<img src=\"$check_pic\"width=\"120px\"/>";
       }
       else
       {
           $user_pic="<img src=\"$default_pic\"width=\"120px\"/>";
       }

        $outputlist.='
   <table width="100%">
               <tr>
                  <td width="23%" rowspan="3"><div style="height:120px;overflow:hidden;"><a href =              "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank">'.$user_pic.'</a></div></td>
                  <td width="14%"><div  align="right">Name:</div></td>
                  <td width="63%"><a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank">'.$row_first_name.' '.$row_last_name.'</a></td>
                  </tr>

                  <tr>
                    <td><div align="right">Birth date:</div></td>
                    <td>'.$row_birthdate.'</td>
                  </tr>
                  <tr>
                   <td><div align="right">Registered:</div></td>
                   <td>'.$row_registered_date.'</td>
                  </tr>

                  <tr>
                   <td><div align="right">Registered:</div></td>
                   <td>'.$row_spec.'</td>
                  </tr>
                  </table>
                  <hr />
          ';


    }

}

Answer 1

您需要在 SQL 查询中使用 WHERE 子句。

SELECT ... JOIN ... WHERE u.specialization = '$selectedSpec'