Here it is, a post after so long!
We see “Search” feature on almost all websites. Some employ third-party tools (like Google Custom Search) while others, mostly CMS based websites, have their own Search feature. The question now is, How do Search Feature Work? Answer is, it (almost always) uses MySQL (or database server’s capability. You all might be knowing that CMSs always store information in databases. So if we can search that we can very well search the whole site!
So today we’re going to employ the feature of MySQL to perform simple searching. As an example we’ll be creating an Online Phonebook to illustrate this.
MySQL’s Simple Search (String Matching) Query
select [coulumn1] from [table] where [column2] like ‘$var%’
(For some of the basic SQL queries refer to MySQL Commands)
we’ll take a simple example to illustrate the above query. Suppose we have a table with the following rows and columns:
| id | column-1 | column-2 |
| 1 | cat | there is a cat |
| 2 | dog | there is a dog |
| 3 | can | there is a cold drink can |
| 4 | campus | i like my college campus |
Now look closely what different queries will return:
1. select * from table where column-1 like
'ca%'
| id | column-1 | column-2 |
| 1 | cat | there is a cat |
| 3 | can | there is a cold drink can |
| 4 | campus | i like my college campus |
2. select * from table where column-1 like
'cam%'
| id | column-1 | column-2 |
| 4 | campus | i like my college campus |
3. select * from table where column-1 like
'd%'
| id | column-1 | column-2 |
| 2 | dog | there is a dog |
4. select * from table where column-2 like
'cat%'
| id | column-1 | column-2 |
| NULL | NULL | NULL |
5. select * from table where column-2 like
'there%'
| id | column-1 | column-2 |
| 1 | cat | there is a cat |
| 2 | dog | there is a dog |
| 3 | can | there is a cold drink can |
At this time it is worth noting that, while we can use this method to search for strings whose initial characters (words) are known but impossible to search for words within a string (like in query no. 4).
That’s it, all we need to perform simple search is the above mentioned query!
Below the source code for an Online Phone Book is given, it’d store phone numbers along with names. Its search feature will search names column, hence you can search for phone numbers by name.
<?php
/*Script: Onilne Phone Book with Search Facility
Date: 29-July-08
Copyright 2008 Arvind Gupta
http://learning-computer-programming.blogspot.com/
You are free to modify, change, publish or do whatever with this script
as long as this note is intact. Thank You!*/
//connect to MySQL
//provide your 'username' and 'pass'
$db=new mysqli('localhost','-USER-','-PASS-');
//if 'save' button was pressed
//user wants to store phone number
if(isset($_POST['save']))
{
$name=trim($_POST['name']);
$phno=trim($_POST['phno']);
//if data supplied are not empty
if(!$name=='' || !$phno=='')
{
//if this is the first time
//and database is not craeted
if(!$db->select_db('one'))
//create the database
$db->query('create database one');
//select the databasw to work with
$db->select_db('one');
//if table is not craeted, craete it
if(!$db->query('select * from phno'))
$db->query('create table phno(id int auto_increment primary key, name varchar(50), phnum varchar(20))');
//ready to insert data
$db->query("insert into phno (name, phnum) values ('$name', '$phno')");
}
}
//show the form
?>
<html>
<head>
<title>My Phone Book</title>
</head>
<body>
<h1>My Phone Book</h1>
<h2 style="background: #000; color: #fff;">Store New Phone Number</h2>
<form name="form2" method="get" action="">
<p style="background: #000; color: #fff;"><b>Search:</b>
<input name="search" type="text" id="search">
<input name="searchb" type="submit" id="searchb" value="Search">
</p>
</form>
<p></p>
<form name="form1" id="form1" method="post" action="">
<table width="250" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="83">Name</td>
<td width="417"><input name="name" type="text" id="name" /></td>
</tr>
<tr>
<td>Ph. No.</td>
<td><input name="phno" type="text" id="phno" value=""></td>
</tr>
<tr>
<td><input name="save" type="submit" id="save" value="Save" /></td>
<td><input type="reset" name="Submit2" value="Reset" /></td>
</tr>
</table>
</form>
<h2 style="background: #000; color: #fff;">Previously Stored</h2>
<p>ORDER BY: <a href="?order=new">newest first </a>| <a href="?order=old">oldest
first</a> | <a href="?order=az">a-z</a> | <a href="?order=za">z-a</a></p>
</body>
</html>
<?php
//----DISPALY PREVIOUSLY STORED PH. NUMBERS----
//create the SQL query as per the action
//if any ordering is selected
$order=$_GET['order'];
if($order=='new')
$query="select * from phno order by id desc";
elseif($order=='old')
$query="select * from phno order by id asc";
elseif($order=='az')
$query="select * from phno order by name asc";
elseif($order=='za')
$query="select * from phno order by name desc";
//or if user is searching
elseif(isset($_GET['searchb']))
{
$search=$_GET['search'];
$query="select * from phno where name like '$search%'";
}
else
//use the default query
$query="select * from phno";
//if database does not exits
//first time operation
if(!$db->select_db('one'))
{
echo "<p><i>NONE</i></p>";
exit;
}
//else
//do the query
$result=$db->query($query);
//find number of rows
$num_rows=$result->num_rows;
//if no rows present probably when
//searching
if($num_rows<=0)
echo "<p><i>No Match Found!</i></p>";
//process all the rows one-by-one
for($i=0;$i<$num_rows;$i++)
{
//fetch one row
$row=$result->fetch_row();
//print the values
echo "<p><span style=\"font-size: 200%;\">$row[1]: </span> $row[2]</p>";
}
//close MySQL connection
$db->close();
?>
It might now be obvious that we took this phonebook example due to the limitation of “like” query to search for words within a string. That’s not to say it’s not possible. In fact searching within string, even longer ones is very easy and efficient using MySQL’s built-in capability. But we’ll discuss that in some future posts.
Previous Posts: