Last updated on July 31st, 2016 at 11:16 am

MySQl is very powerful language when we are working with database. We mainly use MySql to perform CRUD (Create, Read, Update and Delete ) operation but we can do more with MySql. In this post i will explain you how to make search in database from A – Z and how we can find first character or any name from database using MySql and PHP.
MySqlPhp

Demo

Connection to database using PHP

PHP is very simple and easy server side language to learn. We can make connection to database using php with simple connection function.

<?php
$conn = mysql_connect("localhost", "username", "password");
?>

In above function we need to define three parameters first parameter is to define your host , Second  parameter is for Username and last parameter is for password. After creating connection we need to select database

$db = mysql_select_db("database_name", $conn);

Now connection and database is selected. Create table inside database and insert data or sample data given below.

CREATE TABLE IF NOT EXISTS `demo_sorting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3210 ;

INSERT INTO `demo_sorting` (`id`, `name`) VALUES
(1, 'Abhinav'),
(2, 'AJAY'),
(3, 'Aman'),
(4, 'AMIT'),
(5, 'Abhilash'),
(6, 'Amit sainiki'),
(7, 'Car'),
(8, 'Digvijay'),
(9, 'Eshan'),
(10, 'Gaurav impin'),
(11, 'Gaurav saini'),
(12, 'Himanshu ide'),
(13, 'Impinge'),
(14, 'Jaggi imping'),
(15, 'Ishan'),
(16, 'Kashmir impi'),
(17, 'Sachin Two'),
(18, 'Lucky idea'),
(19, 'Manoj'),
(20, 'Manu'),
(21, 'Manurohit'),
(22, 'MOHIT'),
(23, 'Pc Sainiki'),
(24, 'Neeraj Imp'),
(25, 'Ashu'),
(26, 'UmangImp'),
(27, 'ishfaq'),
(28, 'ishfaq'),
(29, 'sana fatma'),
(30, 'sahil'),
(31, 'verma_aarti'),
(32, 'pushp2379'),
(33, 'zack2386'),
(34, 'xiograos'),
(35, 'maria l. castellon'),
(36, 'sanjitasem'),
(37, 'raviraj2519'),
(38, 'Jaswinder Singh'),
(39, 'arun crazyforweb'),
(40, 'stan_tsao'),
(41, 'Nitin (Sanyam)'),
(42, 'deepak ismoip'),
(43, 'sunil k. jindal'),
(44, 'chethan vallala'),
(45, 'Priya'),
(46, 'Aneh Thakur'),
(47, 'Nancy Thakkar'),
(48, 'Manjit'),
(49, 'Balbir'),
(50, 'Roti'),
(51, 'neha'),
(52, 'ajay ');

Now we need to create a list from A – Z so user click on any alphabet and we make search in table for result.

$list = range('A', 'Z');
foreach($list as $value){
	echo "<a href='?r=".$value."'>".$value."</a>&nbsp;&nbsp;|&nbsp;&nbsp;";
}

Search in table for result

if(isset($_GET['r'])){
	$r = mysql_real_escape_string($_GET['r']);
	$sql = "SELECT `name` FROM demo_sorting where `name` LIKE '$r%'";
	$qur = mysql_query($sql);
	if(mysql_num_rows($qur) > 0){
		while($res = mysql_fetch_array($qur)){
			echo $res['name'].'<br>';
		}
	}
	else{
		echo "No match found";
	}
}

 Complete Code

<?php
$conn = mysql_connect("localhost", "root", "");
$db = mysql_select_db("trinity", $conn) or die(mysql_error());

// A to J search
$list = range('A', 'Z');
foreach($list as $value){
	echo "<a href='?r=".$value."'>".$value."</a>&nbsp;&nbsp;|&nbsp;&nbsp;";
}
echo '<br>';
if(isset($_GET['r'])){
	$r = mysql_real_escape_string($_GET['r']);
	$sql = "SELECT `name` FROM demo_sorting where `name` LIKE '$r%'";
	$qur = mysql_query($sql);
	if(mysql_num_rows($qur) > 0){
		while($res = mysql_fetch_array($qur)){
			echo $res['name'].'<br>';
		}
	}
	else{
		echo "No match found";
	}
}
?>

Display all first character of name using MySql

<?php
$conn = mysql_connect("localhost", "root", "");
$db = mysql_select_db("trinity", $conn) or die(mysql_error());
$sql = "SELECT `name` , LEFT( `name` , 1 ) AS first_char FROM demo_sorting WHERE UPPER( `name` ) BETWEEN 'A' AND 'Z' OR `name`  BETWEEN '0' AND '9' ORDER BY `name`";
$qur = mysql_query($sql) or die(mysql_error());
echo "<pre>";
while($row = mysql_fetch_array($qur)){
	echo $row['name']. '  '.$row['first_char'].'<br>';
}

?>