In the following PHP program I have accessed the MySQL database (using PHP’s object oriented mysqli extension ) by a PHP function generated by me, and fetched a “MySQL database table” column value (Departments column) based on some key value in another column ( Serial ). So in a nutshell this program shows how – based on “primary key column value” of a “particular row” of a “single database table”, pick up “another column value” of that row in the same table.
To get the required value from database you have to just call a function like this : function getDepartmentName( $dbTableName, “Serial”, 2, “Departments”) where Serial and Departments are two columns of same database table, and 2 is the value in the Serial column in a record. If you don’t know PHP MySQL – visit PHP courses in Kolkata, Online PHP Tutoring
In the following table the column named Serial is the primary key
Serial | Departments | Category | Educational_category |
1 | ADULT CONTINUING EDUCATION CEN | Department | Engineering |
2 | ARCHITECTURE | Department | Engineering |
3 | BENGALI | Department | Arts |
4 | CENTRAL LIBRARY | Department | Library |
5 | CHEMICAL ENGINEERING | Department | Engineering |
Say if you pass the Serial = 2 to the function, it will return the value ARCHITECTURE, which will be found in the same row of Serial (2) the corresponding column named Departments in the above table, which is located in the same record. Here the Serial column is the Departmental ID and Departments indicates “Department Name” in Words
Prerequisite to understand this program and source code
- You should have SQL knowledge to understand the following code .
- You should have basic knowledge of programming with functions.
- It is recommened that you should know PHP because all codes in PHP and MySQL.
To get it done you have to call the function like this mentioning column “Serial”, database table name $dbTableName & value in Serial column which is 2 here and $returnColumnName = Departments here
View Live Example
My target is : I shall pass the department Serial to the function and the function will return the Department Name (in Departments column) as shown below. The table header Serial is the primary key of the following table. This is only PHP MySQL version. I have shown a AJAX version in a separate tutorial.
I have done this in practical situation using jQuery AJAX & PHP MySQL. So when you choose a department using the Drop Down departments list (select element) then retrieved value shown below instantly without refreshing. The AJAX code is not shown here. Its pure HTML and PHP code to make it simple.
Serial | Departments | Category | Educational_category |
1 | ADULT CONTINUING EDUCATION CEN | Department | Engineering |
2 | ARCHITECTURE | Department | Engineering |
3 | BENGALI | Department | Arts |
4 | CENTRAL LIBRARY | Department | Library |
5 | CHEMICAL ENGINEERING | Department | Engineering |
Here I have built a database using the departments of Jadavpur University in Kolkata, India. The database table structure has been shown below.
I have done the whole thins using jQuery AJAX & PHP MySQL. But here for simplicity I have shown only the PHP based version. Because jQuery & AJAX code could be little bit complicated for some users..
So build the basic database to work.
My target is to know the “Departments” field value, (which shows the departments name) based on Department Serial field
The table name is : ju_faculty_departments
The Fields ( Columns) of the above table are :
Serial | Departments | Category | Educational_category
CREATE TABLE `ju_faculty_departments` ( `Serial` mediumint(255) DEFAULT NULL, `Departments` varchar(255) DEFAULT NULL, `Category` varchar(255) DEFAULT NULL, `Educational_category` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `ju_faculty_departments` (`Serial`, `Departments`, `Category`, `Educational_category`) VALUES (1, 'ADULT CONTINUING EDUCATION CEN', 'Department', 'Engineering'), (2, 'ARCHITECTURE', 'Department', 'Engineering'), (3, 'BENGALI', 'Department', 'Arts'), (4, 'CENTRAL LIBRARY', 'Department', 'Library');
PHP Code
I have built a PHP function which is a generic function and takes four parameters. You need to pass the database table name, search column name ($dbSearchColumnName) of table (which is a primary key), the value of key and based on the key value ($dbSearchColumnValue) you need to get the value stored in another column ($returnColumnName) of a record.
The record is selected by SQL engine using the key value in primary key column ($dbSearchColumnName)
function getDepartmentName( $dbTableName, $dbSearchColumnName, $dbSearchColumnValue, $returnColumnName )
THE HTML Form shown here: give it a name someform.html
<form method="GET" action="getDeptName.php"> <select id="department" name="department"> <option value='0'>Select your department</option> <option value='1'>ADULT CONTINUING EDUCATION CEN</option> <option value='2'>ARCHITECTURE</option> <option value='3'>BENGALI</option> </select> <button id="submitBtn" type="submit">Submit</button> </form>
View Live Example
So when you submit the form the value is the option field is passed to the getDeptName.php file. PHP script takes the Department’s serial no from the HTML form and returns corresponding Department’s name in string format
getDeptName.php file code shown below-
<?php require_once("dbconnect.php"); if( isset($_GET) ) { if( isset( $_GET["department"] ) && ( $_GET["department"] !== 0 )) { $id = $_GET["department"]; echo "<br />Received Department (Id / Serial) from HTML Select drop down element : ".$id."<p>"; //Call the database search function $deptName = getDepartmentName("ju_faculty_departments","Serial",$id,"Departments"); echo "Retrieved Department Name from Database : ".$deptName; } elseif( $_GET["department"] === 0 ) { echo "Please choose your department first."; } else echo "Departmental ID not found.."; return; } else { echo "GET is not set"; } // The value of return column will be returned function getDepartmentName($dbTableName, $dbSearchColumnName, $dbSearchColumnValue, $returnColumnName) { global $connect; $type = gettype($dbSearchColumnValue); if(($type == "integer") || ($type == "double")) { $query = "select $returnColumnName from $dbTableName where $dbSearchColumnName = $dbSearchColumnValue"; } else { $query = "select $returnColumnName from $dbTableName where $dbSearchColumnName = '$dbSearchColumnValue'"; } //echo $query; $result = $connect->query($query); if($result->num_rows == 1) { $myrow = $result->fetch_array(MYSQLI_ASSOC); return $myrow[$returnColumnName]; } else return "error in MySQL query"; } ?>
The database connection script to connect MySQL using PHP
dbconnect.php
<?php $localhost = "localhost"; $username = "user"; $password = "pass"; $dbname = "dbname"; // db connection $connect = new mysqli($localhost, $username, $password, $dbname); // check connection if($connect->connect_error) { die("Connection Failed : " . $connect->connect_error); } else { // echo "Successfully connected"; } ?>
Few Other Courses
- Click the link if you want to learn JavaScript and JQuery programming courses
- Python programming courses
- Website Designing Courses