How to Pass Dropdown Selected Value to Database in PHP - onlyxcodes

Saturday 22 July 2023

How to Pass Dropdown Selected Value to Database in PHP

This article demonstrates how to use PHP and MySQL to insert, update, and delete select option values in a database.


We frequently need to develop a dropdown list input that may store the values of the selected options in the database.


For instance, a list of students with specific registration numbers and a drop-down list of nations that asks for the student's exact country name values.


In this tutorial, we'll use PHP to develop a selection option list with a list of names of nations and carry out CRUD operations. 


how to pass dropdown selected value to database in php

Table Content

1. Tools used for this Application

2. Make a Database and Table

3. Project Directory Structure

4. Establish Database Connection

5. Display Data from the Database

6. Insert New Records

    6.1 PHP Insert Code with PDO

7. Update Record

    7.1 PHP Update Code With PDO

8. Delete Record

    8.1 PHP Delete Code with PDO


Tools used for this Application

Notepad++ –  We have written all of our codes in this editor.


Database – We made use of the MySQL database on the back end.


Design Pages – All of our front-end web pages were created using Bootstrap.


Server – The XAMPP server served as our local host.


Make a Database and Table

Create a database first in PhpMyAdmin by following these steps.


Step 1: Click the Database tab in PhpMyAdmin after it has opened.


click the database tab in phpmyadmin after it has opened.

Step 2: Click the Create button after typing "php_dropdown_crud_db" in the database name field.


click the create button after typing php_dropdown_crud_db in the database name field.

second method,


To create a database named "php_dropdown_crud_db", run the SQL query below.


CREATE DATABASE IF NOT EXISTS php_dropdown_crud_db;

Project Directory Structure

Check out the project directory setup in the xampp/htdocs folder location below.


xampp/
├── htdocs/
	├── Pass-Dropdown-Selected-Value-Database-PHP/
		├── bootstrap/
			├── css/
			│	└── bootstrap.min.css
			├── js/
			│	└── bootstrap.min.js
			
		├── js/jquery-1.12.4-jquery.min.js
		
		dbconfig.php
		index.php
		add.php
		edit.php

Put this project in the wamp/www folder directory if you're using WampServer.


wamp/
├── wwww/
	├── Pass-Dropdown-Selected-Value-Database-PHP/
		├── bootstrap/
			├── css/
			│	└── bootstrap.min.css
			├── js/
			│	└── bootstrap.min.js
			
		├── js/jquery-1.12.4-jquery.min.js
		
		dbconfig.php
		index.php
		add.php
		edit.php		

The following PHP files must be created after creating a database: 


dbconfig.php – We will set up the MySQL database connection in this file. 


index.php – We will display select option records from the MySQL database table in this file. 


add.php – We will use this file to store the value of a select option in the database. 


edit.php – We will alter a selected value from the dropdown using this file.


Establish Database Connection

This is a database connection file that is being accessed through the PDO extension, therefore copy the code below and create a new file called "dbconfig.php" before pasting it within the file.


<?php
$db_host="localhost"; //localhost server 
$db_user="root";	//database username
$db_password="";	//database password   
$db_name="php_dropdown_crud_db";	//database name

try
{
	$db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
	$e->getMessage();
}

?>

Display Data from the Database

This is the index.php file. In this file, we get/populated select option values from the database.


The Bootstrap package was used to create the UI for this web page, which simply displays the table code.


We created a tabular database with all of the records.


<table class="table table-striped table-bordered table-hover">
    <thead>
        <tr>
			<th>Name</th>
			<th>Country Name</th>
            <th>Edit</th>
            <th>Delete</th>
        </tr>
    </thead>
    <tbody>
	<?php
	$select_stmt=$db->prepare("SELECT * FROM tbl_person");	//sql select query
	$select_stmt->execute();
	while($row=$select_stmt->fetch(PDO::FETCH_ASSOC))
	{
	?>
        <tr>
            <td><?php echo $row['name']; ?></td>
			<td><?php echo $row['country_name']; ?></td>
            <td><a href="edit.php?update_id=<?php echo $row['id']; ?>" class="btn btn-warning">Edit</a></td>
            <td><a href="?delete_id=<?php echo $row['id']; ?>" class="btn btn-danger">Delete</a></td>
        </tr>
    <?php
	}
	?>
    </tbody>
</table>

Look at the table's user interface below to see the populated select option values.


get/populated select option values from the database

Insert New Records

This is the "add.php" file. This file provides a user interface for adding new records to the database. 


Using the Bootstrap framework, we create an intuitive form in this file. There are two fields on the form. The first field is the name, while the second is a select option with values for several countries.


Keep an eye on two variables after the ending </form> tag. Both $insertMsg and $errorMsg are accessible.


The $errorMsg variable will display a relevant message with the bootstrap alert box if any errors are caused in the PHP insert code.


The $insertMsg variable will display the data inserted successfully message with the bootstrap success box if a new record is successfully stored in the PHP insert code.


<form method="post" class="form-horizontal">
					
	<div class="form-group">
	<label class="col-sm-3 control-label">Name</label>
	<div class="col-sm-6">
	<input type="text" name="txt_name" class="form-control" placeholder="enter name" />
	</div>
	</div>
				
	<div class="form-group">
	<label class="col-sm-3 control-label">Choose Country</label>
	<div class="col-sm-6">
	 <select name="txt_country" class="form-control">
		<option value=""> --- select country --- </option>
		<option value="united states"> United States </option>
		<option value="united kingdom"> United Kingdom </option>
		<option value="australia"> Australia </option>
		<option value="germany"> Germany </option>
	 </select>
	</div>
	</div>
						
	<div class="form-group">
	<div class="col-sm-offset-3 col-sm-9 m-t-15">
	<input type="submit"  name="btn_insert" class="btn btn-success" value="Insert">
	<a href="index.php" class="btn btn-danger">Cancel</a>
	</div>
	</div>
					
</form>

<?php
if(isset($errorMsg))
{
?>
	<div class="alert alert-danger alert-dismissible">
		<strong>WRONG ! <?php echo $errorMsg; ?></strong>
	</div>
<?php
}
if(isset($insertMsg))
{
?>
	<div class="alert alert-success alert-dismissible">
		<strong>SUCCESS ! <?php echo $insertMsg; ?></strong>
	</div>
<?php
}
?>			

Look at the insert form this type is UI.


Insert Form:


select option insert form - select option php mysql

PHP Insert Code with PDO

Above the <!DOCTYPE html> tag in the add.php file, there is PHP insert code with the PDO extension.


This PHP insert code runs when a user types any name, selects a country from the select option tag, and then clicks the insert button.


If there is no problem, this code will add new database records with the name and selected dropdown value. 


<?php

require_once "dbconfig.php";

if(isset($_REQUEST['btn_insert']))
{

	$name = $_REQUEST['txt_name'];	//textbox name "txt_firstname"
	$country_name = $_REQUEST['txt_country'];	//textbox name "txt_lastname"
		
	if(empty($name)){
		$errorMsg="Please Enter Name";
	}
	else if(empty($country_name)){
		$errorMsg="Please Select Country";
	}
	else
	{
		try
		{
			if(!isset($errorMsg))
			{
				$insert_stmt=$db->prepare('INSERT INTO tbl_person(name,country_name) VALUES(:name,:cname)'); //sql insert query					
				$insert_stmt->bindParam(':name',$name);
				$insert_stmt->bindParam(':cname',$country_name);   //bind all parameter
					
				if($insert_stmt->execute())
				{
					$insertMsg="Inserted Successfully........"; //execute query success message
					header("refresh:3;index.php"); //refresh 3 second and redirect to index.php page
				}
			}
		}
		catch(PDOException $e)
		{
			echo $e->getMessage();
		}
	}
}

?>

Code Explanation:


Row no 3 – Using the require_once function keyword, we included the 'dbconfig.php' database connection file.


require_once "dbconfig.php";

Row no 5 – The value of the insert form button's "btn_insert" property was gathered using the global variable $_REQUEST[ ]. 


This button request was set via the isset() function.


if(isset($_REQUEST['btn_insert']))
{

Row no 8 to 9 – We retrieve the values for the txt_name and txt_country name attributes from the insert form using the global variable $_REQUEST[ ].


The name attribute value of the input name text box is txt_name.


The name attribute of the select option tag is txt_country.


The variables $name and $country_name retain the values for those attributes.


$name = $_REQUEST['txt_name'];	//textbox name "txt_firstname"
$country_name = $_REQUEST['txt_country'];	//textbox name "txt_lastname"

Row no 11 to 16 – We initialize the variables $name and $country_name within the empty function and check that they do not have empty values.


if(empty($name)){
	$errorMsg="Please Enter Name";
}
else if(empty($country_name)){
	$errorMsg="Please Select Country";
}

Row no 17 to 38 – We start the try/catch block inside the else condition. 


We apply the PDO insert query in the prepare() statement and store new records using the database object of $db.


The :name and :cname values of the variables in the insert query are bound by the bindParam() function. These values are contained in the variables $name and $country_name. 


The execute() function runs our PDO insert query and adds new records to the database table if there are no errors present.


Within three seconds, the header() function shows the successful record insertion message and redirects the viewer to the "index.php" web page.


Here,


All applicable error messages are stored in the $errorMsg variable, while record insert messages are stored in the $insertMsg variable.


else
{
	try
	{
		if(!isset($errorMsg))
		{
			$insert_stmt=$db->prepare('INSERT INTO tbl_person(name,country_name) VALUES(:name,:cname)'); //sql insert query					
			$insert_stmt->bindParam(':name',$name);
			$insert_stmt->bindParam(':cname',$country_name);   //bind all parameter
					
			if($insert_stmt->execute())
			{
				$insertMsg="Inserted Successfully........"; //execute query success message
				header("refresh:3;index.php"); //refresh 3 second and redirect to index.php page
			}
		}
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
	}
}

Update Record

In the previous section, we discussed how to add new entries with select option values to the database. In this section, we'll look at how to modify an existing record of select options in the table. 


The editing form is located in the "edit.php" file. 


Using the Bootstrap package, the edit form has the same layout as the insert form.


They can access specific selected dropdown value data for editing on this form by clicking the edit hyperlink, which directs them to the database. 


<td><a href="edit.php?update_id=<?php echo $row['id']; ?>" class="btn btn-warning">Edit</a></td>

Here, 


The $name variable in the name text box contains references to actual database records in the value attribute.


Additionally, a selected option record is stored in the value attribute of the select option tag's $country_name variable.


If there is a problem with the PHP update codes, the $errorMsg will display the appropriate error message in a Bootstrap alert box.


If the PHP update scripts successfully run, the $updateMsg will display the record update message with the Bootstrap success box.


<form method="post" class="form-horizontal">
					
	<div class="form-group">
	<label class="col-sm-3 control-label">Name</label>
	<div class="col-sm-6">
	<input type="text" name="txt_name"  value="<?php echo $name; ?>" class="form-control">
	</div>
	</div>
				
	<div class="form-group">
	<label class="col-sm-3 control-label">Choose Country</label>
	<div class="col-sm-6">
	 <select name="txt_country" value="<?php echo $country_name; ?>" class="form-control">
		<option value="united states"> United States </option>
		<option value="united kingdom"> United Kingdom </option>
		<option value="australia"> Australia </option>
		<option value="germany"> Germany </option>
	</select>
	</div>
	</div>	
			
	<div class="form-group">
	<div class="col-sm-offset-3 col-sm-9 m-t-15">
	<input type="submit" name="btn_update" class="btn btn-primary" value="Update">
	<a href="index.php" class="btn btn-danger">Cancel</a>
	</div>
	</div>
					
</form>
		
<?php
if(isset($errorMsg))
{
?>
    <div class="alert alert-danger alert-dismissible">
        <strong>WRONG ! <?php echo $errorMsg; ?></strong>
    </div>
<?php
}
if(isset($updateMsg))
{
?>
	<div class="alert alert-success alert-dismissible">
		<strong>UPDATED ! <?php echo $updateMsg; ?></strong>
	</div>
<?php
}
?

The user interface for the update form appears if they click on the edit hyperlink.


Update Form:


select option update form - select option php - select option with php -

PHP Update Code With PDO

This is the PDO-enabled PHP update code that appears at the top of the edit.php file.


This PHP code updates the new record and deletes the old record if the user selects a different option value. 


This PHP update operation leaves the selected option record intact if a user doesn't select another option value.


<?php

require_once "dbconfig.php";

if(isset($_REQUEST['update_id']))
{
	try
	{
		$id = $_REQUEST['update_id']; //get "update_id" from index.php page through anchor tag operation and store in "$id" variable
		$select_stmt = $db->prepare('SELECT * FROM tbl_person WHERE id =:id'); //sql select query
		$select_stmt->bindParam(':id',$id);
		$select_stmt->execute(); 
		$row = $select_stmt->fetch(PDO::FETCH_ASSOC);
		extract($row);
	}
	catch(PDOException $e)
	{
		$e->getMessage();
	}
	
}

if(isset($_REQUEST['btn_update']))
{
	
	$name_up	= $_REQUEST['txt_name'];	//textbox name "txt_name"
	$country_up	= $_REQUEST['txt_country'];	//select box name "txt_country"
		
	if(empty($name_up)){
		$errorMsg="Please Enter Name";
	}
	else if(empty($country_up)){
		$errorMsg="Please Select Country";
	}	
	else
	{
		try
		{
			if(!isset($errorMsg))
			{
				$update_stmt=$db->prepare('UPDATE tbl_person SET name=:nm_up, country_name=:cn_up WHERE id=:id'); //sql update query
				$update_stmt->bindParam(':nm_up',$name_up);
				$update_stmt->bindParam(':cn_up',$country_up);	//bind all parameter
				$update_stmt->bindParam(':id',$id);
				 
				if($update_stmt->execute())
				{
					$updateMsg="Record Updated Successfully.......";	//record updated success message
					header("refresh:3;index.php");	//refresh 3 second and redirect to index.php page
				}
			}	
		}
		catch(PDOException $e)
		{
			echo $e->getMessage();
		}	
	}	
}
?>

Code Explanation:


Row no 3 –  Using the require_once function keyword, we included the 'dbconfig.php' database connection file.


require_once "dbconfig.php";

Row no 5 to 21 – The update_id variable receives a specific row id from the table when a user clicks on the edit hyperlink. 


The $_REQUEST[ ] global variable and the isset() function set the update_id variable request appropriately.


The try/catch block is initiated.


The new $id variable is holding the request for the update_id variable.


We apply the PDO select query with the WHERE clause in the prepare() statement and pick particular row id records using the database object of $db.


The variable :id value in the select query is bound using the bindParam() function. This value is stored in the $id variable. 


Our PDO select query is executed by the execute() function.


According to PDO::FETCH ASSOC, the outcome must be returned as an array key. The column names in our table will be the same as the array keys. 


The variable $row has all of the keys allocated to it. Inserted into the extract() function is the $row variable. In this function, the array values are used as variable values and the array keys are used as variable names. 


if(isset($_REQUEST['update_id']))
{
	try
	{
		$id = $_REQUEST['update_id']; //get "update_id" from index.php page through anchor tag operation and store in "$id" variable
		$select_stmt = $db->prepare('SELECT * FROM tbl_person WHERE id =:id'); //sql select query
		$select_stmt->bindParam(':id',$id);
		$select_stmt->execute(); 
		$row = $select_stmt->fetch(PDO::FETCH_ASSOC);
		extract($row);
	}
	catch(PDOException $e)
	{
		$e->getMessage();
	}
	
}

The update code then begins. The update code is identical to the insert code; the update query is the only difference.


Row no 23 – To get the "btn_update" attribute value for the update form button, we used the global variable $_REQUEST[ ].


if(isset($_REQUEST['btn_update']))
{

Row no 26 to 27 –  We retrieve the values for the txt_name and txt_country name attributes from the update form using the global variable $_REQUEST[ ].


The name attribute value of the input name text box is txt_name.


The name attribute of the select option tag is txt_country.


These attribute values are held by the variables $name_up and $country_up.


$name_up	= $_REQUEST['txt_name'];	//textbox name "txt_name"
$country_up	= $_REQUEST['txt_country'];	//select box name "txt_country"

Row no 29 to 34 – We set the variables $name_up and $country_up within the empty function and check that they do not have empty values.


if(empty($name_up)){
	$errorMsg="Please Enter Name";
}
else if(empty($country_up)){
	$errorMsg="Please Select Country";
}

Row no 35 to 57 – We start the try/catch block inside the else condition. 


We apply the PDO update query with the WHERE clause condition in the prepare() statement and update existing records using the database object of $db.


The update query's variables :nm_up, :cn_up, and :id are bound by the bindParam() function. These values are stored in the $name_up, $country_up, and $id variables. 


Finally, the execute() function runs our PDO update query and updates any existing records in the database table if there are no errors.


Within three seconds, the header() function shows the record update success message and redirects the web page to the "index.php" web page.


Here,


The $updateMsg variable stores the record update message, and the $errorMsg variable stores any necessary error messages.


else
{
	try
	{
		if(!isset($errorMsg))
		{
			$update_stmt=$db->prepare('UPDATE tbl_person SET name=:nm_up, country_name=:cn_up WHERE id=:id'); //sql update query
			$update_stmt->bindParam(':nm_up',$name_up);
			$update_stmt->bindParam(':cn_up',$country_up);	//bind all parameter
			$update_stmt->bindParam(':id',$id);
				 
			if($update_stmt->execute())
			{
				$updateMsg="Record Updated Successfully.......";	//record updated success message
				header("refresh:3;index.php");	//refresh 3 second and redirect to index.php page
			}
		}	
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
	}	
}	

Delete Record

The last step is to delete the selected option record from the database.


Specific data is selected for deletion using the table id when a user clicks on the delete hyperlink.


<td><a href="?delete_id=<?php echo $row['id']; ?>" class="btn btn-danger">Delete</a></td>

PHP Delete Code with PDO

The index.php file contains PHP deletion code that begins above the top of the page. 


The database record with the given row id is deleted using the following PHP delete code.


<?php

require_once "dbconfig.php";
	
if(isset($_REQUEST['delete_id']))
{
	// select record from db to delete
	$id=$_REQUEST['delete_id'];	//get delete_id and store in $id variable
		
	$select_stmt= $db->prepare('SELECT * FROM tbl_person WHERE id =:id');	//sql select query
	$select_stmt->bindParam(':id',$id);
	$select_stmt->execute();
	$row=$select_stmt->fetch(PDO::FETCH_ASSOC);
		
	//delete an orignal record from db
	$delete_stmt = $db->prepare('DELETE FROM tbl_person WHERE id =:id');
	$delete_stmt->bindParam(':id',$id);
	$delete_stmt->execute();
		
	header("Location:index.php");
}
	
?>

Code Explanation:


Using the super global variable $_REQUEST[ ], we receive the delete_id variable request when a user clicks on the delete hyperlink.


The delete_id variable's request is stored in the $id variable.


We apply a PDO select query with a WHERE clause condition using the database's $db object to choose a specific row id record in response to a delete hyperlink click.


The variable :id value in the select query is bound using the bindParam() function. This variable's value is stored in the $id variable. 


Our PDO select query is run by the execute() function.


According to PDO::FETCH ASSOC, the outcome must be returned as an array key. The column names in our table will be the same as the array keys. 


The variable $row has all of the keys allocated to it.


The actual PDO delete query with the WHERE clause condition is then applied using the $db object in the prepare() function.


The variable :id value in the select query is bound using the bindParam() function. This variable's value is stored in the $id variable.  The header() method then refers to the same index.php page when the select option record is erased.


Download Codes

No comments:

Post a Comment

Post Bottom Ad