Friday, November 3, 2017

Ajax infinite scroll using jquery php and mysql


Posted by at No comments:

Thursday, June 2, 2016

Search Engine using PHP, Mysql And Ajax

Hi, guys if you want to create search engine using PHP , Mysql and AJAX you need to follow below steps

1. Create a database

The following SQL statement creates a database called "store":

CREATE DATABASE store;

The following SQL statement creates a table called "users" that contains five columns: userID, name, email, phone, and city.

CREATE TABLE users
(
userID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
phone varchar(15) NOT NULL,
city varchar(255),
UNIQUE (userID,phone,email)
);

 NOT NULL- column should not be NULL.
AUTO_INCREMENT -column incremented automatically
PRIMARY KEY- uniquely identifies each record in a database table.
UNIQUE-uniquely identifies each record in a database table.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

To create database first we need connect to server . server connection PHP file is given below

databaseFunction.php

<?php
$servername = "localhost"; 
$username = "username";
$password = "password";

// Create connection

$conn = mysql_connect($servername, $username, $password);
// Check connection
if (!$conn) {
    die("Connection failed: " . $conn_connect_error);
} 

?>

 Note:Don't forget to insert username and password

 then Create database

 createDataBase.php


<?php
require_once 'databaseFunction.php';

 $sql = "CREATE DATABASE store";

if (mysql_query($sql)) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . mysql_error();
}



?>

and  Create Table

 createTable.php


<?php
require_once 'databaseFunction.php';
$dbname='store';

mysql_select_db($dbname) or die(mysql_error());

 $sql = "CREATE TABLE users
(
userID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
phone varchar(15) NOT NULL,
city varchar(255),
UNIQUE (userID,phone,email)
);";

if (mysql_query($sql)) {
    echo "Table created successfully";
} else {
    echo "Error creating table: " . mysql_error();
}



?>

2.Insert some data to the table 'store'

insertUsers.php



<?php
require_once 'databaseFunction.php';
$dbname='store';

mysql_select_db($dbname) or die(mysql_error());

$sql = 'INSERT INTO users(name,email,phone,city) VALUES("kumar","abc@gmail.com","1234567890","mysore")';

mysql_query($sql);
  
$sql = 'INSERT INTO users(name,email,phone,city) VALUES("suki","abcd@gmail.com","1236666789","mysore")';

mysql_query($sql);
$sql = 'INSERT INTO users(name,email,phone,city) VALUES("vivek","abc@gmail.com","1239997890","mysore")';


mysql_query($sql);
?>

This step is to ensure that some data in the users table, You can skip this step

 Once database was created and data has been added it should look like this



3.Create search box



index.php

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 5px;
}
</style>
</head>
<body>
<p>You can keyword in the search box</p>
<div>
<input type="search" id="searchID" onkeyup="showR(this.value)">
</div>
<p>Search results</p>
<div id="resultID"></div>

</body>

</html>

onkeyup event Execute a Ajax function  called showR(this.value) when a you releases a key

4.Write a ajax function -'showR(str)'

<script>

function showR(str){
   if (str.length == 0) { 
        document.getElementById("resultID").innerHTML = "";
        return;
    } else {
        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("resultID").innerHTML = xmlhttp.responseText;
            }
        };
        xmlhttp.open("GET", "getResult.php?q=" + str, true);
        xmlhttp.send();
    }
}
</script>

    


This Scripts can be placed in the <body>, or in the <head> section of an HTML page.

 The XMLHttpRequest object is used to exchange data with a server.
To send a request to a server, we use the open() and send() methods of the XMLHttpRequest object:

First, check if the input field is empty (str.length == 0). If it is, clear the content of the resultID placeholder and exit the function.
However, if the input field is not empty, do the following:
  • Create an XMLHttpRequest object
  • Create the function to be executed when the server response is ready
  • Send the request off to a PHP file (getResult.php) on the server
  • Notice that q parameter is added getResult.php?q="+str
  • The str variable holds the content of the input field

5.Write PHP file- 'getResults.php'

getResult.php

<?php
require_once 'databaseFunction.php';

$key=$_GET['q'];
$dbname='store';

mysql_select_db($dbname) or die(mysql_error());

$sql = "SELECT * FROM users WHERE name LIKE '%$key%'";
$result=mysql_query($sql );
$num=mysql_num_rows($result);

if($num>0){
echo '<table><tr><td>Name</td><td>Phone</td><td>email</td><td>city</td></tr>';

 while($row=mysql_fetch_array($result)){

 $name=$row['name'];
$phone=$row['phone'];
$email=$row['email'];
$city=$row['city'];

echo '<tr><td>'.$name.'</td><td>'.$phone.'</td><td>'.$email.'</td><td>'.$city.'</td></tr>';
}

echo '</table>';
}else{
echo "No results found";

 }
?>

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The "%" sign is used to define wildcards (missing letters) both before and after the pattern.

 If you want search by email or phone or city, just change the column name in the sql statement
Search by email :
$sql = "SELECT * FROM users WHERE email LIKE '%$key%'";

Search by city :
$sql = "SELECT * FROM users WHERE city LIKE '%$key%'";

Search by phone :
$sql = "SELECT * FROM users WHERE phone LIKE '%$key%'";


Note:The mysql_fetch_array() function fetches a result row as an associative array, a numeric array, or both 

Now,the Coding part is finished. So you can type in search box it will automatically shows results 

output:







Posted by at No comments:
Subscribe to: Posts (Atom)