Live Mysql Data Search using javaScript with PHP

Webslesson

PHP, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Wednesday, 9 June 2021

Ajax Live Data Search using JavaScript with PHP

In this post you can find tutorial on How to Create Ajax Live Mysql Database search using JavaScript with PHP script. With the help of this tutorial you can learn how to implement Ajax Live data search functionality in your HTML web page using javaScript without using any extra library like jQuery. So from HTML web page you can search mysql database using PHP script with the help of Ajax.

If you want to improve your web application UI then at that time we have to reqire search database data without reloading of entire web page. So for get the solution of this problem, here you can see how can we have implement ajax live data search using javaScript with PHP script. From this post you can see how can we easy way to live search with Mysql using javaScript with Ajax.

Ajax Live Database Search using javaScript

If you are looking for Live Data search functionality using pure vanilla javaScript, then you can come on right place because in this tutorial, we have covered topic simple live database search functionality using javaScript with Ajax and PHP, in which search results will be start displaying, when user has start write in search textbox.

In this tutorial we have are going to make live search box which will search data in mysql table and display result on web page without refresh of web page, because here we have use Ajax in javaScript.

Step 1: Create Database Table

For create table in mysql database, we need to execute following SQL query which will create post table in your MySQL database.

 -- -- Table structure for table `post` -- CREATE TABLE `post` ( `id` mediumint(8) UNSIGNED NOT NULL, `post_title` text, `post_description` text, `author` varchar(255) DEFAULT NULL, `datetime` datetime DEFAULT NULL, `post_image` varchar(150) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `post` -- ALTER TABLE `post` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `post` -- ALTER TABLE `post` MODIFY `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT; 

Once table has been created then you have to insert some data using the SQL INSERT statement. After inserting data into MySQL table then able you can perform live database search operation using pure vanilla javaScript.

Step2: Create Search Form & Table for Load Data

After your MySQL database is ready, then we have to proceed to write HTML code and javaScript code. First we have to create web interface which will allows user to perform live search functionality.

Under this tutorial, we will write HTML and javaScript code under index.html file, which source code you can find below.

         
Live Mysql Data Search using javaScript with PHP
 
 
 
Sample Data
 
Total Data - 
 
 
 
 
 
 
 
 
#
 
Post Title
 
Description
 
 
 
 
 
 
     

In above code, first we have create Search form in which you can type their search query and below this form we have create one table under this table we will load post table data when page has been load by using javaScript.

Below HTML code we have write javaScript code and under this we have create one load_data(query = ») function. This function will be called when page has been load then at that time then it will received all post table data in JSON format and convert that data into HTML format and display on this page. And when user has type something in search textbox, then also this function will be called and it will display only filter data on web page without refresh of web page. This is because, we have called this function under search textbox onkeyup=»load_data(this.value);» attribute, so when user type something then this javascript function will called and it will display filter data on web page.

Step 3: Processing Search Query at PHP Script

Now we have come on backend PHP script code which we will write under process_data.php file. Under this file we will write PHP script which will search MySQL database data based on query string which user has send by the Ajax request and this PHP script send data back to ajax request in JSON string fromat which has been display in browser to user side.

  '%' . $condition . '%', ':post_description' => '%' . $condition . '%' ); $query = " SELECT post_title, post_description FROM post WHERE post_title LIKE :post_title OR post_description LIKE :post_description ORDER BY id DESC "; $statement = $connect->prepare($query); $statement->execute($sample_data); $result = $statement->fetchAll(); $replace_array_1 = explode("%", $condition); foreach($replace_array_1 as $row_data) < $replace_array_2[] = ''.$row_data.''; > foreach($result as $row) < $data[] = array( 'post_title' =>str_ireplace($replace_array_1, $replace_array_2, $row["post_title"]), 'post_description' => str_ireplace($replace_array_1, $replace_array_2, $row["post_description"]) ); > > else < $query = " SELECT post_title, post_description FROM post ORDER BY id DESC "; $result = $connect->query($query); foreach($result as $row) < $data[] = array( 'post_title' =>$row["post_title"], 'post_description' => $row["post_description"] ); > > echo json_encode($data); > ?> 

Under this PHP script first we have make database connection and after making database connection this script check ajax request has been received from fetch all data from post table or search query has been send for fetch filter data from database.

Suppose Ajax request has been received for fetch all data from Mysql database then here we have write SELECT query for fetch all data from post and send back data to Ajax request in JSON format.

But suppose Ajax request has been receive for get filter data from MySQL database. So in this script first it has clean search query by using preg_replace() function for prevent SQL Injection and then after write SELECT query with LIKE statement for search data from post MySQL table and return back data to Ajax request in JSON format.

Conclusion

In this post, you have learned how to create live search in PHP with MySQL database table using javaScript and Ajax.

If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.

Источник

PHP — AJAX and MySQL

The following example will demonstrate how a web page can fetch information from a database with AJAX:

Example

Example Explained — The MySQL Database

The database table we use in the example above looks like this:

id	FirstName	LastName	Age	Hometown	Job
1	Peter	Griffin	41	Quahog	Brewery
2	Lois	Griffin	40	Newport	Piano Teacher
3	Joseph	Swanson	39	Quahog	Police Officer
4	Glenn	Quagmire	41	Quahog	Pilot

Example Explained

In the example above, when a user selects a person in the dropdown list above, a function called «showUser()» is executed. The function is triggered by the onchange event. Here is the HTML code:

Example

• Create an XMLHttpRequest object
• Create the function to be executed when the server response is ready
• Send the request off to a file on the server
• Notice that a parameter (q) is added to the URL (with the content of the dropdown list)

The PHP File

The page on the server called by the JavaScript above is a PHP file called «getuser.php».

The source code in «getuser.php» runs a query against a MySQL database, and returns the result in an HTML table:

table, td, th border: 1px solid black;
padding: 5px;
>

$con = mysqli_connect(‘localhost’,’peter’,’abc123′);
if (!$con) die(‘Could not connect: ‘ . mysqli_error($con));
>

mysqli_select_db($con,»ajax_demo»);
$sql=»SELECT * FROM user WHERE «;
$result = mysqli_query($con,$sql);

echo «

«;
while($row = mysqli_fetch_array($result)) echo «

«;
echo «

«;
echo «

«;
echo «

«;
echo «

«;
echo «

«;
echo «

«;
>
echo «

Firstname	Lastname	Age	Hometown	Job
» . $row[‘FirstName’] . «	» . $row[‘LastName’] . «	» . $row[‘Age’] . «	» . $row[‘Hometown’] . «	» . $row[‘Job’] . «

«;
mysqli_close($con);
?>

Explanation: When the query is sent from the JavaScript to the PHP file, the following happens:

1. PHP opens a connection to a MySQL server
2. The correct person is found
3. An HTML table is created, filled with data, and sent back to the «txtHint» placeholder

Источник