php - inner join with allowing user to choose between search type do not work -
i have search page tat allow users search using search governorate or district or village
if user choose governorate browser display right answers
but if user choose governorate , district system change display same result
governorate
- governorate_id
- governorate_name district
- district_id
- district_name
- governorate_id
village
- id
- village_name
- district_id
members user_id
- governorate_id
- district_id
- village_id
i want when user choose 1 of types or of the system must display users list related selection not users
code:
$errormsg = ""; $outputlist = ""; //**********search locationn***************************************// if(isset($_post['listbyq'])) { //********************by governorate**************************************// if($_post['listbyq']=="by_gov") { $bygov = $_post['governorate']; @ $bydist = $_post['district']; @ $byvillage = $_post['village']; echo $bygov; echo $bydist; echo $byvillage; $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 ($bygov = '' or governorate = '$bygov') , ($bydist = '' or district = '$bydist') , ($byvillage = '' or village = '$byvillage')") or die(mysql_error()); $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"; } }
the issues where
clause:
governorate = '$bygov' or district = '$bydist' or village = '$byvillage'"
these connected or
not and
.
presumably, when selects governate , district, want district and governate, not district or governate. 1 approach code where
clause like:
where ($bygov = '' or governorate = '$bygov') , ($bydist = '' or district = '$bydist') , ($byvillage = '' or village = '$byvillage')
another approach use custom code @ application level customize where
clause each case, like:
$where = ''; if ($bygov != '') {$where = $where." governate = '$bygov' , "} if ($bydist != '') {$where = $where." district = '$bydist' and"} if ($byvillage != '') {$where = $where." village = '$byvillage' and"} $where = $where." 1=1"
Comments
Post a Comment