Duplication in results using php mysql and inner join -
i have 3 drop list contain retrieved values database 3 different tables
governorate:
- governosrate_id
- governorate_name
district:
- district_id
- district_name
village:
- id
- village_name
memebrs:
- user_id
- user_name
- governorate
- district
- village want when user select one of 3 or of them drop list system must display result related selected 1
but problem when user select governorate work fine when select governorate , district duplicate result related selected value , if user choose governorate district , village result become triples
anyone can me ????
i show code of 3 types
and if have complaint try not beat me
code:
by governorate
//**********search 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 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 governorate = '$bygov'")or die(mysql_error("error: querying 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 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 selected governorate"; } by district
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 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 district = '$bydist'")or die(mysql_error("error: querying 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 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 selected district"; } by village
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 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 village = '$byvillage'")or die(mysql_error("error: querying 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 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 selected district"; } }
a few simple things worth trying:
echo or var_dump $_post result ensure getting data think getting. may cause of problem. getting more 1
it seems messy code cause of problem here. have 3 output sections have 3 possible outputs, of can triggered when appropriate if conditions satisfied. try echoing 'here' 'there' , 'everywhere' or words of choice within each block - see suspect code is!
a better option might change clause like:
where village='[villagename post]' or district='[districtname post'] or governorate='[govname post]'
this way need 1 if , 1 ouput. scales better.
try running each sql test data manually inserted (php myadmin handy this) , see if problem @ sql or php level.
@ - try , avoid it. better write code in way not generate errors. did put there empty index erro? if so, might problem. check html , see answer 1 above.
Comments
Post a Comment