Skip to main content

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:

Popular posts from this blog

Fix For Toshiba Satellite "RTC Battery is Low" Error (with Pictures)

RTC Battery is Low Error on a Toshiba Satellite laptop "RTC Battery is Low..." An error message flashing while you try to boot your laptop is enough to panic many people. But worry not! "RTC Battery" stands for Real-Time Clock battery which almost all laptops and PCs have on their motherboard to power the clock and sometimes to also keep the CMOS settings from getting erased while the system is switched off.  It is not uncommon for these batteries to last for years before requiring a replacement as the clock consumes very less power. And contrary to what some people tell you - they are not rechargeable or getting charged while your computer or laptop is running. In this article, we'll learn everything about RTC batteries and how to fix the error on your Toshiba Satellite laptop. What is an RTC Battery? RTC or CMOS batteries are small coin-shaped lithium batteries with a 3-volts output. Most laptops use

The Best Way(s) to Comment out PHP/HTML Code

PHP supports various styles of comments. Please check the following example: <?php // Single line comment code (); # Single line Comment code2 (); /* Multi Line comment code(); The code inside doesn't run */ // /* This doesn NOT start a multi-line comment block /* Multi line comment block The following line still ends the multi-line comment block //*/ The " # " comment style, though, is rarely used. Do note, in the example, that anything (even a multi-block comment /* ) after a " // " or " # " is a comment, and /* */ around any single-line comment overrides it. This information will come in handy when we learn about some neat tricks next. Comment out PHP Code Blocks Check the following code <?php //* Toggle line if ( 1 ) {      // } else {      // } //*/ //* Toggle line if ( 2 ) {      // } else {      // } //*/ Now see how easy it is to toggle a part of PHP code by just removing or adding a single " / " from th

Designing a Simple Order Form Application in PHP

Ok guys, for this post we’re going to create a simple yet complete order form page. Order forms are used on many sites to take customers order online. Order forms should have the capability to take orders from visitors regarding what items they want to purchase and store the information for further processing. For this post’s example, we are going to create an order form for a Book Seller. The form will be designed to take order of five different items (books). Our order form application should be able to take order of five different items in any separate quantities tht user wants, it should also ask for shipping address and name of the customer. It should then store the information provided in a file along with the date and time order was placed. The application should also be able to take any number of orders and store them all linearly for further human processing. For this, we need a front end of a HTML form to which the user would interact a