Select Option Dropdown

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”.

Источник

Многоуровневый select из базы данных

Примеры построения многоуровневых выпадающих списков (select option) и базы данных с применением рекурсии PHP.

В скриптах используется MySQL-таблица `category` с полями `id` , `parent` , `name` , где поле `parent` содержит id родителя.

Оформление вложенности пробелами

1. В начале получаем все записи из БД в виде ассоциативного массива.
2. С помощью функции array_to_tree() преобразуем его в древовидный, к элементам массива добавляется элемент «children» в который перемещаются все дочерние элементы.
3. С помощью функции out_options() рекурсивно выводятся все элементы массива.
4. Во втором аргументе функции out_options() указывается id элемента, которому нужно установить selected .

prepare("SELECT * FROM `category` ORDER BY `name`"); $sth->execute(); $category = $sth->fetchAll(PDO::FETCH_ASSOC); $category = array_to_tree($category); function array_to_tree($array, $sub = 0) < $a = array(); foreach($array as $v) < if($sub == $v['parent']) < $b = array_to_tree($array, $v['id']); if(!empty($b)) < $a[$v['id']] = $v; $a[$v['id']]['children'] = $b; >else < $a[$v['id']] = $v; >> > return $a; > function out_options($array, $selected_id = 0, $level = 0) < $level++; $out = ''; foreach ($array as $i =>$row) < $out .= '$out .= '>'; if ($level > 1) < $out .= str_repeat('#emsp;', $level - 1); >$out .= $row['name'] . ''; if (!empty($row['children'])) < $out .= out_options($row['children'], $selected_id, $level); >> return $out; > ?>

Результат:

Оформление символами псевдографики

Оформление ветвей дерева с помощью символов ├ и └:

prepare("SELECT * FROM `category` ORDER BY `name`"); $sth->execute(); $category = $sth->fetchAll(PDO::FETCH_ASSOC); $category = array_to_tree($category); function array_to_tree($array, $sub = 0) < $a = array(); foreach($array as $v) < if($sub == $v['parent']) < $b = array_to_tree($array, $v['id']); if(!empty($b)) < $a[$v['id']] = $v; $a[$v['id']]['children'] = $b; >else < $a[$v['id']] = $v; >> > return $a; > function out_options($array, $selected_id = 0, $level = 0) < $level++; $out = ''; foreach ($array as $i =>$row) < $out .= '$out .= '>'; if ($level > 1) < if ($level >2) < $out .= str_repeat(' ', $level - 1); >$keys = array_keys($array); $last_keys = $keys[count($array) - 1]; if ($last_keys != $i) < $out .= '├'; >else < $out .= '└'; >> $out .= $row['name'] . ''; if (!empty($row['children'])) < $out .= out_options($row['children'], $selected_id, $level); >> return $out; > ?>

Результат:

Использование optgroup

Использование . оправдано если необходимо выбрать только крайнюю категорию в дереве, но optgroup не поддерживает вложенность и никакие пробельные символы в начале label=». » . Поэтому в примере используется — – широкое тире.

prepare("SELECT * FROM `category` ORDER BY `name`"); $sth->execute(); $category = $sth->fetchAll(PDO::FETCH_ASSOC); $category = array_to_tree($category); function array_to_tree($array, $sub = 0) < $a = array(); foreach($array as $v) < if($sub == $v['parent']) < $b = array_to_tree($array, $v['id']); if(!empty($b)) < $a[$v['id']] = $v; $a[$v['id']]['children'] = $b; >else < $a[$v['id']] = $v; >> > return $a; > function out_optgroup_options($array, $selected_id = 0, $level = 0) < $level++; $out = ''; foreach ($array as $i =>$row) < if (empty($row['children'])) < $out .= '$out .= '>'; if ($level > 1) < $out .= str_repeat('#emsp;', $level - 1); >$out .= $row['name'] . ''; > else < $out .= ''; $out .= out_optgroup_options($row['children'], $selected_id, $level); > > return $out; > ?>

Источник

How to Insert Select Option Value in Database Using PHP & MySQL

In this tutorial, You will learn to insert select option values in the database using PHP & MySQL with some simple steps. These steps are very easy to understand and implement in web applications.

Here, I have taken only a single dropdown input field to store select option values in the database. Once you learn it, you will easily customize it according to your project requirement.

How to Store Dropdown Value in Database in PHP

Before getting started it’s coding, you should create the following folder structure –

codingstatus/ |__database.php |__ select-option.php |__ insert-option.php

Learn Also –

Now, let’s start to store dropdown values in the database step by step –

1. Create SQL Database & Table

First of all, You will have to create a database with the name of “codingstatus”.

Database Name – codingstatus

CREATE DATABASE codingstatus;

After that, create a table with the name of “courses” in the database “codingstatus.

CREATE TABLE `courses` ( `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, `courseName` varchar(255) DEFAULT NULL, );

2. Connect PHP to MySQL

To insert select option value in the database, you must connect PHP to MySQL database with the help of the following query.

• $hostName – It must contain hostname.
• $userName – It must contain username of the database.
• $password – It must contain password of the database
• $database – It must contain database name.

connect_error) < die("Connection failed: " . $conn->connect_error); > ?>

3. Create Input Field for Select Option

Here, I have created a single dropdown input field with some select options that contain some course name.

File Name – select-option.php

  Select Course Web Designing Web Development app development Game Development Graphic Desiging Digital Marketing  

4. Insert Select Option in Database

To insert select option in the database, you will have to implement the following steps to write its code –

Step-1: First of all, apply if condition withisset($_POST[‘submit’]) to check form is set or not

Step-2: Assign course name input to the variable $courseName

Step-3: check course name is empty or not using empty() with if statement. if it is true then follow the next step

Step-4: write MySQL insert query to insert the select option value in the “courses” table

Step-5: if select option is inserted into database successfully then print a success message

File Name – insert-option.php

Insert Select Option Value with another Input Value using PHP & MySQL

Now, You will learn to insert a section option value with another input field like fullName into another table. In the previous step, we have inserted static option values. But In this step, We will display select option values from a database and then insert them into another table of the same database

Before getting started, You will have to create the following two files –

Also, Create another table Name – students with the help of the following query –

CREATE TABLE `students` ( `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, `fullName` varchar(255) DEFAULT NULL, `courseName` varchar(255) DEFAULT NULL, );

Create a form and display select option values

First of all, Include database.php and insert-script.php file

Then create an HTML form with a select option & text input field and display data from the database in the select option

Select Coursequery($query); if($result->num_rows> 0)< while($optionData=$result->fetch_assoc()) < $option =$optionData['courseName']; ?> > ?>

Insert Select Option value & Text Input Value

In this step, Write a MySQL query to insert select option value and text input value into the dabase.

File Name – insert-script.php

Источник

Show selected option value from Array & MySQL DB using PHP

In this tutorial, you will learn how to create an array of categories, display the values inside HTML select box and have selected options pre-defined with PHP and also I will show you how to get the selected options from a database using the ID of the record.

         body < font-size: 2em; >.container < display: grid; grid-template-rows: repeat(2, 1fr); grid-template-columns: repeat(2, 1fr); grid-gap: 6rem; padding: 5rem; border: 1px solid #ccc; justify-content: space-evenly; >select 
Selected From Array
 
Selected From DB Record
 
"; foreach($options as $option)< if($selected == $option) < echo "$option"; > else < echo "$option"; > > echo ""; ?>
 
 if(isset($_GET['category'])) < $categoryName = $_GET['category']; $sql = "SELECT * FROM categories WHERE if($result = mysqli_query($link, $sql)) < if(mysqli_num_rows($result) >0) < while($row = mysqli_fetch_array($result))< $dbselected = $row['category']; >// Function frees the memory associated with the result mysqli_free_result($result); > else < echo "Something went wrong. "; >> else < echo "ERROR: Could not execute $sql." . mysql_error($link); >> $options = array('Comedy', 'Adventure', 'Drama', 'Crime', 'Adult', 'Horror'); echo ""; foreach($options as $option)< if($dbselected == $option) < echo "$option"; > else < echo "$option"; > > echo ""; ?>