dynamic table with mutliple filter options

sonofjorel · January 10, 2011

I am trying to create a dynamic table that gets generated based upon users birthday, then selection of last name (via a select menu), then first name (via a select menu), Each selection would subsequently filter out items not chosen. i.e. if the last name of Griffin were selected, only those with the last name Griffin would be present when selecting the first name.

I have been able to generate the birthday and then last name selection, but I am unclear of how to add the first name into the mix (and any other subsequent filters thereafter)

Here is what I as so far. Any help would be appreciated.

database sql code:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) NOT NULL,
  `lastname` enum('Griffin','Griffin6','Quagmire','Swanson') NOT NULL,
  `age` int(11) NOT NULL,
  `hometown` varchar(25) NOT NULL,
  `job` varchar(25) NOT NULL,
  `birthdate` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `user` */

insert  into `user`(`id`,`firstname`,`lastname`,`age`,`hometown`,`job`,`birthdate`) values 
(1,'Peter','Griffin',41,'Quahog','Brewery','1960-01-01'),
(2,'Lois','Griffin',40,'Newport','Piano Teacher','1961-08-11'),
(3,'Joseph','Swanson',39,'Quahog','Police Officer','1962-07-23'),
(4,'Glenn','Quagmire',41,'Quahog','Pilot','1960-02-28'),
(5,'Megan','Griffin',16,'Quahog','Student','1984-04-24'),
(6,'Stewie','Griffin',2,'Quahog','Dictator','2008-03-03');

index.php:

<?php
include('config.php');  //Include the database connections in it's own file, for easy integration in multiple pages.

$lastname_result = mysql_query("SELECT lastname FROM user GROUP BY lastname");

$result = mysql_query("SELECT * FROM user");

?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="start/jquery-ui-1.8.4.custom.css" rel="stylesheet" type="text/css"/></link>
<link href="../themes/style.css" rel="stylesheet" type="text/css" media="print, projection, screen" /></link>
<link href="../common.css" rel="stylesheet" type="text/css"></link>
<script type="text/javascript" src="jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="jquery.tablesorter.js"></script>
<script type="text/javascript" src="jquery-ui-1.8.4.custom.min.js"></script>
<script type="text/javascript">
$(document).ready(function() 
    { 
       	$("#myTable").tablesorter({widgets: ['zebra']});
	$("#options").tablesorter({sortList: [[0,0]], headers: { 3:{sorter: false}, 4:{sorter: false}}});
    } 
); 

function reSort(par) {
$("#tableBody").empty();
var vfrom = document.getElementById('from').value;
var vto = document.getElementById('to').value;
$.get('table.list.php?from='+vfrom+'&to='+vto+'&lastname='+par,function(html) { 
             // append the "ajax'd" data to the table body 
             $("#tableBody").append(html); 
            // let the plugin know that we made a update 
            $("#myTable").trigger("update"); 
		// set sorting column and direction, this will sort on the first and third column 
            var sorting = [[0,0]]; 
            // sort on the first column 
            $("#myTable").trigger("sorton",[sorting]); 
        }); 
}

</script>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
<tr>
<td width="160" valign="top"></td>
<td width="732" valign="top">
<table width="90%" border="0" align="center">
<form method="post">
<label for="from">From</label>
<input type="text" id="from" name="from"/>
<label for="to">to</label>
<input type="text" id="to" name="to"/>
  <?php //added javascript function call to function located in "ajax.js" ?>
<select id="selectbox" name="area" onchange="reSort(this.value);"> <?php //added javascript function call to function located in "ajax.js", and changed the id for content change via javascript. ?>
<option value="">Select an lastname</option>
<option value=""></option>
<?php while(list($lastname)=mysql_fetch_array($lastname_result)) {
     echo "<option value='$lastname'>$lastname</option>";
}
?>
</select>
</form>
</table>
<table id="myTable" class="tablesorter" border="0" cellpadding="5" cellspacing="1">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Birthday</th>
<th>Hometown</th>
<th>Job</th>
</tr>
</thead>
<tbody id="tableBody"> <?php //added id to the tbody so we could change the content via javascript ?>

</tbody>
</table>  
<p> </p>
<p> </p>
<p align="right">  </p>
</td>
<td width="196" valign="top"> </td>
</tr>



</table>
</body>
</html>

table.list.php:

<?php
include('config.php');  //database connection.
if(!isset($_GET['lastname'])) { //if the proper get parameter is not there, redirect to google.
header('Location: http://google.com');
exit();
}

$lastname = preg_replace('~[^A-Za-z]+~','',$_GET['lastname']);  //strip out anything but alpha for the name.
$lastname = trim($lastname); //trim the name from all whitespace.
if($lastname != '') { //check against an empty string.  could have just used "empty()".

$where = ' WHERE lastname = \'' . $lastname . '\''; //write the where clause.
}
else { //if lastname is empty, the where clause will be to, and it will return all names.
$where = NULL;
unset($lastname);
}

$from = (isset($_GET['from'])) ? date('Y-m-d',strtotime($_GET['from'])) : NULL;
$to = (isset($_GET['to'])) ? date('Y-m-d',strtotime($_GET['to'])) : NULL;

if($from != NULL && $to != NULL) {
$where .= (isset($lastname)) ? ' AND ' : ' WHERE ';
$where .= " birthdate BETWEEN '$from' AND '$to'";
}
elseif($from != NULL) {
$where .= (isset($lastname)) ? ' AND ' : ' WHERE ';
$where .= " birthdate >= '$from'";
}
elseif($to != NULL) {
$where .= (isset($lastname)) ? ' AND ' : ' WHERE ';
$where .= " birthdate <= '$to'";
}
$query = "SELECT * FROM user $where";
$result = mysql_query($query); //get the database result.
while($row=mysql_fetch_assoc($result)){  //loop and display data.
?>
<tr>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['birthdate']; ?></td>
<td><?php echo $row['hometown']; ?></td>
<td><?php echo $row['job']; ?></td>
</tr>

<?php } // End while loop. ?>

Sign In

dynamic table with mutliple filter options

Recommended Posts

sonofjorel

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information