重复的结果使用php mysql和内部连接


Duplication in results using php mysql and inner join

我有三个下拉列表,包含从3个不同的表中检索到的值

省:

  • governosrate_id
  • governorate_name
地区:

  • district_id
  • district_name

村:

  • id
  • village_name

memebrs:

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

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

任何人都可以帮助我????

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

如果有人要抱怨,尽量不要打我

<标题>代码:由省

//**********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";
   }
}

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

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

  2. 看起来很可能是混乱的代码导致了这里的问题。您有三个输出部分,因此您有三个可能的输出,当满足适当的IF条件时,所有这些输出都可以被触发。尝试在每个块中返回"Here"、"There"answers"Everywhere"或您选择的单词-您很快就会看到可疑代码在哪里!

  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。