Databases
StackOverflow2013
Postsdbo
PODuplication in results using php mysql and inner join
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODuplication in results using php mysql and inner join
    primarykey
    data
    text
    <p>I have three drop list that contain retrieved values from the database from 3 different tables </p> <p>governorate:</p> <ul> <li>governosrate_id</li> <li>governorate_name</li> </ul> <p>district:</p> <ul> <li>district_id</li> <li>district_name</li> </ul> <p>village:</p> <ul> <li>id</li> <li>village_name</li> </ul> <p>memebrs:</p> <ul> <li>user_id</li> <li>user_name</li> <li>governorate</li> <li>district</li> <li>village what i want is when the user select <strong>one of the three or all of them</strong> drop list the system must display the result that are related to the selected one </li> </ul> <p>but the problem is that when the user select from the governorate it work fine but when it select governorate and district it duplicate the result related to the selected value and if the user choose governorate district and village the result become triples </p> <p>anyone can help me ????</p> <p>i will show the all the code of the three types </p> <p>and if anyone have a complaint try to not beat me </p> <h1>code:</h1> <h1>by governorate</h1> <pre><code>//**********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 &gt; 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="&lt;img src=\"$check_pic\"width=\"120px\"/&gt;"; } else { $user_pic="&lt;img src=\"$default_pic\"width=\"120px\"/&gt;"; } $outputlist.=' &lt;table width="100%"&gt; &lt;tr&gt; &lt;td width="23%" rowspan="5"&gt;&lt;div style="height:120px;overflow:hidden;"&gt;&lt;a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank"&gt;'.$user_pic.'&lt;/a&gt;&lt;/div&gt;&lt;/td&gt; &lt;td width="14%"&gt;&lt;div align="right"&gt;Name:&lt;/div&gt;&lt;/td&gt; &lt;td width="63%"&gt;&lt;a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank"&gt;'.$row_first_name.' '.$row_last_name.'&lt;/a&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Birth date:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_birthdate.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Registered:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_registered_date.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Job:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_spec.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Location:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_gov.'__'.$row_dist.'__'.$row_village.'&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;hr /&gt; '; } } } else { $errorMSG = "No member within this selected governorate"; } </code></pre> <h1>by district</h1> <pre><code> 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 &gt; 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="&lt;img src=\"$check_pic\"width=\"120px\"/&gt;"; } else { $user_pic="&lt;img src=\"$default_pic\"width=\"120px\"/&gt;"; } $outputlist.=' &lt;table width="100%"&gt; &lt;tr&gt; &lt;td width="23%" rowspan="5"&gt;&lt;div style="height:120px;overflow:hidden;"&gt;&lt;a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank"&gt;'.$user_pic.'&lt;/a&gt;&lt;/div&gt;&lt;/td&gt; &lt;td width="14%"&gt;&lt;div align="right"&gt;Name:&lt;/div&gt;&lt;/td&gt; &lt;td width="63%"&gt;&lt;a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank"&gt;'.$row_first_name.' '.$row_last_name.'&lt;/a&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Birth date:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_birthdate.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Registered:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_registered_date.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Job:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_spec.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Location:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_gov.'__'.$row_dist.'__'.$row_village.'&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;hr /&gt; '; } } } else { $errorMSG = "No member within this selected District"; } </code></pre> <h1>by village</h1> <pre><code>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 &gt; 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="&lt;img src=\"$check_pic\"width=\"120px\"/&gt;"; } else { $user_pic="&lt;img src=\"$default_pic\"width=\"120px\"/&gt;"; } $outputlist.=' &lt;table width="100%"&gt; &lt;tr&gt; &lt;td width="23%" rowspan="5"&gt;&lt;div style="height:120px;overflow:hidden;"&gt;&lt;a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank"&gt;'.$user_pic.'&lt;/a&gt;&lt;/div&gt;&lt;/td&gt; &lt;td width="14%"&gt;&lt;div align="right"&gt;Name:&lt;/div&gt;&lt;/td&gt; &lt;td width="63%"&gt;&lt;a href = "http://localhost/newadamKhoury/profile.php?user_id='.$row_id.'" target="_blank"&gt;'.$row_first_name.' '.$row_last_name.'&lt;/a&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Birth date:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_birthdate.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Registered:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_registered_date.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Job:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_spec.'&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;&lt;div align="right"&gt;Location:&lt;/div&gt;&lt;/td&gt; &lt;td&gt;'.$row_gov.'__'.$row_dist.'__'.$row_village.'&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;hr /&gt; '; } } } else { $errorMSG = "No member within this selected District"; } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USLebDev
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODuplication in results using php mysql and inner join
      1. This table or related slice is empty.
      1. VTModeratorReview
    2. VO
      singulars
      1. PODuplication in results using php mysql and inner join
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSO is not a code review site. You are supposed to try to solve the problem, demonstrate what you have tried and ask a pointed question. In the FAQ, you will find hints on how to ask. No beating required.
      singulars
      1. PODuplication in results using php mysql and inner join
      1. USmichi
    2. COJust use GROUP BY your any repeated filed in the end of every query.
      singulars
      1. PODuplication in results using php mysql and inner join
      1. USManish Jangir
    3. COWhat, pray tell, is the `specialization` table? Is it possible its contents are increasing the cardinality of your result set?
      singulars
      1. PODuplication in results using php mysql and inner join
      1. USO. Jones
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload