PHP Fill ComboBox from Database

      Comments Off on PHP Fill ComboBox from Database

PHP Fill ComboBox from Database

php fill combobox from database

PHP Fill ComboBox from Database is most frequent used functionality in  every programming language. Here I would like to explain with one simple example. Let’s Start!

Did you know, How to Start Programming with Algorithm and Flowchart?

Basically we are storing data into different tables with their id and in one or more table we use reference of their id this functionality is called as normalization. Here i have prepared 3 tables named as student, subject, enrolment.

Student table is used to store student id, name and other details about student. While stubject table consist of subject id and subject name. Enrolment table consist of enrollment id, student id and subject id. (here student id and enrollment id used instead of student name and subject name because i would like to explain normalization also.

Note: This article is prepared for beginners only, This version/coding pattern can not be used directly while developing live website.

Database Backup (SQL)

--
-- Table structure for table `enrolment`
--

CREATE TABLE IF NOT EXISTS `enrolment` (
`eid` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `subid` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


INSERT INTO `enrolment` (`eid`, `sid`, `subid`) VALUES
(1, 1, 1);



CREATE TABLE IF NOT EXISTS `student` (
`sid` int(11) NOT NULL,
  `sname` varchar(20) NOT NULL,
  `smobile` varchar(10) NOT NULL,
  `sgender` varchar(1) NOT NULL,
  `scity` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


INSERT INTO `student` (`sid`, `sname`, `smobile`, `sgender`, `scity`) VALUES
(1, 'Ram', '1234567890', 'M', 'Vadodara'),
(2, 'Sita', '2323232', 'F', 'Mumbai');


CREATE TABLE IF NOT EXISTS `subject` (
`subid` int(11) NOT NULL,
  `subname` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


INSERT INTO `subject` (`subid`, `subname`) VALUES
(1, 'WPOS'),
(2, 'Mobile Computing');


ALTER TABLE `enrolment`
 ADD PRIMARY KEY (`eid`);


ALTER TABLE `student`
 ADD PRIMARY KEY (`sid`);

ALTER TABLE `subject`
 ADD PRIMARY KEY (`subid`);


ALTER TABLE `enrolment`
MODIFY `eid` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;

ALTER TABLE `student`
MODIFY `sid` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;

ALTER TABLE `subject`
MODIFY `subid` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;

File : index.php

<?php session_start(); ?>

<html>
	<head>
		<title>	php fill combobox from database</title>
	</head>
	<body bgcolor="#E0E0E0">
	
	<h2>Welcome to php tutorial on php fill combobox from database</h2>
	
	<br><br>

<form method="post" action="index2.php">	
<?php
//============= Variables for Database ===================
$hostname = "localhost";
$username = "root";
$password = "";
$database = "mydb";
//========================================================

//Connection...
$link = mysql_connect($hostname, $username, $password);

//Set Database
mysql_select_db($database,$link);


$query = "select * from student";
$result = mysql_query($query);	

echo "Select Student : ";
echo "<select name=\"studentid\">";
 while($row=mysql_fetch_array($result))
 { 
       echo "<option value='".$row['sid']."'>".$row['sname']."</option>";
 }
 echo "</select>";
 echo "<br><br>";
 
 
$query = "select * from subject";
$result = mysql_query($query);	

echo "Select Subject : ";
echo "<select name=\"subjectid\">";
 while($row=mysql_fetch_array($result))
 { 
       echo "<option value='".$row['subid']."'>".$row['subname']."</option>";
 }
 echo "</select>";
 ?>
 <br>
 
 <input type="submit" value="Enrolment">
 </form>
  
 <?php
$query = "select enrolment.eid as a, student.sname as b,subject.subname as c from student,subject,enrolment where enrolment.sid = student.sid and enrolment.subid = subject.subid";
$result = mysql_query($query);

echo "<br><br>";
echo "<table border=\"1\">";
echo "<tr><td>Enrollment id</td><td>Student Name </td><td>Enrolled in Subject </td><td>Remove Assignment </td> </tr>";

while($row=mysql_fetch_array($result))
{
	$eid=$row['a'];
	$sname=$row['b'];
	$subname=$row['c'];
	echo "<tr><td>$eid</td><td>$sname</td><td>$subname</td><td> <a href=\"remove.php?id=$eid\" onclick=\"return confirm('Are you sure?');\"> Remove </a></td></tr>";
}
echo "</table>";
?>
 
<?php
if(isset($_SESSION['msg']))
{
	//echo ""$_SESSION['msg'];
echo "<script language=\"JavaScript\">alert(\"".$_SESSION["msg"]."\");</script>";
unset($_SESSION['msg']);
}
?>

 

File: index2.php

<?php session_start(); 

$sid = $_POST['studentid'];
$subid = $_POST['subjectid'];


//echo $sid.$subid;
//return;


//============= Variables for Database ===================
$hostname = "localhost";
$username = "root";
$password = "";
$database = "mydb";
//========================================================

//Connection...
$link = mysql_connect($hostname, $username, $password);

//Set Database
mysql_select_db($database,$link);


$query = "insert into enrolment values (NULL,$sid,$subid)";
$result = mysql_query($query);	

$_SESSION["msg"]="Student Enrolled Successfully";
header('Location: index.php');

?>

File: remove.php

<?php session_start(); 

$eid = $_GET['id'];

//============= Variables for Database ===================
$hostname = "localhost";
$username = "root";
$password = "";
$database = "mydb";
//========================================================

//Connection...
$link = mysql_connect($hostname, $username, $password);

//Set Database
mysql_select_db($database,$link);


$query = "delete from enrolment where eid=$eid";
$result = mysql_query($query);	

$_SESSION["msg"]="Student Removed Successfully";
header('Location: index.php');

?>

 

Screenshot 1

php fill combobox from database

Screenshot 2

php fill combobox from database

Screenshot 3

php fill combobox from database

Screenshot 4

php fill combobox from database

Hope you have understand the concept of how to fetch data from mysql database and store into combobox using PHP programming language. If you are having any doubts you can contact me using contact us form, I will reply back surely.

PS:
If you like my article, Please share with your friends on Facebook, WhatsApp, Twitter, etc. Thanks in Advance.