Categories
PHP Tutorials

PHP mysqli code example to search MySQL database

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