Cannot get drop down to work...

lordterrin · September 2, 2014

Hi There,

I have the following code that I've pieced together from various sources. Neither PHP nor SQL are my main languages, so I'm still learning how this works. I'd like some advice on why this isn't working:

<?php

$con=mysqli_connect("localhost","root","","dwinvoice");

// Check connection

if (mysqli_connect_errno()) {

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}



$getPM = "Select Distinct PMName from report";

$results = mysqli_query($con,$getPM)

?>

<select name="select1">

<?php    

    while ($line = mysql_fetch_array($getPM, MYSQL_ASSOC))

?>

    <option value="<?php echo $line['field'];?>"> <?php echo $line['field'];?> </option>     

</select>



<?php

$result = mysqli_query($con,"SELECT * FROM report WHERE PMName = $results");



echo "<table border='1'>

<tr>

<th>Track Number</th>

<th>PM Name</th>

</tr>";



while($row = mysqli_fetch_array($result)) {

echo "<tr>";

echo "<td>" . $row['TrackNumber'] . "</td>";

echo "<td>" . $row['PMName'] . "</td>";

echo "</tr>";

}



echo "</table>";



mysqli_close($con);

?>

I am trying to build a dropdown menu that will pull all distinct names from the PMName column in the table 'report', then take that selection and utilize it in the HTML table down below. I know the syntax here:

$result = mysqli_query($con,"SELECT * FROM report WHERE PMName = $results");

is wrong, but I'm not sure how to make it right. The drop-down list does not actually pull any values, though it displays on the page, and I'm not sure what I'm doing wrong....

Barand · September 2, 2014

Try

echo $line['PMName '];

as that is the name of the column that you are selecting.

jazzman1 · September 2, 2014

in addition what the master said, try to wrap $result with single quotes (assuming PMName is a char type in mysql)

$result = mysqli_query($con,"SELECT * FROM report WHERE PMName = '$results'");

lordterrin · September 2, 2014

in addition what the master said, try to wrap $result with single quotes (assuming PMName is a char type in mysql)
$result = mysqli_query($con,"SELECT * FROM report WHERE PMName = '$results'");

Awesome - this worked. Thank you so much!

lordterrin · September 2, 2014

So - I've gotten this code to work successfully as long as I'm entering a specific person's name, then passing that form value into my SQL statement (using the tip you provided above), but I still don't know how to have the "form" auto-populate into a drop-down menu. Maybe I'm missing something from what you said earlier....

Here is a snippet of code. The box is appearing, but it's not pulling anything out of my SQL database:

<head>
<script language="javascript1.2">     
    function submit( f ) { 
      f.submit(); 
    } 
  </script> 
</head>
 
<?php 
include('./db.php');
$PM = mysqli_query($con, "SELECT DISTINCT PMName FROM report" ); 
echo "<b>Select a PM:</b> \n"; 
echo " <select name='PMName' onChange='submit(this.form)'>\n"; 
while( $row = mysql_fetch_row( $PM )) { 
  $sel = ( $table === $row[0] ) ? "id='sel' selected" : ""; 
  printf( " <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] ); 
} 
echo " </select>\n"; 
echo " <input id='edit' type='button' value='GO' onClick='submit(this.form)'>\n"; 
?> 
 
</form>
</body>
</html>

Edited September 2, 2014 by lordterrin

mentalist · September 2, 2014

Where is $table set?

lordterrin · September 2, 2014

Where is $table set?

It is not set anywhere. I don't even know what the specific function of $table does (as I just copied the example from another website to try and figure it out...)....

lordterrin · September 2, 2014

I found another one that also seems very straightforward, but I can't get it to work for me either....:

<?php 
include('./db.php');  //this is my connection info
 
//SQL Query
$query="SELECT DISTINCT PMName FROM report";
 
//Do Query
$result = mysqli_query ($con,$query);
 
 
echo "<select name='element_2' id='element_2'>";
 
while($row=mysql_fetch_array($result))
{
echo "<option value='$row[PMName]'</option>";
}
echo "</select>";
 
?>

Boreas · September 2, 2014

You should probably be using mysqli_fetch_row, as you are using mysqli API not mysql:

<?php 
include('./db.php');
$PM = mysqli_query($con, "SELECT DISTINCT PMName FROM report" ); 
echo "<b>Select a PM:</b> \n"; 
echo " <select name='PMName' onChange='submit(this.form)'>\n"; 
while( $row = mysqli_fetch_row( $PM )) { 
  $sel = ( $table === $row[0] ) ? "id='sel' selected" : ""; 
  printf( " <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] ); 
} 
echo " </select>\n"; 
echo " <input id='edit' type='button' value='GO' onClick='submit(this.form)'>\n"; 
?>

$table is there so that an option value from the drop down can be pre selected from some other user input etc

#ref your next post, again you need mysqli_fetch_array not mysql_fetch_array if using mysqli API

Edited September 2, 2014 by Boreas

lordterrin · September 2, 2014

Awesome - that worked! All of the examples I'm finding (to try and learn) use mysql_query - which seems to not work anymore since it's been replaced with mysqli. It makes reproducing things very difficult. The only issue I'm having now is that I don't see where my drop-down selection is SAVED. The code is "working", in that the dropbox is now populated with the list I want. My only question now is - what variable is this saved to so i can pass it correctly into the SQL statement below:

$result = mysqli_query($con,"SELECT * FROM report WHERE PMName = '$PMSelection'");

<html>

<head>

<script language="javascript1.2">     

    function submit( f ) { 

      f.submit(); 

    } 

  </script> 

</head>

 

<body>

<form>

 

<?php 

include('./db.php');

$PM = mysqli_query($con, "SELECT DISTINCT PMName FROM report ORDER BY PMName ASC" ); 

echo "<b>Select a PM:</b> \n"; 

echo " <select name='PMName' onChange='submit(this.form)'>\n"; 

while( $row = mysqli_fetch_row( $PM )) { 

  $sel = ( $table === $row[0] ) ? "id='sel' selected" : ""; 

  printf( " <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] ); 

} 

echo " </select>\n"; 

echo " <input id='edit' type='button' value='GO' onClick='submit(this.form)'>\n"; 

?> 

 

</form>

</body>

</html>

 

 

<script language="javascript1.2"> 

//alert("I am an alert box!");    

</script> 

 

<?php

 

$PMSelection = $_POST["PM"];

 

$result = mysqli_query($con,"SELECT * FROM report WHERE PMName = '$PMSelection'");

 

echo "<table border='1'>

<tr>

<th>Track Number</th>

<th>PM Name</th>

<th>Reg NSB$  </th>

<th>Total Backlog $  </th>

<th>Average Backlog Margin by Track #  </th>

<th>Reg RGP%  </th>

<th>PM Comments  </th>

</tr>";

 

while($row = mysqli_fetch_array($result)) {

  echo "<tr>";

  echo "<td>" . $row['TrackNumber'] . "</td>";

  echo "<td>" . $row['PMName'] . "</td>";

  echo "<td>" . $row['RegNSB'] . "</td>";

  echo "<td>" . $row['TotalBacklog'] . "</td>";

  echo "<td>" . $row['AverageBacklogTrackMargin'] . "</td>";

  echo "<td>" . $row['RegRGP'] . "</td>";

  echo "<td>" . $row['PMComments'] . "</td>";

  echo "</tr>";

}

 

echo "</table>";

 

mysqli_close($con);

?>

This snippet:

$PMSelection = $_POST["PMName"];

$result = mysqli_query($con,"SELECT * FROM report WHERE PMName = '$PMSelection'");

came from an earlier edition where I was manually typing in the name I wanted to see - as the whole POST reference isn't even in there anymore I'm not surprised this isn't working

I just don't see where the dropdown list is passing on a variable, (maybe it's not...).

Edited September 2, 2014 by lordterrin

Boreas · September 2, 2014

Few things:

- You should specify the form's method as post / get

<form method="post" enctype="multipart/form-data">

...

</form>

- Your <select> tag has name="PMName", therefore the passed variable (either via GET or POST will be $_GET["PMName"] or $_POST["PMName"]. You currently have $PMSelection = $_POST["PM"];

$PMSelection = $_POST["PMName"];

lordterrin · September 2, 2014

edited - I had some comment lines in there where I shouldn't have

This works perfectly now. Thank you SO much for your help!

Edited September 2, 2014 by lordterrin

Boreas · September 2, 2014

No problem, glad you got it working. As a word of advice, you need to sanitise any data coming from client side before using in a query to your database. For example you are using the selection of the <select> held in $PMSelection directly in a db query without escaping it or checking it against a white list.

Do some reading on escaping (escape, escape, escape is generally the idea) and on white listing / sanitising inputs from clients. If you don't validate what has come from a user then you are open to sql injection

Good luck

Sign In

Cannot get drop down to work...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information