Drop box filteron table

[balgrath]

Hi All,

I have built an SELECT statement that pulls * from my table. and displays nicely. yay me but I am looking to take this to the next level by adding an inline filter, as in a SELECT box with options (I will pull these either from the results or another db table)

 

<?php
require './connections/db_connect.php';
$db_name="Projects_Main"; // Database name 
$tbl_name="Main"; // Table name

// Connect to server and select database.
mysql_connect($host, $usrname, $pssword)or die("Connection failed"); 
mysql_select_db($db_name)or die("Connection failed");
$sql=("SELECT * FROM $tbl_name WHERE Status not like 'CLOSED'");
$result=mysql_query($sql);

$num_rows = mysql_num_rows($result);
$fields_num = mysql_num_fields($result);

?>
<table>

<!-- create the table headers and using the next section build the table. sections not shown as page space not sufficent -->
<tr>
<th>Project ID</th>
<th>Financial Year</th>
<th>Project Executive</th>
<th>Retail Project Manager</th>
<th>Country</th>
</tr>
<!--START:filter    NOT WORKING-->
<tr>
<th>no filter</th>
<th>no filter</th>
<th>no filter</th>
<th>no filter</th>
<th><?php
	$database = 'Projects_Main';
	$contable = 'Country';
		if (!mysql_connect($db_host, $db_user, $db_pwd))
			   die("Can't connect to database 'cos somethin' is wrong");
		if (!mysql_select_db($database))
			 die("Can't select database");
	$result = mysql_query("SELECT country_abbrev, country_name FROM {$contable} order by country_name");
	$options="";
while ($row=mysql_fetch_array($result)) 
		{
			$id=$row["country_abbrev"];
			$thing=$row["country_name"];
			$options.="<OPTION VALUE=\"$id\">".$thing."</option>";
		}
?>
<SELECT NAME="Country" type="text">
<OPTION VALUE=0>Select Country
<? echo $options?>
</SELECT>
</th>

</tr>
<!--END:  filter -->

<?php
while($rows=mysql_fetch_array($result))
{


?>
<!-- echo the contents of database table -->
	<tr>
		<td align="center"><?php echo $rows['Project_id']; ?></td>
		<td><?php echo $rows['FinancialYear']; ?></td>
		<td><?php echo $rows['ProjectExecutive']; ?></td>
		<td><?php echo $rows['RetailProjectManager']; ?></td>
		<td><?php echo $rows['Country']; ?></td>

	</tr>
<?php
}
?>
</table>

<?php
mysql_close();
?>

 

I am a little confused on how to implement the select box on change with out reloading the entire page?? any guidance??

Balgrath

[Psycho]

If you want to do this without reloading the page you will have to use AJAX: a JavaScript client-side script that makes a server-side call and updates the page accordingly. However, I would first suggest building it so that you have to submit the page - then implement AJAX. That way you can develop the server-side code independent of the client-side code. This makes it easier when developing - when there is a problem you don't have to try and figure out if it is PHP or JavaScript. Once you have the PHP code functioning, you can then implement the JavaScript code (a la AJAX) to have it work without a page refresh.

 

So, are you having any problems with the current implementation?

[balgrath]

thanks for the feed back.  The current page works and bring everything as it should. looks pretty too I might add. god bless CSS.  The filtering on the other hand I am only beginning to look at. I have see the jquery.tablesorter very nice and the Live filter also very nice but was holing for something simpler )

I'm obviously going to have to look at this a lot harder.

 

The Select box that I have in post above\below (cant remember how this forum posts) works as I use it in my input form for new records.  It populate the drop down form another table.  its messy, and does need to be tidied and injection proofed but i'll sort that later.

 

Can you point me to some lite reading?? 

 

Thanks

Balgrath

 

[Psycho]

Hmm...

 

I guess you could use a JavaScript only solution. I assume jquery.tablesorter is a client-side only table filtering function. If you don't have hundreds of records that is a possible solution (assuming you understand that it will only work when the user has JS enabled). But, even if you go the AJAX route you would need JS + PHP. So, if you have a fairly limited record set a JS solution might be easier to implement. If the record set is large you don't want to push all that data down to the client and AJAX would be a better choice.

 

I'm not familiar with  jquery.tablesorter but I could probably give you a down-and-dirty solution:

 

1. Since this will be client side, you need to use the client side values. So, the first order of business is to change the options for the country select list to be the country names, not the abbreviation, so we can match up the selected value with the values in the table.

$options.="<OPTION VALUE=\"{$thing}\">{$thing}</option>\n";

NOTE: I would add a default selection at the top of the list for "ALL". Make the value an empty string. Where you define the options variable use

$options="<OPTION VALUE=\"\">ALL</option>\n";

 

Now, in the code that produces the table add an ID to each TR tag as well as the TD for the country value. Use a numerically based index appended to each

<?php
    $rowID = 0;
   while($rows=mysql_fetch_array($result))
   {
        echo "<tr id=\"row_{$rowID}\">\n";
        echo "<td align=\"center\">{$rows['Project_id']}</td>\n";
        echo "<td>{$rows['FinancialYear']}</td>\n";
        echo "<td>{$rows['ProjectExecutive']}</td>\n";
        echo "<td>{$rows['RetailProjectManager']}</td>\n";
        echo "<td id=\"ctry_{$rowID}\">{$rows['Country']}</td>\n";
        echo "</tr>\n";
        $rowID++;
    }
?>

 

Next create an onchange event for the select field

<SELECT NAME="Country" type="text" onchange="filterTable(this.options[this.selectedIndex].value);">

 

Lastly create a function to show/hide each row based upon the selected value

function filterTable(selCtry)
{
    dim rowIdx = 0;
    dim tdObjVal, displayVal;
    while(document.getElementById('ctry_'+rowIdx))
    {
        //Get value of current row country cell
        tdObjVal = document.getElementById('ctry_'+rowIdx).innerHTML;
        //Define display style for current row based upon selcted country
        displayVal = (selCtry=='' || selCtry==trObjVal) ? '' : 'none';
        //Show/hide current row using display style property
        document.getElementById('row_'+rowIdx).style.display = displayVal;
        rowIdx++;
    }
    return;
}

 

All of this was done on-the-fly and not tested with your script. It may work as is or there may be some syntax errors or minor issues to fix.

[balgrath]

thanks again for the info.. interesting approach.. but involves me rebuilding my lovely tables  :-\  (was looking for an idiot proof implementation that even I could use  )

 

I may take a deeper look around the AJAX idea. totally new area for me but if it hits the mark i'm happy to learn. thanks for you assistance it has given me food for thought.

 

Thanks

Balgrath

[Psycho]

thanks again for the info.. interesting approach.. but involves me rebuilding my lovely tables

What do you mean rebuilding your tables? The look of your tables won't be affected at all. The changes I proposed would only add some internal ID fields to the tables. The tables would look EXACTLY as they do now. The code may look much different because I moved the HTML code for the table to be within the PHP. It is messy to enter/exit PHP blocks repeatedly in your script. So, I just converted the same HTML code you have now to be generated within the PHP while loop (and adding the ID fields, nothing more).

 

[balgrath]

now I have re read your post, i must apologies.. eyes must have seen something the brain didn't. i'll have a go and revert. as an expert in my field I know Noobos tend do stupid things so you'll have to excuse my nooboish. thanks

Balgrath