php - 使用 php mysql 和 inner join 重复结果

Question

我有三个下拉列表，其中包含从 3 个不同表的数据库中检索到的值

治理:

• governosrate_id
• 省级名称

地区:

• 地区编号
• 学区名称

村庄:

• 编号
• 村名

成员:

• 用户编号
• 用户名
• 省
• 地区
• 村庄我想要的是当用户选择三个或全部中的一个下拉列表时，系统必须显示与所选一个相关的结果

但问题是，当用户从省中选择时，它工作正常，但当它选择省和地区时，它会复制与所选值相关的结果，如果用户选择省区和村庄，结果将变成三元组

谁能帮帮我？？？

我将显示三种类型的所有代码

如果有人要投诉，请不要打我

代码:

各省

//**********search by locationn***************************************//
if(isset($_POST['listbyq']))
{    
//********************by governorate**************************************//
   if($_POST['listbyq']=="by_gov")
   {
       $bygov = $_POST['governorate'];
       $sql = mysql_query("SELECT user_id,first_name, last_name, birth_date, registered_date, 
    s.specialization_name,
    g.governorate_name,
    d.district_name,
    v.village_name 
          FROM members u
                       INNER JOIN  specialization s 
                        ON u.specialization = s.specialization_id
                        INNER JOIN governorate g
                        ON u.governorate = g.governorate_id
                        INNER JOIN districts d
                        ON u.district = d.district_id
                        INNER JOIN village v
                        ON u.village = v.id
                        WHERE governorate = '$bygov'")or die(mysql_error("Error: querying the governorate"));

       $num_row = mysql_num_rows($sql);
       if($num_row > 0 )
       {
           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'];
              $row_gov = $row['governorate_name'];
              $row_dist = $row['district_name'];
              $row_village = $row['village_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="5"><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">Job:</div></td>
                     <td>'.$row_spec.'</td>
                    </tr>

                    <tr>
                     <td><div align="right">Location:</div></td>
                     <td>'.$row_gov.'__'.$row_dist.'__'.$row_village.'</td>
                    </tr>
                    </table>
                    <hr />
            ';

           }
       }

   }
   else
   {
       $errorMSG = "No member within this selected governorate";
   }

按地区

 if($_POST['listbyqa']=="by_dist")
   {
      @ $bydist = $_POST['district'];
       $sql = mysql_query("SELECT user_id,first_name, last_name, birth_date, registered_date, 
    s.specialization_name,
    g.governorate_name,
    d.district_name,
    v.village_name 
          FROM members u
                       INNER JOIN  specialization s 
                        ON u.specialization = s.specialization_id
                        INNER JOIN governorate g
                        ON u.governorate = g.governorate_id
                        INNER JOIN districts d
                        ON u.district = d.district_id
                        INNER JOIN village v
                        ON u.village = v.id
                       WHERE district = '$bydist'")or die(mysql_error("Error: querying the district"));

       $num_row = mysql_num_rows($sql);
       if($num_row > 0 )
       {
           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'];
              $row_gov = $row['governorate_name'];
              $row_dist = $row['district_name'];
              $row_village = $row['village_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="5"><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">Job:</div></td>
                     <td>'.$row_spec.'</td>
                    </tr>

                    <tr>
                     <td><div align="right">Location:</div></td>
                     <td>'.$row_gov.'__'.$row_dist.'__'.$row_village.'</td>
                    </tr>
                    </table>
                    <hr />
            ';

           }
       }

   }
   else
   {
       $errorMSG = "No member within this selected District";
   } 

按村庄

if($_POST['listbyqb']=="by_city")
   {
       echo $_POST['listbyqb'];
      @ $byvillage = $_POST['village'];
       $sql = mysql_query("SELECT user_id,first_name, last_name, birth_date, registered_date, 
    s.specialization_name,
    g.governorate_name,
    d.district_name,
    v.village_name 
          FROM members u
                       INNER JOIN  specialization s 
                        ON u.specialization = s.specialization_id
                        INNER JOIN governorate g
                        ON u.governorate = g.governorate_id
                        INNER JOIN districts d
                        ON u.district = d.district_id
                        INNER JOIN village v
                        ON u.village = v.id
                       WHERE village = '$byvillage'")or die(mysql_error("Error: querying the district"));

       $num_row = mysql_num_rows($sql);
       if($num_row > 0 )
       {
           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'];
              $row_gov = $row['governorate_name'];
              $row_dist = $row['district_name'];
              $row_village = $row['village_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="5"><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">Job:</div></td>
                     <td>'.$row_spec.'</td>
                    </tr>

                    <tr>
                     <td><div align="right">Location:</div></td>
                     <td>'.$row_gov.'__'.$row_dist.'__'.$row_village.'</td>
                    </tr>
                    </table>
                    <hr />
            ';

           }
       }

   }
   else
   {
       $errorMSG = "No member within this selected District";
   }
}

Answer 1

一些简单的事情值得尝试:

1. echo 或 var_dump $_POST 结果以确保您获得了您认为获得的数据。这可能是问题的原因。你可能得到不止一个

2. 很可能是乱码导致了这里的问题。你有三个输出部分，所以你有三个可能的输出，所有这些都可以在满足适当的 IF 条件时触发。尝试在每个 block 中回显“这里”、“那里”和“到处”或您选择的词 - 您很快就会看到可疑代码的位置!

3. 更好的选择可能是将您的 where 子句更改为:

   WHERE village='[villagename from post]' OR district='[districtname from post'] OR governorate='[govname from post]'
   

这样你只需要一个 IF 和一个输出。它还具有更好的扩展性。

1. 尝试在手动插入测试数据的情况下运行每个 sql(PHP myAdmin 对此非常方便)，看看问题是出在 sql 还是 php 级别。

2. @ - 尽量避免它。最好以不会产生错误的方式编写代码。你把它放在那里是为了一个空的索引错误吗？如果是这样，这可能是您的问题。检查您的 HTML 并查看上面的答案 1。