Create a drop-down list that options fetched from a MySQL database in PHP

In many scenarios, we may need to create a dropdown input that can display all the options consistent with the current state of the database. This form of input is used many times in real life and the following examples may help to understand the same.

1. A set of students who have unique registration numbers.
2. A set of branch names and their branch ids.
3. A list of categories to which a particular product must belong.

In this article, we will create a drop-down with a list of categories to which a particular product must belong.

Approach: In each of these examples, if we use a drop-down menu that fetches data from the database the user will be able to enter data more accurately and the UI will be more user-friendly.

• A database with a table of categories and another table of products with a foreign key to the category ID to which the particular product belongs.
• HTML form that accepts the data.

Database creation:

CLick on the “new” button to make a new database

Create a new database with name “example_store”

To run SQL and prepare the database

MySQL queries:

-- Table structure for table `category` CREATE TABLE `category` ( `Category_ID` int(11) NOT NULL, `Category_Name` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Dumping data for table `category` INSERT INTO `category` (`Category_ID`, `Category_Name`) VALUES (1, 'Category A '), (2, 'Category B'); -- Table structure for table `product` CREATE TABLE `product` ( `Product_ID` int(11) NOT NULL, `product_name` varchar(255) NOT NULL, `category_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Dumping data for table `product` INSERT INTO `product` (`Product_ID`, `product_name`, `category_id`) VALUES (1, 'Product A1', 1), (2, 'Product A2', 1), (3, 'Product B1', 2); -- Primary Key Constraints ALTER TABLE `category` ADD PRIMARY KEY (`Category_ID`); ALTER TABLE `product` ADD PRIMARY KEY (`Product_ID`), ADD KEY `Category_constraint` (`category_id`); -- AUTO_INCREMENT for table `category` ALTER TABLE `category` MODIFY `Category_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- AUTO_INCREMENT for table `product` ALTER TABLE `product` MODIFY `Product_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- Foreign Key Constraints ALTER TABLE `product` ADD CONSTRAINT `Category_constraint` FOREIGN KEY (`category_id`) REFERENCES `category` (`Category_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

Example: We create a PHP file in a folder called “example_store” in htdocs and create the following form.php webpage which can be accessed in a browser at “localhost/example_store/form.php”.

Источник

Create a PHP Dropdown List

1. Create a Dropdown List in PHP
2. Create a Static Dropdown List in PHP
3. Create a Dynamic Dropdown List in PHP

This article will introduce the dropdown list and create one with PHP.

Create a Dropdown List in PHP

A dropdown list is a group of items in a list. The contents are not visible until you click on the small arrow.

Let’s first look at the Static dropdown list.

Create a Static Dropdown List in PHP

A static dropdown list is a simple PHP dropdown box with no database connection. We will create a static dropdown box for some programming languages in the example code below.

We will then use PHP to echo out the selected language.

//Create a static dropdown box  form id="L" method="post">  select name="Language">  option value="PHP">PHPoption>  option value="Python">Pythonoption>  option value="Java">Javaoption>  option value="C++">C++option>  select> input type="submit" name="Submit" value="Submit"> form>  php if(isset($_POST['Language']))   echo "Selected Language: ".htmlspecialchars($_POST['Language']); > ?> 

The dropdown box should look like this.

We clicked the arrow to display the full list of items in the dropdown box in the image above. Let’s try and choose the language PHP from the menu and see what happens.

That is how you create a dropdown box without a database connection. Let’s now look at the dynamic dropdown list.

Create a Dynamic Dropdown List in PHP

A dynamic dropdown list fetches contents from a database. Let’s look at an example.

We have a MySQL database called sample tutorial . In our database, we have the table parkinglot .

From the table above, we will create a dropdown box that fetches the contents of our BrandName row.

First, we will create a database connection and use the SELECT * FROM function to fetch the contents of the BrandName row. Lastly, we will create a dropdown menu for the above items.

php $user = 'root'; $pass = ''; $db = 'sample tutorial';  $con = mysqli_connect("localhost", $user, $pass, $db);   $sql = "SELECT `BrandName` FROM `parkinglot1` WHERE 1;";  $car_brands = mysqli_query ($con, $sql);  ?>         Car Brands:   --- Select ---      while ($cat = mysqli_fetch_array(  $car_brands,MYSQLI_ASSOC)):;   ?>       endwhile;  ?>       

The code is a success. We managed to fetch the contents of our table from our database and use them in our dropdown box.

This article shows how to create the two dropdown lists types in PHP.

The code for the dynamic dropdown box does not execute when you select any of the car brands. It only shows the contents in our database.

John is a Git and PowerShell geek. He uses his expertise in the version control system to help businesses manage their source code. According to him, Shell scripting is the number one choice for automating the management of systems.

Источник

Handling select box (drop-down list) in a PHP form

This tutorial will show you how to add select boxes and multi-select boxes to a form, how to retrieve the input data from them, how to validate the data, and how to take different actions depending on the input.

Select box

Let’s look at a new input: a “select” box, also known as a “drop-down” or “pull-down” box. A select box contains one or more “options”. Each option has a “value”, just like other inputs, and also a string of text between the option tags. This means when a user selects “Male”, the “formGender” value when accessed by PHP will be “M”.

 p> What is your Gender? select name="formGender">  option value="">Select. option>  option value="M">Maleoption>  option value="F">Femaleoption>  select>  p> 

The selected value from this input was can be read with the standard $_POST array just like a text input and validated to make sure the user selected Male or Female.

 php  if(isset($_POST['formSubmit']) )   $varMovie = $_POST['formMovie'];  $varName = $_POST['formName'];  $varGender = $_POST['formGender'];  $errorMessage = "";   // - - - snip - - -  >  ?> 

It’s always a good idea to have a “blank” option as the first option in your select box. It forces the user to make a conscious selection from the box and avoids a situation where the user might skip over the box without meaning to. Of course, this requires validation.

( For a generic, easy to use form validation script, see PHP Form Validation Script )

Multi-select

Suppose you want to present a select box that allows the user to select multiple options.

Here is how to create such an input in HTML:

 label for='formCountries[]'>Select the countries that you have visited:label>br> select multiple="multiple" name="formCountries[]">  option value="US">United Statesoption>  option value="UK">United Kingdomoption>  option value="France">Franceoption>  option value="Mexico">Mexicooption>  option value="Russia">Russiaoption>  option value="Japan">Japanoption>  select> 

Please note the similarity to a checkbox group. First, set multiple=“multiple” as a property of the select box. Second, put [ ] at the end of the name. Finally, we don’t really need a “blank” option in this select box, because we can simply check to make sure the user selected something or not. To select multiple values, use the shift or ctrl buttons when clicking.

The PHP code to process this field is very similar to the checkbox code. $_POST[‘formCountries’] returns an array of the selected values.

 php  if(isset($_POST['formSubmit']))   $aCountries = $_POST['formCountries'];   if(!isset($aCountries))    echo("
You didn't select any countries!\n");  >  else    $nCountries = count($aCountries);   echo("
You selected $nCountries countries: ");  for($i=0; $i  $nCountries; $i++)    echo($aCountries[$i] . " ");  >  echo("
");  > >  ?> 

As before, use “isset” is to make sure some values were selected.

Using switch

Now, let’s change the multi-select box back to a standard single select box. We want to now perform different actions based on what selection the user makes. You could write a bunch of “if” statements, but that could get messy. Let’s look at two ways: dynamic commands and the switch statement.

These two approaches have their pro’s and con’s. The switch method is basically a concise method of writing a bunch of “if” statements. Each case matches the variable passed the switch and performs all actions after that case up until a break statement. In this case, each case is redirecting to the corresponding page to the selected country. If the selected country is not found in one of the cases, the “default” case is assumed, and “Error!” is displayed.

The second method is just passing the selected value to the header function to redirect to the correct page.

The first method requires writing more code, but is more secure because it ensures the form only redirects to 6 pre-programmed cases, or else displays an error message and ends execution.

The second method is much more concise, but less secure because a malicious user could monkey around with the form and submit whatever value he wants. If using method 2, it’s a good idea to validate the selected country first, to make sure it won’t result in a redirect to a malicious page.

Download Sample Code

Download the PHP form select samples below:

Источник

How To Populate Drop Down List From Database Using PHP and MySQLi

In this tutorial, I will teach you how to Populate Drop Down List From Database Using PHP and MySQLi. This method has the ability to fill the data in a drop-down list form the MySQLi database.

This article can answer the question asked on another site.

Watch the video here before we proceed.

Follow the step by step procedure in Populating Drop Down List using PHP.

Lets Begin:

First , create a landing page and name it “index.php“.

Second, do the following code in the page that you have created.

Populate Drop Down List From Database Using PHP and MySQLi

Select a Book:
#box < width: 100%; >.column < width: 33.33%; display: inline-block; >.form < padding: 2px; width: 100%; display: inline-block; >.first-column < display: inline-block; width: 100px; height: 2px; margin: 2px; position: inherit; >.second-column < display: inline-block; width: 150px; height: 2px; margin: 2px; position: inherit; >select < width: 150px; font-size: 12px; height: 25px; padding: 4px >.clear
Finally, create a function for retrieving data from the database and fill it into the select box.

 Select'; $sqli = "SELECT * FROM tblbooks"; $result = mysqli_query($con, $sqli); while ($row = mysqli_fetch_array($result)) < echo ''.$row['BookTitle'].''; > echo ''; ?>

You can watch the video here about the PHP CREATE, READ, UPDATE AND, DELETE SOURCE CODE to get the full idea on how to create a simple PHP CRUD.

Another freebie from the author, watch and download the full source code of Leave Management System in PHP.

You can also visit here the PHP Projects with source code free download if you are looking for some complete PHP projects with source code.

Or if you want a crash course in PHP, you check here the PHP and MySQL Tutorial for beginners in 7 days.

Inquiries

If you have any questions or suggestions about PHP and MySQLi database tutorial, please feel free to contact us at our contact page or simply leave a comment below.

Источник