CODEWITHSUNDEEP
phphtmlmysql
resontant81
resontant81

Reputation: 39

MySQL Select based on drop down value

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

Upvotes: 0

Views: 10694

Answers (2)

Jose Manuel Abarca Rodr&#237;guez
Jose Manuel Abarca Rodr&#237;guez

Reputation: 10391

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>";

Upvotes: 1

Brian
Brian

Reputation: 1025

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>';
}
?>

Upvotes: 0

Related Questions