CODEWITHSUNDEEP
mysqldatabaseajax
Emma
Emma

Reputation: 2022

Filter rows of an HTML table generated from a database using AJAX

I have an HTML/XML table generated by AJAX that displays limited columns of a database table. I would like to filter rows of the HTML/XML table using columns from the database that aren't part of the HTML table.

Example:

MySQL tables (ignore bad syntax):

TABLE technicians (id,
                   name,
                   email,
                   level,
                   PRIMARY KEY (id));
TABLE certificates (id,
                    type,
                    name,
                    PRIMARY KEY(id, type),
                    FOREIGN KEY (id) REFERENCES technicians(id));

HTML/XML table:

<table>
  <tr>
    <td>technician name</td>
    <td>technician level</td>
  </tr>
</table>

Each row in the HTML/XML table will display the technician name and technician level, but each "technician row" will have more data associated with it in the database. I want to filter each "technician row" by certificates as well as level. Note that as an individual attribute is filtered the corresponding rows are shown/hidden, instead of submitting a block of criteria at once (each criteria submits itself, basically).

As far as I can tell, I can do this in the following ways:

  1. Track the additional data in the HTML/XML table. This way I can hide/show individual rows as their attributes are filtered.
  2. Reload the table by passing the filter criteria to my server side script. This would require me to parse the entire filter form and reload the whole table each time an attribute is filtered.
  3. Send an AJAX request of criteria to filter to a script that will return ID's of technicians in JSON format. That way I could store only the technician's ID with the table row.

I'm new to web development and I'm trying to find a good balance between doing things server side vs. client side. What is the proper way to accomplish this?

EDIT: added number 3.

Upvotes: 0

Views: 889

Answers (1)

Alex Martelli
Alex Martelli

Reputation: 881903

The way I would do it, if (as seems to be the case, since you mention AJAX) you're already anyway requiring the client's browser to have decent Javascript functionality in order to access your site, is send all the relevant data from the proper SELECT query down to the client as JSON; this way, the Javascript code on the client side can do all the desired filtering and build and display the HTML table on the fly as needed -- faster than a round trip to the server (much faster, on good modern browsers with good Javascript engines).

The task (like most other Javascript tasks;-) is vastly facilitated if you pick a good Javascript framework for your work (jQuery appears to be the most popular one at this time, to the point that I think it can be recommended as the "default" choice for new users) -- in particular, such frameworks do an awesome job of smoothing over many browser differences, incompatibilities, and bugs, making your sites available to a wider audience.

Upvotes: 1

Related Questions