Tutorials

Use jQuery to display number of rows in a mysql database on a form

Im currently writing a search engine that uses a couple of jQuery UI autocompletes and comboboxes, and to spice things up a bit I thought I would add some kind of live updating script that shows the user how many results will be returned, depending on the current search settings BEFORE actually pressing submit/search

i.e.  the Autotrader car search does this pretty dam nicely.

This is my translation of this effect with jQuery and PHP/mysql

Note: This tutorial assumes you already have a form with jQuery auto-completes etc all with element id’s attached

First lets start of with the javascript that will be send the data to an external php file



function numRows() {

var querydb = $("formid").serialize();

$.ajax({

url: "checkNumRow.php",

dataType: "text",

data: querydb,

success: function(result){

$("#results").text(result);

},

});

}

The jQuery script is now ready to send a query to the php file checkNumRows.php whenever the function numRows() is called, it will also return the data to any tag named #results.

So now you want the function numRows() to be called whenever the user changes data on the unfinished form, simply add the following at the end of your javascript file


$(":checkbox, :radio").click(numRows);

$("select").change(numRows);

$("input").blur(numRows);

$("input").focusout(numRows); 
// I used focusout as well as blur because blur doesn't seem to 
// work well with auto complete input boxes

numRows();

Now that the javascript is ready you need to create a php file called checkNumRows.php and insert the following code below and populate it with your own code to search your database


/*
filename::checkNumRows.php
Build your mysql search string using $_POST["element.id"]
above this so hat you end up with something along the 
lines of
$sql = "MYSQL SEARCH STRING";
*/
$results = mysql_query($sql);
$numrows = mysql_num_rows($results);
if ($numrows=="1"){
// You can echo "insert html code here" to create something more complex
//  like the autotrader display
echo  $numrows . " result found";
} else {
echo  $numrows . " results matching the search criteria";
}

Now that is done your php file should be able to display the number of rows in your database depending on whatever your $_POST (the names of your form elements on your form) requests are.
For example a black three door car search might look like this

www.yourwebsite.com/checkNumRows.php?colour=black&doors=3
and should return something similar to
200 results matching the search criteria

Now this is done, back on the page with your form you just add this 1 line to display live results

Feel free to ask questions, I will provide as much help as possible