learningcurve Posted June 20, 2012 Share Posted June 20, 2012 I have a query with two drop down boxes populated from the db through a query. Everything is working great. I now want to add an option for ALL. Not sure how I would do that with the way I have populated the boxes. Thanks! <html> <head> </head> <body> <?php //db connections or error returns $connect = mysql_connect(DELETED); if (!$connect) { exit('<p>Unable to connect to the server at this time.</p>'); } mysql_select_db("surveys"); if (!mysql_select_db("surveys")) { exit ('<p>Unable to locate the database at this time.</p>'); } //if the "submit" variable does not exist, the form has not been submitted - display initial page if (!isset($_POST['submit'])) { //form ?> <form name="sgid" id="sgid" method="post" action="sgidsurveyquery.php"> <h1>SGID Satisfaction Survey Results</h1> <h2>Results can be filtered by evaluation party and semester/year given.</h2></br> Please choose: <?php //pull evaluator drop down choices from db as array $query="SELECT q1 FROM surveys.SGID_satisfaction"; $result=mysql_query($query); $options=""; while ($row=mysql_fetch_array($result)){ $evaluator=$row("q1"); $options.="<OPTION VALUE=>$evaluator</option"; } ?> <select name="evaluator"> <option value=0> <? echo $options?> </select> for <?php //pull date drop down choices from db as array $query="SELECT q2 FROM surveys.SGID_satisfaction"; $result=mysql_query($query); $options=""; while ($row=mysql_fetch_array($result)){ $date=$row("q2"); $options.="<OPTION VALUE=>$date</option"; } ?> <select name="date"> <option value=0> <? echo $options?> </select> <input type="submit" name ="submit" value="Show Results"> </form> <?php } else { //display results print"<h2>SGID Satisfaction Survey Results</h2><br/>"; $evaluator=$_POST['evaluator']; $date=$_POST['date']; //run query $query = "SELECT q1, q2, q3, q4, q5 FROM surveys.SGID_satisfaction WHERE q1='$evaluator' AND q2= '$date' ORDER BY created"; echo "<h3>Results of SGID Satisfaction Survey performed by $evaluator during $date</h3></br>"; //display number of records returned $result = mysql_query ($query) or die(mysql_error()); $num_rows = mysql_num_rows($result); echo "Your query returned $num_rows responses."; //Start a table print "<table CELLPADDING=10 border =1 >"; print "<tr>"; print "<th style='width:150px'>Which organization did your SGID?</th>"; print "<th style='width:100px'>Please specify the semester and year the SGID was done.</th>"; print "<th style='width:300px'>What about this service was helpful for your teaching?</th>"; print "<th style='width:300px'>What about this service could be improved to support your teaching?</th>"; print "<th style='width:300px'>Would you be willing for us to contact you later about what, if anything, you have changed and how that has affected student learning?</th>"; print "</tr>"; while ($row = @mysql_fetch_array($result)) { print "<table CELLPADDING=10 border = 1>"; print "<tr>"; print "<td style='width:150px'>".$row['q1']."</td>"; print "<td style='width:100px'>".$row['q2']."</td>"; print "<td style='width:300px'>".$row['q3']."</td>"; print "<td style='width:300px'>".$row['q4']."</td>"; print "<td style='width:300px'>".$row['q5']."</td>"; print "</tr>"; print "</table>"; } //close results mysql_close(); } ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/ Share on other sites More sharing options...
Drummin Posted June 20, 2012 Share Posted June 20, 2012 Add a checkbox to your form for "all". Update DB query as follows. //run query $query = "SELECT q1, q2, q3, q4, q5 FROM surveys.SGID_satisfaction "; if (isset($_POST['all'])){ //run query without conditions $query .= "ORDER BY created"; }else( //run query with conditions $query .= "WHERE q1='$evaluator' AND q2= '$date' ORDER BY created"; } NOTE: Don't use short tags <? ?>. Use full tags <?php ?> Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1355625 Share on other sites More sharing options...
learningcurve Posted June 20, 2012 Author Share Posted June 20, 2012 Thanks, but I think I was unclear. I want to add All as a choice in the drop down. So they could pick all for drop down 1 and still have a choice for the second drop down and vice versa. Not just a generic ALL. Thanks for the good answer though. Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1355627 Share on other sites More sharing options...
Drummin Posted June 20, 2012 Share Posted June 20, 2012 See how this works. (Untested) <html> <head> </head> <body> <?php //db connections or error returns $connect = mysql_connect(DELETED); if (!$connect) { exit('<p>Unable to connect to the server at this time.</p>'); } mysql_select_db("surveys"); if (!mysql_select_db("surveys")) { exit ('<p>Unable to locate the database at this time.</p>'); } //if the "submit" variable does not exist, the form has not been submitted - display initial page if (!isset($_POST['submit'])) { //form ?> <form name="sgid" id="sgid" method="post" action="sgidsurveyquery.php"> <h1>SGID Satisfaction Survey Results</h1> <h2>Results can be filtered by evaluation party and semester/year given.</h2></br> Please choose: <?php //pull evaluator drop down choices from db as array $query="SELECT q1 FROM surveys.SGID_satisfaction"; $result=mysql_query($query); $options=""; $options.="<option value=\"0\">Select</option>"; while ($row=mysql_fetch_array($result)){ $evaluator=$row("q1"); $options.="<option value=\"$evaluator\">$evaluator</option>"; } $options.="<option value=\"all\">All</option>"; ?> <select name="evaluator"> <?php echo $options; ?> </select> for <?php //pull date drop down choices from db as array $query="SELECT q2 FROM surveys.SGID_satisfaction"; $result=mysql_query($query); $options=""; $options.="<option value=\"0\">Select</option>"; while ($row=mysql_fetch_array($result)){ $date=$row("q2"); $options.="<option value=\"$date\">$date</option>"; } $options.="<option value=\"all\">All</option>"; ?> <select name="date"> <?php echo $options; ?> </select> <input type="submit" name ="submit" value="Show Results"> </form> <?php } else { //display results print"<h2>SGID Satisfaction Survey Results</h2><br/>"; $evaluator = mysql_real_escape_string($_POST['evaluator']); $date = mysql_real_escape_string($_POST['date']); $search = array(); //run query $query = "SELECT q1, q2, q3, q4, q5 FROM surveys.SGID_satisfaction "; if ($evaluator!="0" && $evaluator!="all"){ $search[] ="q1='$evaluator'"; } if ($date!="0" && $date!="all"){ $search[] ="q2='$date'"; } if (count($search)>=1){ $query .= "WHERE "; $query .= implode(' AND ',$search); } $query .= " ORDER BY created"; echo "<h3>Results of SGID Satisfaction Survey performed by $evaluator during $date</h3></br>"; //display number of records returned $result = mysql_query ($query) or die(mysql_error()); $num_rows = mysql_num_rows($result); echo "Your query returned $num_rows responses."; //Start a table print "<table CELLPADDING=10 border =1 >"; print "<tr>"; print "<th style='width:150px'>Which organization did your SGID?</th>"; print "<th style='width:100px'>Please specify the semester and year the SGID was done.</th>"; print "<th style='width:300px'>What about this service was helpful for your teaching?</th>"; print "<th style='width:300px'>What about this service could be improved to support your teaching?</th>"; print "<th style='width:300px'>Would you be willing for us to contact you later about what, if anything, you have changed and how that has affected student learning?</th>"; print "</tr>"; while ($row = mysql_fetch_array($result)) { print "<table cellpadding=10 border = 1>"; print "<tr>"; print "<td style='width:150px'>".$row['q1']."</td>"; print "<td style='width:100px'>".$row['q2']."</td>"; print "<td style='width:300px'>".$row['q3']."</td>"; print "<td style='width:300px'>".$row['q4']."</td>"; print "<td style='width:300px'>".$row['q5']."</td>"; print "</tr>"; print "</table>"; } //close results mysql_close(); } ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1355649 Share on other sites More sharing options...
learningcurve Posted June 21, 2012 Author Share Posted June 21, 2012 Sorry, doesn't work. I don't get an error message and the form only displays on the page until it hits what should be the first drop down. I am going through your code but I am enough of a newbie that I don't get all of it. Thanks for the real escape string though! Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1355770 Share on other sites More sharing options...
Drummin Posted June 21, 2012 Share Posted June 21, 2012 Man I can't believe I didn't spot that those () in place of []. Try this. <html> <head> </head> <body> <?php //db connections or error returns $connect = mysql_connect(DELETED); if (!$connect) { exit('<p>Unable to connect to the server at this time.</p>'); } mysql_select_db("surveys"); if (!mysql_select_db("surveys")) { exit ('<p>Unable to locate the database at this time.</p>'); } //if the "submit" variable does not exist, the form has not been submitted - display initial page if (!isset($_POST['submit'])) { //form ?> <form name="sgid" id="sgid" method="post" action="sgidsurveyquery.php"> <h1>SGID Satisfaction Survey Results</h1> <h2>Results can be filtered by evaluation party and semester/year given.</h2></br> Please choose: <?php //pull evaluator drop down choices from db as array $query="SELECT q1 FROM surveys.SGID_satisfaction"; $result=mysql_query($query); $options=""; $options.="<option value=\"0\">Select</option>"; while ($row=mysql_fetch_array($result)){ $evaluator=$row['q1']; $options.="<option value=\"$evaluator\">$evaluator</option>"; } $options.="<option value=\"all\">All</option>"; $options.="<option value=\"test\">test</option>"; ?> <select name="evaluator"> <?php echo $options; ?> </select> for <?php //pull date drop down choices from db as array $query="SELECT q2 FROM surveys.SGID_satisfaction"; $result=mysql_query($query); $options=""; $options.="<option value=\"0\">Select</option>"; while ($row=mysql_fetch_array($result)){ $date=$row['q2']; $options.="<option value=\"$date\">$date</option>"; } $options.="<option value=\"all\">All</option>"; $options.="<option value=\"test2\">test</option>"; ?> <select name="date"> <?php echo $options; ?> </select> <input type="submit" name ="submit" value="Show Results"> </form> <?php } else { //display results print"<h2>SGID Satisfaction Survey Results</h2><br/>"; $evaluator = mysql_real_escape_string($_POST['evaluator']); $date = mysql_real_escape_string($_POST['date']); $search = array(); //run query $query = "SELECT q1, q2, q3, q4, q5 FROM surveys.SGID_satisfaction "; if ($evaluator!="0" && $evaluator!="all"){ $search[] ="q1='$evaluator'"; } if ($date!="0" && $date!="all"){ $search[] ="q2='$date'"; } if (count($search)>=1){ $query .= "WHERE "; $query .= implode(' AND ',$search); } $query .= " ORDER BY created"; echo "<h3>Results of SGID Satisfaction Survey performed by $evaluator during $date</h3></br>"; //display number of records returned $result = mysql_query ($query) or die(mysql_error()); $num_rows = mysql_num_rows($result); echo "Your query returned $num_rows responses."; //Start a table print "<table CELLPADDING=10 border =1 >"; print "<tr>"; print "<th style='width:150px'>Which organization did your SGID?</th>"; print "<th style='width:100px'>Please specify the semester and year the SGID was done.</th>"; print "<th style='width:300px'>What about this service was helpful for your teaching?</th>"; print "<th style='width:300px'>What about this service could be improved to support your teaching?</th>"; print "<th style='width:300px'>Would you be willing for us to contact you later about what, if anything, you have changed and how that has affected student learning?</th>"; print "</tr>"; while ($row = @mysql_fetch_array($result)) { print "<table CELLPADDING=10 border = 1>"; print "<tr>"; print "<td style='width:150px'>".$row['q1']."</td>"; print "<td style='width:100px'>".$row['q2']."</td>"; print "<td style='width:300px'>".$row['q3']."</td>"; print "<td style='width:300px'>".$row['q4']."</td>"; print "<td style='width:300px'>".$row['q5']."</td>"; print "</tr>"; print "</table>"; } //close results mysql_close(); } ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1355829 Share on other sites More sharing options...
learningcurve Posted June 21, 2012 Author Share Posted June 21, 2012 Okay... Very, very, very close... it is perfect except it returns all possible choices every time they occur instead of just once. For example, the column in the db contains red, blue and green. I want red, blue, green and all to show up in the drop down options. What is showing up is red 29 times (or however many times it occurs in the db), blue 15 times, green 12 and all once - you get the idea. The good news is that whatever i select works and returns results. So... I kinda feel like I am close but no cigar. I do appreciate your help and guidance in getting me this far - you are a gem! Any more nuggets of wisdom? Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1355908 Share on other sites More sharing options...
Drummin Posted June 21, 2012 Share Posted June 21, 2012 OH, so these tables are holding many records with the same "color" value. Try wrapping the table field in DISTINCT() within your query. For example. $query="SELECT DISTINCT(q1) FROM surveys.SGID_satisfaction"; Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1355933 Share on other sites More sharing options...
learningcurve Posted June 22, 2012 Author Share Posted June 22, 2012 I was thinking the same thing on my way home but couldn't remember what the exact word was - thought I was going to have to pull out a book. That did it! Thank you so much for your help - I now have a great working query!! (and much more knowledge about PHP - I feel like I am finally staring to "get" it). Lisa Quote Link to comment https://forums.phpfreaks.com/topic/264524-adding-all-to-drop-down-populated-by-db-query/#findComment-1356123 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.