php - Join two tables and only output rows that exist in the first table -
i'm working on form allows users apply several award categories. can apply several categories, can apply each category once.
the award categories stored in table: 
when user applies category, row entered following table user's nominee_id , award_category_id: 
i code show user awards have applied already, works fine.
$result = mysql_query(" select award_subsection.*, award_subsection.title subsection_title, award_nomination_category.* award_subsection inner join award_nomination_category on award_subsection.id=award_nomination_category.award_category_id award_subsection.active='1' , award_subsection.award_id='$id' , award_subsection.additional_function='award category' , award_nomination_category.active='1' , award_nomination_category.award_id='$id' , award_nomination_category.nominee_id='$nominee_id' "); $category_count = mysql_num_rows($result); if($category_count > 0) { echo' <div id="column_full" class="reverse_margin"> <div id="subsection_heading"> <h3>you have selected following award categories:</h3> </div><!-- --><div id="subsection_content"> <!-- '; while ($row = mysql_fetch_array($result)) { echo ' --><div id="column_third"> <h3>' . $row['subsection_title'] . '</h3> </div><!-- '; } echo' --> </div> </div> '; } but problem when want render drop down menu shows categories user have not applied to. following code works user has applied 1 category previously, when user applies more 1, drop down menu displays categories. suspect because since there 2 rows of application same user, script running through table twice , getting of categories.
$result = mysql_query(" select award_subsection.*, award_subsection.id subsection_id, award_subsection.title subsection_title, award_nomination_category.* award_subsection join award_nomination_category on award_subsection.id!=award_nomination_category.award_category_id award_subsection.active='1' , award_subsection.award_id='$id' , award_subsection.additional_function='award category' , award_nomination_category.active='1' , award_nomination_category.award_id='$id' , award_nomination_category.nominee_id='$nominee_id' group award_subsection.id "); while($row = mysql_fetch_array($result)) { echo ' <option value="' . $row['subsection_id'] . '">' . $row['subsection_title'] . '</option> '; } is there way php know categories selected in first query , remove them in second query? thx in advance!
i figured out!!
here's code if anyone's interested:
// check applied categories $result = mysql_query(" select award_subsection.*, award_subsection.id subsection_id, award_subsection.title subsection_title, award_nomination_category.* award_subsection inner join award_nomination_category on award_subsection.id=award_nomination_category.award_category_id award_subsection.active='1' , award_subsection.award_id='$id' , award_subsection.additional_function='award category' , award_nomination_category.active='1' , award_nomination_category.award_id='$id' , award_nomination_category.nominee_id='$nominee_id' "); $category_count = mysql_num_rows($result); if($category_count > 0) { echo' <div id="column_full" class="reverse_margin"> <div id="subsection_heading"> <h3>you have selected following award categories:</h3> </div><!-- --><div id="subsection_content"> <!-- '; // create array of ids nominee has applied $selected_categories = array(); while ($row = mysql_fetch_array($result)) { $selected_categories[] = $row['subsection_id']; echo ' --><div id="column_third"> <h3>' . $row['subsection_title'] . '</h3> </div><!-- '; } echo' --> </div> </div> '; } echo' <form method="post" action="award-nomination-submit.php" data-parsley-validate> <div id="column_full" class="reverse_margin"> <input type="hidden" name="award_id" value="' . $id . '"> <input type="hidden" name="nominee_id" value="' . $nominee_id . '"> <input type="hidden" name="step" value="' . $step . '"> <h3>select award category:</h3> <select class="visual_form" name="award_category" required> <option value="" selected>please select</option> '; // build award categories drop down $result = mysql_query(" select * award_subsection active='1' , award_id='$id' , additional_function='award category' "); while($row = mysql_fetch_array($result)) { // if id exists in array, don't echo out in drop down if(in_array($row['id'], $selected_categories)) { echo''; } else { echo ' <option value="' . $row['id'] . '">' . $row['title'] . '</option> '; } } echo ' </select> </div> '; basically, created array out of ids output first sql query. in second sql query, compared id ids in array , echo out ones doesn't match :)
Comments
Post a Comment