Jump to content

Adding all to drop down populated by db query

learningcurve

By learningcurve
in PHP Coding Help

 Share

Recommended Posts

learningcurve Member

learningcurve

Posted
Posted

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>

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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 ?>

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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>

Link to comment
Share on other sites
learningcurve Member

learningcurve

Posted
  • Author
Posted

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!

 

Link to comment
Share on other sites
Drummin Member

Drummin

Posted
Posted

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>

Link to comment
Share on other sites
learningcurve Member

learningcurve

Posted
  • Author
Posted

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?

 

Link to comment
Share on other sites
learningcurve Member

learningcurve

Posted
  • Author
Posted

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

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.