CODEWITHSUNDEEP
javascriptphpjqueryajax
KDJ
KDJ

Reputation: 292

php ajax live database search or one query that gets all results to search through

I am building a "customer look up" tool for a company. The staff will be using this to pull records of their customers.

I was curious which was better and why..

Option 1:

Using php + ajax to search via onkeyup of search box. This would be a live search on key up that would query the database each time the key goes up. I would most likely approach this using WHERE CustomerName LIKE '%blah blah%' The database of customers could end up getting really big at some point...just wanted to add that in. How big? Well, thats up to the company.

OR

option 2:

Going ahead and calling all customers via one query that all get populated in a list on the website. As the staff member types in a search box, the pre-populated list gets filtered through. Again, the database of customers might get pretty large.

Either way, its alot of data to be sorting through. I need to keep this "onkeyup" style method without the use of a "search" button.

IF there is a better way, please also tell me about that as well.

I've already coded the part where it pre-populates the list and looked at the ajax option; however, I'm worried about performance once the customer list begins to grow. So, thats where this question comes in. This post is not to get specific code, just want to learn the best way to approach this project as well as future projects like this.

I will go ahead and post the code I'm currently using to filter through pre-populated data just for the record...

$(".searchbox").on('keyup', function () {
                if ($(this).val() != '') {
                    search(this);
                }
                else {
                    $(".data").show();
                    $.each($(".searchbox"), function () {
                        if ($(this).val() != '') {
                            $(this).keyup();
                        }
                    });
                }
            });

            function search(ele) {
                var val = $(ele).val() || '';
                if (val == '')
                    return;

                var dataclass = $(ele).attr('data-class');
                var SearchInText = '';
                $.each($(".data:visible"), function () {
                    SearchInText = $(this).find("td." + dataclass).text();
                    if (SearchInText.indexOf(val) == -1)
                        $(this).hide();
                });
            }

Upvotes: 0

Views: 804

Answers (1)

Johna
Johna

Reputation: 1894

If you expect the customer database to be really large, loading them all the browser in a one call is not a good a option. You will have to live load the customers based on the user input.

Live loading(onkeyup) could give much stress to your database. However, you can improve it with caching.

Since you are retrieving only the 'customer name' from the database, you can use a caching mechanism to keep customer names in the memory and send the filtered results to the browser. That way, you can avoid stress in your database.

You should delay the keyup handler to minimize request to server.

You can find how to do it in the following link.

How to delay the .keyup() handler until the user stops typing?

Upvotes: 2

Related Questions