CODEWITHSUNDEEP
phpjquerymysqldatabasejson
Nathan
Nathan

Reputation: 3

Filtering DB Results Using jQuery and PHP

I have just started creating my first website, which is backed by a database (this is also my first db). Due to my total inexperience, I have no idea if I should rely on jQuery or PHP for a specific task, and am hoping that someone might be able to provide some insight. (This is a 'best practices' question.)

In the table "Files," there are 6 fields and approximately 50 rows. Users are shown all of the information in the table, and are able to apply filters (via checkboxes) to manipulate how that data is displayed. For example, if the boxes category = DNA and keyword = assay are checked, a php script selects those rows in the db where the fields category and keyword have the values DNA and assay, respectively. This information is then returned to a jQuery script as a JSON object for formatting and insertion into the DOM (I used $.getJSON).

So far, this works just fine. However, upon reading that "too many" queries to a db incurs a performance penalty, I wrote a new jQuery script which gets ALL of the information from the table "Files" immediately after the document loads. This information is distributed into 6 different arrays (i.e. category[ ] and keyword[ ], with the index of a given row the same in all 6 arrays). Then, if the same filters as above are applied, I search the category[ ] and keyword[ ] arrays. The information associated with the index i is displayed if the strings DNA and assay are found in category[ i ] and keyword[ i ].

Obviously, the second method reduces the number of queries to the db. However, it feels 'wrong' to me in that I am using jQuery for something that isn't exactly 'behavior,' and I doubt that jQuery is as fast at searching arrays as MySQL is at finding rows in a table. Testing both versions of the site, they seem to perform equally well, but I am only able to test the site locally. I imagine that the jQuery version will become less viable if the table grows to be very large, but it is unlikely that it will ever exceed 100 rows.

If you have any advice, I would greatly appreciate any help in deciding which option I should implement. Thank you in advance for your input, and I hope you'll accept my apologies for being such a noob!

Upvotes: 0

Views: 975

Answers (2)

RET
RET

Reputation: 9188

You have to weigh up the calls back and forth to your server in the AJAX version against the client-side manipulation. Although there's nothing wrong with your inside-out hash approach (the multiple arrays with shared keys), it's quite possible you could filter the rows based on classes and use jQuery to show/hide them.

<li class="dna assay">blah...</li>

and elsewhere...

$('#dnacheckbox').click(function(){ $('.dna').toggle() });

Personally, I don't see what you're doing with jQuery as not being behaviour - sorting and filtering is classic user interaction.

If you can say with confidence that the row-count will remain under a few hundred, I would suggest the client-side option is the one to go for. It will definitely feel snappier to the end-user.

I'm not sure exactly how you're presenting your data, but have a look at DataTables before you do too much Wheel R&D.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157877

With 6 fields and approximately 50 rows you can do whatever you wish - with jquery or PHP or PHP and jquery. There will be no difference at all.

Upvotes: 1

Related Questions