Ad

Wednesday, July 30, 2008

Performing Simple String Search Using PHP and MySQL

Performing Simple String Search Using PHP and MySQL

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();
?>

Performing Simple String Search Using PHP and MySQL

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:

6 comments:

  1. Learning Computer Programming :
    We found the information you post really useful, and we decided to make a review about it in our website.
    You can check it out at : http://www.mombu.com/programming/programming/t-mombu-recommends-learning-computer-programming-1679099.html#post6442101
    Our score is : 8.0

    ReplyDelete
  2. @mombu.com

    Great, Thank You!

    ReplyDelete
  3. Anonymous7:33 PM

    Thanks alot :) ,..........vik

    ReplyDelete
  4. Thank You..

    ReplyDelete
  5. Anonymous2:17 PM

    so many thz..;)

    ReplyDelete
  6. Anonymous2:53 AM

    Great work. Thank you.

    ReplyDelete

You are free to comment anything, although you can comment as 'Anonymous' it is strongly recommended that you supply your name. Thank You.

Please don't use abusive language.