MySQL Select based on drop down value

Question

I have the following code:

<?php
session_start();
include_once("config.php");

$query = "SELECT Category FROM books";
$result = mysqli_query ($mysqli, $query);

echo '<select name="dropdown" value=""><option value="">Dropdown</option>';
    while($row = mysqli_fetch_array($result))
    {
        echo '<option value="' . $row['Category'] . '">' . $row['Category'] . '</option>';
    }
    echo "</select>";

?>

the values of the drop down box are filled from the database. I was wondering if theres a way to have a select statement that will run when a user clicks on one of the options in the drop down menu and then populate the results in a table?

any information will help!

Thanks

Jose Manuel Abarca Rodríguez · Accepted Answer · 2015-03-17 23:12:52Z

1

Ok, resontant81, you want to fill a table depending on the option selected, next code does exactly what you want, the explanation comes just after :

<html>
  <head>
    <title>My list</title>
    <script type="text/javascript">
//----------------------------------------------------------------
// SENDS SELECTED OPTION TO RETRIEVE DATA TO FILL TABLE.
function send_option () {
var sel = document.getElementById( "my_select" );
var txt = document.getElementById( "my_option" );
txt.value = sel.options[ sel.selectedIndex ].value;
var frm = document.getElementById( "my_form" );
frm.submit();
}
//----------------------------------------------------------------
    </script>
  </head>
  <body>

    Click on any option
    <br/>
    <select id="my_select" onchange="send_option();">
      <option>Select an option</option>
<?php
//----------------------------------------------------------------
// LIST FILLED FROM DATABASE (ALLEGEDLY).
for ( $i = 0; $i < 5; $i++ )
{ $text = chr($i+65) . chr($i+65) . chr($i+65);
  echo "<option value='" . $text . "'>" . $text . "</option>";
}
//----------------------------------------------------------------
?>
    </select>
    <br/> 
    <br/>
    <table>
<?php
//----------------------------------------------------------------
// TABLE FILLED FROM DATABASE ACCORDING TO SELECTED OPTION.
if ( IsSet( $_POST["my_option"] ) ) // IF USER SELECTED ANY OPTION.
     for ( $i = 0; $i < 4; $i++ ) // DISPLAY ROWS.
     { echo "<tr>";
       for ( $j = 0; $j < 6; $j++ ) // DISPLAY COLUMNS.
         echo "<td>" . $_POST["my_option"] . "</td>"; // DISPLAY OPTION.
       echo "</tr>";
     }
else echo "<tr><td>Table empty</td></tr>";
//----------------------------------------------------------------
?>
    </table>

<!-- FORM TO SEND THE SELECTED OPTION. -->
    <form method="post" action"01.php" style="display:none" id="my_form">
      <input type="text" id="my_option" name="my_option"/>
    </form>

  </body>
</html>

To make things easier for you (and for me), I am not using a database, all you have to do is copy-paste previous code to a text file, rename it "01.php" (because that's the action of the form, you can change it), and run it in your browser, is ready to use.

The dropdown is filled from database (in this case, with letters), when an option is selected the page reloads with the selected option and fills the table.

You said: "a select statement that will run when a user clicks on one of the options in the drop down menu and then populate the results in a table". This select statement you want you must put it right after the line :

if ( IsSet( $_POST["my_option"] ) ) // IF USER SELECTED ANY OPTION.

So your select statement will take the selected option from $_POST and use it to retrieve the right data and display it.

Let me know if it helps you.

This is the code to fill the dropdown, it's my code with yours combined:

// LIST FILLED FROM DATABASE (ALLEGEDLY).
$query = "SELECT Category FROM books";
$result = mysqli_query ($mysqli, $query);
while ( $row = mysqli_fetch_array($result) )
  echo "<option value='" . $row['Category'] . "'>" . $row['Category'] . "</option>";

Next edit is to fill the table. Change the query for the right one if it's not right :

// TABLE FILLED FROM DATABASE ACCORDING TO SELECTED OPTION.
$query = "SELECT Category FROM books where category like '" . $_POST["my_option"] . "'";
$result = mysqli_query ($mysqli, $query);
while( $row = mysqli_fetch_array($result) )
  echo "<tr>" .
       "<td>" . $row['book_name'] . "</td>" .
       "<td>" . $row['author'] . "</td>" .
       "<td>" . $row['Category'] . "</td>" .
       "</tr>";

edited Mar 17, 2015 at 23:12

answered Mar 17, 2015 at 15:04

Jose Manuel Abarca Rodríguez

10.4k3 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

resontant81 Over a year ago

Hi thanks for the question. Where tou said "all you have to do is copy-paste previous code to a text file" do you mean the original code that i posted?

Jose Manuel Abarca Rodríguez Over a year ago

No, I mean my own code, copy-paste my code, the code I posted (it will run like a charm).

Jose Manuel Abarca Rodríguez Over a year ago

Try adding your own code to fill the dropdown list. If you can make that work, then try adding your own code to fill the table using $_POST["my_option"] as parameter of you select statement. We are almost done!

resontant81 Over a year ago

Hmm im stuck on getting my fields from the database into the drop down box.

resontant81 Over a year ago

Ah okay yes now the drop down box has been populated. I never added line: include_once('config.php'); at the top

|

Brian · Accepted Answer · 2015-03-17 00:46:32Z

I'm assuming $mysqli is your db connection and it's made through config.php. I'm also assuming that category is a column name in the books table. It is up to you to sanitize and validate the user input. This is simply an example to get you started.

page.php ....

<?php
session_start();
include_once("config.php");

function categories() {

    global $mysqli;
    $result = "";
    $stmt = "SELECT Category FROM books GROUP BY Category";
    $sql = mysqli_query ($mysqli, $stmt);
    while ($row = $sql->fetch_array(MYSQLI_BOTH))
    {
    $result .= '<option value="' . $row['Category'] . '">' . $row['Category'] . '</option>';
    }
    mysqli_free_result($sql);
    mysqli_close($mysqli);

    return $result;

}

IF (isset($_POST['ThisForm'])) {

  $category = htmlspecialchars(strip_tags(trim($_post['dropdown'])));
  $stmt = "SELECT * FROM books WHERE category ='$category'";
  $sql = mysqli_query ($mysqli, $stmt);
  while ($row = $sql->fetch_array(MYSQLI_BOTH))
  {
    // do something with result

  }
  // free result and close connection
  mysqli_free_result($sql);
  mysqli_close($mysqli);

}ELSE{
  // base form 
  echo '<form action="page.php" name="something" method="post">';
  echo '<select name="dropdown" value=""><option value="">Dropdown</option>'.categories().'</select>';
  echo '<input type="submit" name="ThisForm" value="submit" />';
  echo '<form>';
}
?>

Collectives™ on Stack Overflow

MySQL Select based on drop down value

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related