How to Pass Dropdown Selected Value to Database in PHP

In this tutorial, I will show you how to pass or insert the dropdown selected value to the database in PHP and I have also covered the update and delete operation of the drop-down list.

Here, I have created a form with select option values with country names and perform CRUD (Create, Read, Update, Delete) operations using PHP and MySQL database. Once you learn it, you will easily customize it according to your project requirements.

how to pass dropdown selected value to database in php

Table Content

1. Make a Database and Table

2. Project Directory Structure

3. Establish Database Connection

4. Display Data from the Database

5. Insert New Records

5.1 PHP Insert Code with PDO

6. Update Record

6.1 PHP Update Code With PDO

7. Delete Record

7.1 PHP Delete Code with PDO

Make a Database and Table

In my PhpMyAdmin I have created "php_dropdown_crud_db" named database and within this database I made the person table.

CREATE DATABASE IF NOT EXISTS php_dropdown_crud_db;

CREATE TABLE tbl_person (
    id int(11),
    name varchar(100),
    country_name varchar(100)
    
);

Project Directory Structure

Check out my project directory I have set up in the xampp/htdocs folder.

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

I have created the following PHP four files to perform the drop-down list CRUD (Create, Read, Update, delete) operation.

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 update the selected value from the dropdown using this file.

Establish Database Connection

This is the database connection file called "dbconfig.php" and within this, I have set up the database connection using the PDO extension.

<?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, I have retrieved select option values from the database.

<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 and in this file, I provided the user interface for adding new select option records to the database.

In this file, I have made a simple HTML form with two fields the first field is name and the second is drop-down options.

<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:

PHP Insert Code with PDO

I have created this PHP insert code Above the <!DOCTYPE html> tag in the add.php file.

This PHP insert code runs when we select the option value and click the insert button.

If there is no error caused then this PHP code will insert a new record of drop-down value in the database

<?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();
		}
	}
}

?>

Update Record

In the previous section, I discussed how to add new records with select option values to the database.

In this section, I will show you how to modify an existing record of select options in the database table.

I have retrieved the table ID in the edit hyperlink because this ID helps us to update the selected drop-down option value in the database.

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

Here,

This is the edit.php file and within this file, I made the editing form to update the selected option value.

<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:

PHP Update Code With PDO

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

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

This PHP update operation leaves the selected option record intact if we don'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();
		}	
	}	
}
?>

Delete Record

In the previous section, I discussed how to update elect option values in the database.

In this section, I will show you how to delete an existing record of select options in the database table.

I have retrieved the table ID in the delete hyperlink because this ID helps us to delete the existing select option records from the database.

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

PHP Delete Code with PDO

This PHP delete code I created in the index.php file separately.

If we click on the delete hyperlink then the selected drop-down list will deleted by following the 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");
}
	
?>

Wednesday 29 May 2024

How to Pass Dropdown Selected Value to Database in PHP

Table Content

Make a Database and Table

Project Directory Structure

Establish Database Connection

Display Data from the Database

Insert New Records

PHP Insert Code with PDO

Update Record

PHP Update Code With PDO

Delete Record

PHP Delete Code with PDO

Learn More:

No comments:

Post a Comment

Post Bottom Ad

Subscribe

Get new tutorials by email:

Like on Facebook

Tags

Labels

Popular posts

Contact Form

Connect With Us

Contact Form

Pages