Create a Simple Application to Search a User Name from Database in AJAX


Hi,

Many of us does not know how to use AJAX and roaming in the Internet for one sample application from which we can start building our knowledge in AJAX (Asynchronous JavaScript and XML).

 

I have a small web application whose backend database is in MySQL and the Server side scripting language is PHP (Hypertext Pre Processor).

 

First we should why we need AJAX in our application. We were pretty happy with our own standard applications where we use POST or GET method to go to database and Fetch data and display it in the Page.

 

In the earlier cases if we want a small database access then also we need to POST the form with variables and then execute the Database query and then fetch and Display data back in the Browser.

 

Suppose we want to validate a user name, i.e we want to check if the user name already exists or not before inserting the user details in the database.

 

In this case we need 2 server accesses.

 

  1. First time send data to server to check the user name from database.
  2. GET Data back to the Client with the details such as the name already exists or Name does not exist.
  3. Again send the full form in the server with the complete user details to insert the user in the database.

 

So, if our form is pretty long and contains images and JavaScripts other stuffs then these elements will load again along with the form. This will hamper the performance of the site.

 

So we can use AJAX here to minimize the load time and Access time.

 

As we know AJAX is not a new coding language. It is a Methodology. This methodology uses XMLHTTP Request to send data to server and get back the results with JavaScript.

 

Now we will create a JavaScript document named ServerReq.js, which will contain all the utility functions that we can use through out the application.

 

/*

* File: ServerReq.js

*This file creates the instance of xmlHttpRequest object, sends and

* Receives the request and response respectively.

*/

/*

* Static script to instantiate XMLHttp object for different browsers.

*/

var clientHttpHandler;

clientHttpHandler = create();

 

/*

* This method creates the xmlHttpRequest object and returns it.

*/

function create()         

{

            var xmlHttpRequest = false;

 

            //Internet Explorer

            try

            {

                        xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);

            }

            catch (xml2Exception)

            {

                        try

                        {

                                    xmlHttpRequest = new ActiveXObject(“Microsoft.XMLHTTP”);

                        }

                        catch (xmlException)

                        {

                                    xmlHttpRequest = false;

                        }

            }

 

            //Netscape, Mozila, Firefox, Safari, Opera

            if (!xmlHttpRequest && typeof XMLHTTPRequest == ‘undefined’)

            {

                        try

                        {

                                    xmlHttpRequest = new XMLHttpRequest();

                        }

                        catch (genException)

                        {

                                    XMLHttpRequest = false;

                        }

            }

            return xmlHttpRequest;

}

/*

* This method sends the request to the server url according to the

* Passing parameters. It sets the user’s response function to the

* onreadystatechange event of the xmlHttpRequest object.

*/

function send(httpMethod, serverUrl, isAsync, respFunc)

{

            clientHttpHandler.open(httpMethod, serverUrl, isAsync);

            clientHttpHandler.onreadystatechange = respFunc;

            clientHttpHandler.send(null);

}

/*

* This method checks the state and the status of the response and

* Depending on that fetches the response text.

* readystate: 0 – uninitialized, 1 – loading, 2 – loaded, 3 – interactive, 4 – complete

*/

function receive()

{

            //var response = “”;

            try

            {

                        if (clientHttpHandler.readyState == 4) // Completed

                        {

                                    if (clientHttpHandler.status == 200) // “OK”

                                    {

                                                return true;

                                    }

                                    else if (clientHttpHandler.status == 403) // “Forbidden”

                                    {

                                                alert(“Error: Access denied. Please check the permissions!”);

                                    }

                                    else if (clientHttpHandler.status == 404) // “URL Not Found”

                                    {

                                                alert(“Error: URL not found. Please check the URL!”);

                                    }

                                    else // Miscellaneous

                                    {

                                                alert(“Error: status code ” + clientHttpHandler.status);

                                    }

                        }

                        return false;

            }

            catch (genException)

            {

            }

}

 

 

In this function we have created an XMLHttpRequest object depending on the type of browser.

Then we have used the OnReadyStateChange event of the Object that will check if the Request has been completed or not.

 

This event has 4 ready states:

 

State

Description

0

The request is not initialized

1

The request has been set up

2

The request has been sent

3

The request is in process

4

The request is complete

 

 

So each time the ready state changes the function receive() will be executed.

But we will look for the ready state 4 as in this state the request is marked as completed. SO whatever request we have sent that is being completed and we can now access the response data now.

 

Now we will create a Function that will use the above script to send, parse and display data to the webpage from where we have requested it.

 

/*

* File: SendAjaxRequest.js

This function we will call from our webpage with the values that

Needs to be sent to the server for processing.

*/

 

function SendReqSearchRequest(userName){

         /*

          We will append a random number each time we need to

          Send the request. So that browsers does not cache the request

          And every time a fresh page will be executed.

         */

        var rand = Math.floor(Math.random()*1000001);

             

        // The URL where our background page resides

 

         sURL = “../utility/searchuser.php”; // I have used a relative path

           

            /*

         I have here used the GET method to send data to the background

         Page so builds the query string here.

         */

         queryString = “?rand=” + rand + “&username=” + userName;

           

         // Append the query string at the end of the URL

 

            sURL += “?” + queryString;

                       

            //Attach a function which will take care of the data when it comes back

 

         clientFunc = populateSearchResultData;

 

         // If this request is a synchronous or not.

         // If we use multiple synchronous requests at a time then only the

        // Last request will be executed.

 

            isSync = true;

    

        // Now send the request.

 

            send(“GET”, sURL, isSync, clientFunc);

           

}

 

/*

* This method populates the returrn data

*/

function populateSearchResultData ()

{

            var opStr = “”;

 

            if (receive())

            {

                         response = clientHttpHandler.responseText;

                       

                         if(response == “NF”){

                                    document.getElementById(“ResultDiv”).innerHTML = “User name OK”;

                         }else{

                                  document.getElementById(“ResultDiv”).innerHTML = “User name Already in Databse<br/> Choose a different one.”;

 

                   }  

            }

}

 

 

Now we will create our background page searchuser.php, which will search the user name from the data we pass to it.

 

<?php

 

//1. Conenct to Databae Here

 

//2. Select the Database

 

//Check if the user name variable is set or not as without it we cannot search

 

 

if(isset($_REQUEST[‘username’])){

 

            // Build the query string

            $name = $_REQUEST[‘username’];

 

            $query = “SELECT user_name FROM tbl_user WHERE user_name = ‘{$name}'”;

 

            // Now fire the query

 

            $result = mysql_query($query);

 

            if(mysql_num_rows($result) > 0){

                        //User found

 

                        echo “OK”;

            }else{

 

                        //User NOT found

 

                        echo “NF”;

            }

}else{

 

            echo “ERROR”;

}

 

?>

 

 

Now this page will search the user name in the background and outputs specified string, which will send back the Client to process as per requirement.

 

 

And now we require one more page before we proceed to Test.

 

 

This page is the main page of our application from where user creation is done. I have used only one element to demonstrate the AJAX technology; you can use it anywhere you like.

 

Name of the file: AddUser.html

 

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN”>

<HTML>

<HEAD>

<TITLE> AJAX Test Page </TITLE>

<META NAME=”Generator” CONTENT=”EditPlus”>

<META NAME=”Author” CONTENT=””>

<META NAME=”Keywords” CONTENT=””>

<META NAME=”Description” CONTENT=””>

<script type=”text/javascript” src=”js/ServerReq.js”></script>

<script type=”text/javascript” src=”js/SendAjaxRequest.js”></script>

<script type=”text/javascript”>

function checkUserName(){

            user =  document.getElementById(“username”);

          if(null == user or user.value == “”){

            alert(“Please enter a User Name to check “);

         }

            SendReqSearchRequest(user);

}

</script>

 

</HEAD>

 

<BODY>

<form method=”POST”>

User Name : <input type=”text” name=”username” id=”username”/>

<a href=”#” onlcick=”javascript:checkUserName()”> check user name </a><br/>

<div id=”ResultDiv”></div><br/>

Password : <input type=”password” name=”pass” id=”pass” /> <br/>

Confirm Password : <input type=”password” name=”confpass” id=”confpass” /> <br/>

 

<input type=”submit” name=”submit” value=”Add user”/>

 

</form>

 

</BODY>

</HTML>

 

Here if the users click on the “check user name” link, the function will be called and JavaScript will send the request to the server in the background. In the mean time users can proceed filling the form. This form will be ready.

 

When the response data comes back from the server it will be displayed in the Div.

 

Also, Do not forget to add this JavaScripts at the TOP:

 

<script type=”text/javascript” src=”js/ServerReq.js“></script>

<script type=”text/javascript” src=”js/SendAjaxRequest.js“></script>

 

You can note from these above lines that the Scripts are located in the js folder under the root directory.

 

Important: As ServerReq.js holds the necessary functions to create and use the XMLHTTPRequest object then you always need to include the script ServerReq.js before using the Object.

 

HappY AjAxing 😉

 

How to create a search functionality with php mysql


Note: I wrote this post first on October , 2007 , nearly 3 years back and since then this post got a lot of audiences. Surely this topic is one of the good ones for the newbies. After reading the responses for this post I saw that users are facing some errors and after a long while I got some time to have a second look. So I have again installed PHP, APACHE, MYSQL and MYSQL WorkBench in my local machine and tested the script like a user. I have found some new bugs and made the modifications accordingly. Now the Script is completely new and tested fully.

To simplify I tried to use some out of box functionality so that one can run the example right away.

For the sample database I have used “SAKILA” database from MySQL. I have only used the “Customer” table for the example, but this can be extended to a bigger example. Hope you’ll find this useful.

First: Install the SAKILA Database to your local MySQL instance. You can download it from here. We will be using the CUSTOMER table. Here is the table for your reference:

CREATE TABLE customer (customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,store_id TINYINT UNSIGNED NOT NULL,

first_name VARCHAR(45) NOT NULL,

last_name VARCHAR(45) NOT NULL,

email VARCHAR(50) DEFAULT NULL,

address_id SMALLINT UNSIGNED NOT NULL,

active BOOLEAN NOT NULL DEFAULT TRUE,

create_date DATETIME NOT NULL,

last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (customer_id),

KEY idx_fk_store_id (store_id),

KEY idx_fk_address_id (address_id),

KEY idx_last_name (last_name),

CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,

CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

Second: This table already have a lot of data. You can verify it by the simple select command.

Initially it has nearly 599 rows. A very good amount of data to test our code.

In this test we will use the CUSTOMER table and we’ll try to search a particular name from the three columns ‘First Name’, ‘Last Name’ and ‘Email’ and if found we’ll display the data. There are 2 ways to do it. I’ll first explain the easiest way and then a bit more advanced version of it. Both the scripts are tested by me using SAKILA database of MySQL and they are working fine. In case of any problem don’t forget to send me your queries.

So, lets get started.

In the first Example we’ll use a 2 page solution. The first page is the search page which is in plain HTML and will contain a Search box and a Submit button and it will post the data by GET method to the next page called ‘Search.php’

In the first Page, just after the <body> tag of your page, place the following HTML. This is for the form which will contain the textfield to enter our search string in.
<form name=”form” action=”search.php” method=”get”>
<input type=”text” name=”q” />
<input type=”submit” name=”Submit” value=”Search” />
</form>


Now, create a new page called ‘search.php’ and add the following into it. Follow the PHP comments for what the script is doing, if you get stuck, don’t forget to send me your queries!

<?php  // Get the search variable from URL
if(!isset($_GET[‘q’]))
die(“Search Query not found”);$var = $_GET[‘q’];
$trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10;

// check for an empty string and display a message.
if ($trimmed == “”){
echo “<p>Please enter a search…</p>”;
exit;
}

// check for a search parameter
if (!isset($var)){
echo “<p>We dont seem to have a search parameter!</p>”;
exit;
}

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect(“localhost”,”username”,”password”);

//specify database ** EDIT REQUIRED HERE **
mysql_select_db(“sakila”) or die(“Unable to select database”);
// Build SQL Query
$query = “select * from customer where first_name like \”%$trimmed%\” or last_name like \”%$trimmed%\” or email like \”%$trimmed%\” order by first_name DESC”;

// EDIT the above SQL query and specify your table and field names

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternativethis is optional

if ($numrows == 0)
{
echo “<h4>Results</h4>”;
echo “<p>Sorry, your search: $trimmed returned zero results</p>”;

// google
echo “<p><a href=\”http://www.google.com/search?q=&#8221;
. $trimmed . “\” target=\”_blank\” title=\”Look up
” . $trimmed . ” on Google\”>Click here</a> to try the
search on google</p>”;
}

// next determine if s has been passed to script, if not use ZERO (0) to Limit the output
if (empty($s)) {
$s=0;
}

// get results
$query .= ” limit $s,$limit”;
$result = mysql_query($query) or die(“Couldn’t execute query”);

// display what the person searched for
echo “<p>You searched for: $var </p>”;

// begin to show results set
echo “Results: <br/>”;
$count = 1 + $s ;

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$f_name = $row[“first_name”];
$l_name = $row[“last_name”];
$email = $row[“email”];

echo “$count.> $f_name $l_name $email<br/>” ;
$count++ ;
}

$currPage = (($s/$limit) + 1);

//break before paging
echo “<br />”;

// next we need to do the links to other results
if ($s>=1) {

// bypass PREV link if s is 0
$prevs=($s-$limit);
print ” <a href=\”$PHP_SELF?s=$prevs&q=$var\”><<
Prev 10</a>  “;
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

// not last page so give NEXT link
$news=$s+$limit;

echo ” <a href=\”$PHP_SELF?s=$news&q=$var\”>Next 10 >></a>”;
}

$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo “<p>Showing results $b to $a of $numrows</p>”;

?>

Three major areas are covered in this script, the first is selecting data from the database which matches your entered keyword, the second is displaying the results on the web page and the last is generating the paging, which displays results in chunks of 10 with next/previous links where they are necessary.
Important: This script requires numerous edits before it can be adapted for use. The SQL query in this example is only selecting 1 field from a hypothetical database for the training purpose. Further down the script, the value of that field is being displayed on the page. The major amendments that need to be performed are 1.) The SQL statement, and 2.) The PHP which displays the results.

Lets Look at another example which is a bit complex than the previous one. It uses some PHP inbuilt functions which might be new for you. So I have added some documentation for you:

  • trim() –  Strip whitespace (or other characters) from the beginning and end of a string
  • explode() –  Split a string by string
  • array_unique() –   Removes duplicate values from an array
  • preg_replace() –  Perform a regular expression search and replace

Other functions used include common database access functions like mysql_connect,mysql_num_rows, etc. Please see code comments for more detailed explanation of the script. If you have questions or suggestions please use the comment box on the bottom of the page.

Open your favorite editor and create search.php file as shown below.

<?php//This is a working script

//Make sure to go through it and edit database table fields that you are searching

//This script assumes you are searching 3 fields

$hostname_logon = “localhost” ;

$database_logon = “sakila” ;

$username_logon = “username” ;

$password_logon = “password” ;

//open database connection

$connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( “Unable to connect to the database” );

//select database

mysql_select_db($database_logon) or die ( “Unable to select database!” );

//specify how many results to display per page

$limit = 10;

// Get the search variable from URL

$var = @$_GET[‘q’] ;

//trim whitespace from the stored variable

$trimmed = trim($var);

//separate key-phrases into keywords

$trimmed_array = explode(” “,$trimmed); // check for an empty string and display a message.

if ($trimmed == “”) {

$resultmsg =  “<p>Search Error</p><p>Please enter a search…</p>” ;

}// check for a search parameter

if (!isset($var)){

$resultmsg =  “<p>Search Error</p><p>We don’t seem to have a search parameter! </p>” ;

}

// Build SQL Query for each keyword entered

foreach ($trimmed_array as $trimm){

// EDIT the following SQL Query and specify your table and field names

$query = “SELECT * FROM customer WHERE first_name LIKE \”%$trimm%\” OR last_name LIKE  \”%$trimm%\” OR email LIKE  \”%$trimm%\” ORDER BY first_name DESC” ;

// Execute the query to  get number of rows that contain search kewords

$numresults = mysql_query($query);

$row_num_links_main = mysql_num_rows($numresults);     // next determine if ‘s’ has been passed to script, if not use 0.

// ‘s’ is a variable that gets set as we navigate the search result pages.

if (empty($s)) {

$s=0;

}      // now let’s get results.

$query .= ” LIMIT $s,$limit” ;

$numresults = mysql_query ($query) or die ( “Couldn’t execute query” );

$row= mysql_fetch_array ($numresults);

//store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.

do{

//EDIT HERE and specify your field name that is primary key

$adid_array[] = $row[ ‘customer_id’ ];

}while( $row= mysql_fetch_array($numresults));

} //end foreach

if($row_num_links_main == 0 && $row_set_num == 0){

$resultmsg = “<p>Search results for: $trimmed </p><p>Sorry, your search returned zero results</p>” ;

}

//delete duplicate record id’s from the array. To do this we will use array_unique function

$tmparr = array_unique($adid_array);

$i=0;

foreach ($tmparr as $v) {

$newarr[$i] = $v;

$i++;

} // now you can display the results returned. But first we will display the search form on the top of the page

?>

<form action=”search.php” method=”get” name=”search”>

<div align=”center”>

<input name=”q” type=”text” value=”<?php echo $var;?>” size=”15″>

<input name=”search” type=”submit” value=”Search”>

</div>

</form>

<?php

// display what the person searched for.

if( isset ($resultmsg)){

echo $resultmsg;

exit();

}else{

echo “Search results for: ” . $var;

}

foreach($newarr as $value){

// EDIT the following SQL Query and specify your table and field names

$query_value = “SELECT * FROM customer WHERE customer_id = ‘$value'”;

$num_value = mysql_query($query_value);

$row_linkcat = mysql_fetch_array($num_value);

$row_num_links = mysql_num_rows($num_value);

//now let’s make the keywods bold. To do that we will use preg_replace function.

//EDIT parts of the lines below that have fields names like $row_linkcat[ ‘field1’ ]

//This script assumes you are searching only 3 fields. If you are searching more fileds make sure that add appropriate line.

$first_name = preg_replace ( “‘($var)’si” , “<b>\\1</b>” , $row_linkcat[ ‘first_name’ ] );

$last_name = preg_replace ( “‘($var)’si” , “<b>\\1</b>” , $row_linkcat[ ‘last_name’ ] );

$email = preg_replace ( “‘($var)’si” , “<b>\\1</b>” , $row_linkcat[ ’email’ ] );

foreach($trimmed_array as $trimm){

if($trimm != ‘b’ ){

//IF you added more fields to search make sure to add them below as well.

$first_name = preg_replace( “‘($trimm)’si” ,  “<b>\\1</b>” , $first_name);

$last_name = preg_replace( “‘($trimm)’si” , “<b>\\1</b>” , $last_name);

$email = preg_replace( “‘($trimm)’si” ,  “<b>\\1</b>” , $email);

}

//end highlight?>

<p>

<?php echo $first_name; ?><br>

<?php echo $last_name; ?><br>

<?php echo $email; ?>

</p>

<?php

}   //end foreach $trimmed_array

if($row_num_links_main > $limit){

// next we need to do the links to other search result pages

if ($s>=1) { // do not display previous link if ‘s’ is ‘0’

$prevs=($s-$limit);

echo “<div align=’left’><a href=’$PHP_SELF?s=$prevs&q=$var&catid=$catid’>Previous ” .$limit. “</a></div>”;

}

// check to see if last page

$slimit =$s+$limit;

if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {

// not last page so display next link

$n=$s+$limit;

echo “<div align=’right’><a href=’$PHP_SELF?s=$n&q=$var&catid=$catid’>Next ” .$limit. “</a></div>”;

}

}

}  //end foreach $newarr

?>

The search code is now available for Download. Hope this helps.