Talk In Code

Using server side scripts to search for things can be as complex or as simple as the situation requires. However, if you have a table of results and you just want to enable a simple JavaScript search on that table then this might be the script for you.

To search a table using JavaScript you need to split the table into bits, this can be done using the getElementsByTagName() function, which takes the name of the element that you want to capture. So to grab all of the rows of a table as an array you need to pass the value of tr.

var rows = document.getElementsByTagName("tr");

We can then iterate through these rows, grabbing the column that you want to search on, with the following code.

<form action="#" method="get" onsubmit="return false;">
<input type="text" size="30" name="q" id="q" value="" onkeyup="doSearch();" />
</form>

Next, the table. Note that I have added an additional row to the end of this table. This will be used to display a note to the user if they have entered a query that isn’t found.

<table>
<tr><td>One</td></tr>
<tr><td>Two</td></tr>
<tr><td>Three</td></tr>
<tr><td>Four</td></tr>
<tr><td>Five</td></tr>
<tr><td>Six</td></tr>
<tr><td>Seven</td></tr>
<tr><td>Eight</td></tr>
<tr style="display:none;" id="noresults">
 <td>(no listings that start with "<span id="qt"></span>")</td>
</tr>
</table>

The first thing we need to do in our search function is to prepare the search term. This turns the query string to lowercase, which we can then match to the table column.

var q = document.getElementById("q");
var v = q.value.toLowerCase();

Now we can go through each row value and try to match it to the value in the query string. If it matches then we display the row, if not then we hide it.

  for ( var i = 0; i < rows.length; i++ ) {
    var fullname = rows[i].getElementsByTagName("td");
    fullname = fullname[0].innerHTML.toLowerCase();
    if ( fullname ) {
        if ( v.length == 0 || (v.length < 3 && fullname.indexOf(v) == 0) || (v.length >= 3 && fullname.indexOf(v) > -1 ) ) {
        rows[i].style.display = "";
      } else {
        rows[i].style.display = "none";
      }
    }
  }

Here is the full function, including the code to implement the no results note.

<script type="text/javascript">
//<!--
function doSearch() {
  var q = document.getElementById("q");
  var v = q.value.toLowerCase();
  var rows = document.getElementsByTagName("tr");
  var on = 0;
  for ( var i = 0; i < rows.length; i++ ) {
    var fullname = rows[i].getElementsByTagName("td");
    fullname = fullname[0].innerHTML.toLowerCase();
    if ( fullname ) {
        if ( v.length == 0 || (v.length < 3 && fullname.indexOf(v) == 0) || (v.length >= 3 && fullname.indexOf(v) > -1 ) ) {
        rows[i].style.display = "";
        on++;
      } else {
        rows[i].style.display = "none";
      }
    }
  }
  var n = document.getElementById("noresults");
  if ( on == 0 && n ) {
    n.style.display = "";
    document.getElementById("qt").innerHTML = q.value;
  } else {
    n.style.display = "none";
  }
}
//-->
</script>

Here is a working example of the code, with valid HTML.

If you have a table of incremental values it can be hard to find out which ones are missing. The only solution might be to write a script to get all the data from the database and see which ones are missing. However, there is a way of doing this without using a script.

Using a standard select query like this:

SELECT * FROM table;

Gets the following data:

1
3
10
23

We can see that values are missing, but which ones? The following query will show us where the gaps are in the data of the table.

SELECT t1.id+1 as Missing
FROM table as t1
LEFT JOIN table as t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY t1.id;

Produces the following result.

2
4
11
24

However, this only tell us where the gaps are, not how long they are. To get the range of where the gaps from and to we need to do something a little more complex.

SELECT
t1.id+1 AS 'Missing From',
MIN(t2.id) - 1 AS 'To'
FROM table AS t1, table AS t2
WHERE t1.id < t2.id
GROUP BY t1.id
HAVING t1.id < MIN(t2.id) - 1;

This query gives the following result.

Using this dataset we can figure out where the gaps in the data are and perhaps do something with them.

MySQL uses the datatype TINYINT to store boolean values. MySQL stores the value as TINYINT(1) which is the same as a bit so the value is either 0 (false) or 1 (true). Using boolean fields can be very useful, but it can be costly in processing as to change the value you have to query the database, find out the value of the field and then act accordingly.

Here is a simple MySQL query that can be used to toggle the value already present in the TINYINT field without having to do any pre-querying.

UPDATE table SET field = 1 - field