PHP mysqli example – search MySQL database table using a function

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 ). 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 dont know PHP MySQL – visit our PHP MySQL programming online coaching classes

Say if you pass the Serial = 2, you will get the corresponding column value of Departments column = ARCHITECTURE, which is located in the same record in the database table, but in different columns. Don’t worry whole table structure shown below.

You should have SQL query knowledge to understand the following code . 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 Id (Serial column) to the function and I shall get the Department Name (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

Fields / Columns 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";
 }
 ?>