PHP + MYSQL filter query results dynamically (without sending back to the server)

Prime8 · 12-09-2012, 06:32 AM

Hello, I have a database I am trying to perform some filtering on. The default display is all of the records (working - or was before I tried what I'm about to post), and I have some inputs (drop downs, text fields) I'd like to change the state of and have the results update - hopefully without the use of a submit button. Here's basically what I'm trying to do.

Basic PHP code:

PHP Code:


			
echo "<th><select name='A' id='A' onChange='" . theAFunction() . "'>

<option value='1'>1</option>

<option value='2'>2</option>

</select></th>";

echo "<th><input type='text' name='B' id='B' onChange='" . theBFunction() . "'></input></th>";

.

.

.



$a = '%'; // Setting the variables to match all the records first

$b = '%';

$c = '%';

$d = '%';



$filter = "SELECT * FROM table WHERE aye LIKE '" . $a . "' AND bee LIKE '" . $b . "' AND cee LIKE '" . $c . "' ORDER BY aye";



$result = mysql_query($filter);



.

.

.

while ($row = mysql_fetch_array($result))

echo "<tr>";

echo "<td>" . $row[aye] . "</td>";

echo "<td>" . $row[bee] . "</td>";

echo "</tr>";



function theAFunction()

{

// trying incorrectly to get the values of the fields as they are changed,

// obviously $_POST doesn't work but this is where my problem is

$a = $_POST['A'];

}

function theBFunction()

{

$b = $_POST['B'];

}

.

.

.

The above code returns errors on the $_POST lines (to be expected since nothing has been sent to the server).

I'm more used to the javascript DOM, and it seems like I do need to pass the results back to javascript somehow to do this without submitting a form - but then I think I will run in to a problem with getting js to update the query results from the database.

Any help would be appreciated.

BubikolRamios · 12-09-2012, 09:43 AM

If you don't want to submit form use AJAX

sunfighter · 12-10-2012, 04:42 AM

Your very first line of code should be throwing up an error:

Code:

echo "<th><select name='A' id='A' onChange='" . theAFunction() . "'>

It isn't finished.
Next <th> is used in tables. Should not be here.

Code:

WHERE aye LIKE '%' ...

makes no sense.

Why don't you post the complete code? The html and the php so you can see the problem.

You have to submit a form, that's how html talks to php to update or select information from a database.

Old Pedant · 12-10-2012, 07:47 PM

Quote:

Originally Posted by sunfighter

Your very first line of code should be throwing up an error:

Ummm...I don't use PHP, but I know that is not true.

His very first line of code is actually 4 physical lines:

Code:

echo "<th><select name='A' id='A' onChange='" . theAFunction() . "'> 
<option value='1'>1</option> 
<option value='2'>2</option> 
</select></th>";

See the semicolon at the end there? It's correct.

***************

Quote:

WHERE aye LIKE '%' ... makes no sense.

Yes, it does. He is simply *testing* the logic. As he stated

Quote:

The default display is all of the records ...

and

Quote:

$a = '%'; // Setting the variables to match all the records first

And that WHERE clause (and the subsequent parts of it) will do exactly that.

****************

His problem is simple, he calls theAfunction( ) in doing that first echo, which I assume does what it is supposed to do by setting the value of $a to match $_POST["A"], but then he turns around *AFTER THAT* and *RESETS* $a back to $.

I *think* he just needs to put the initialization of $a, $b, etc. *BEFORE* that first call to theAfunction().

But maybe not. I don't follow the logic (or illogic) of this page, at all.

Prime8 · 12-10-2012, 11:43 PM

You've nailed it Old Pedant, but for the last part, where I call the function is where it falls over. I could, in that function set $a = 'something' just not a $_POST[]. With php, $_POST causes the parser to expect there to be some data that was sent to the server using the same method, and tries to return it. I may have confused the issue there, but my intent was to show the hoped-for end-result.

The problem is basically that I have data fetched from a table using php, but I'd like to perform client side filtering on it via javascript (edit: well, preferably php but from the responses here js looks like the best bet). I need some tool to requery the data client side after it is fetched with php. AJAX will apparently do it (as BubikolRamios) pointed out, but I'm considering JQuery as well. I've been putting off tackling JQuery, so this might be a good time to start.

Old Pedant · 12-11-2012, 12:38 AM

Ummm...AJAX doesn't do any filtering unless you write it. For that matter, neither does jQuery.

More than likely, you *SHOULD* do the filtering in PHP/MySQL.

Why not something simple:

Code:

$a = isset($_POST["a") ) ? $_POST["a"] : '%'; 
$b = isset($_POST["b") ) ? $_POST["b"] : '%';

??

Usually, people send AJAX requests as part of the query string, though, so then you would use:

Code:

$a = isset($_GET["a") ) ? $_GET["a"] : '%'; 
$b = isset($_GET["b") ) ? $_GET["b"] : '%';

You see it? If there's no a=value in the querystring (or in the post data, as the case may be), then $a defaults to %. If there is a value, $a receives it, same with $b.

It's probably a TINY bit more efficient to code those as

Code:

$a = $_GET["a"]; if ( ! isset($a) ) { $a = "%"; }
$b = $_GET["b"]; if ( ! isset($b) ) { $b = "%"; }

(or use POST instead of GET, of course).

Prime8 · 12-13-2012, 03:58 AM

Quote:

Originally Posted by Old Pedant

Ummm...AJAX doesn't do any filtering unless you write it. For that matter, neither does jQuery.

Yea this is the key, I think I'm going to have to write it. One of the things I want to do is filter the data without the need to send a new query to the server. I'll do some poking at it and see how easy it is with JQuery/AJAX, if I can't get it I'll fall back on posting form data.

Thanks as always Old Pedant.

Prime8 · 12-14-2012, 12:58 AM

So I've added a variable that I can increment in my while loop to give each <tr> and <td> a unique id and removed the calls to the php functions, I'm wondering if I can write the functions in javascript and do some logic to set the css display property of rows I want to hide. something like this:

PHP Code:


			
<?php
echo "<table>";
echo "<th><select name='A' id='A' onChange='theAFunction()'>
<option value='1'>1</option>
<option value='2'>2</option>
</select></th>";
echo "<th><input type='text' name='B' id='B' onChange='theBFunction()'></input></th>";

$a = '%'; // Setting the variables to match all the records first
$b = '%';

$filter = "SELECT * FROM table WHERE aye LIKE '" . $a . "' AND bee LIKE '" . $b . "' ORDER BY aye";

$result = mysql_query($filter);

$filterrow_i = 0;
$aye_i = 0;
$bee_i = 0;

while ($row = mysql_fetch_array($result))
{
echo "<tr id='rowfil_" . $filterrow_i . "'>";
echo "<td id='tda_" . $aye_i . "'>" . $row[aye] . "</td>";
echo "<td id='tdb_" . $bee_i . "'>" . $row[bee] . "</td>";
echo "</tr>";
$aye_i++;
$bee_i++;
}
echo "</table>";
?>

That part works so far, it gives each <tr> and <td> the correct id. The next step is to use those functions (now in my js) to do the logic and hide the rows I don't want to see.

Old Pedant · 12-14-2012, 01:31 AM

But what about re-ordering rows? That is, allowing the user to change the "sort order" of the data?

If you are going to do this, why not do it right?

The right way is to create an array of objects that JS code receives as same and then put in the sorting, etc., all in the JS code.

It's really not too hard.

Prime8 · 12-14-2012, 04:13 AM

Maybe (likely) I've missed something fundamental. What do you mean?

Old Pedant · 12-14-2012, 08:03 PM

Okay, I think you know I don't use PHP.

And I'm sure there is a better way to do what I am about to show you. In PHP, I think you can create an array of objects and then call a method that will produce the same array in JS code in JSON notation. But since I don't know how to do that, I'll use brute force.

But first, let's use a real world example, instead of your hokey "aye" and "bee".

Back later.

Prime8 · 12-14-2012, 08:14 PM

FWIW, I was able to achieve my goal (obviously without sorting) using jquery. I don't think it would have been much more difficult using vanilla js though. I wrote a function that loops through the rows, if the innerHTML of a column in the row doesn't match the selected index of the drop down it changes the class of the row and hides it.

This won't really make sense since I haven't modified it to match my example, but here it is for now:

Code:

function threadSearch()
	{
	var thfil = 0;
	var thS0 = document.getElementById('threadFilter');
	$("tr.rowhid").attr("class","rowfil"); //set the class of all the rows
	$("tr.rowfil").show(); //show all the rows
	for (var i=0;i=document.getElementById('a_th_' + thfil);i++)
		{
		var th2 = document.getElementById('a_th_' + thfil);
		if (thS0.options[thS0.selectedIndex].text !== th2.innerHTML)
			{
			//alert("200"); - used for testing
                        // change the class of the row I want to hide
			$("tr#rowfil_" + thfil + "").attr("class","rowhid");
			$("tr.rowhid").hide(); //hide the row
			// i++; superfluous
			thfil++;
                             if (thS0.options[thS0.selectedIndex].text == '')
					{
					$("tr.rowhid").attr("class","rowfil");
					$("tr.rowfil").show();
					}
			}
		else
			{
			// i++; superfluous
			thfil++;
			}
		}
	}

edit: added another 'if' statement in case the user sets the drop down to blank - behavior is to show all the records.

Prime8 · 12-14-2012, 08:42 PM

Here's my function edited to match the example:

Code:

function theAfunction()
	{
	var afil = 0;
	var thS0 = document.getElementById('A');
	$("tr.rowhid").attr("class","rowfil");
	$("tr.rowfil").show();
	for (var i=0;i=document.getElementById('tda_' + afil);i++)
		{
                var a2 = document.getElementById('tda_' + afil);
		if (thS0.options[thS0.selectedIndex].text !== a2.innerHTML)
			{
			//alert("200");
			$("tr#rowfil_" + afil + "").attr("class","rowhid");
			$("tr.rowhid").hide();
			//i++;
			afil++;
				if (thS0.options[thS0.selectedIndex].text == '')
					{
					$("tr.rowhid").attr("class","rowfil");
					$("tr.rowfil").show();
					}
			}
		else
			{
			//i++;
			afil++;
			}
		}
	}

Old Pedant · 12-14-2012, 09:17 PM

Okay...here is the HTML and JavaScript code. Have to go to Dr. appointment. Back later to show you how to integrate PHP into it.

In the mean time, copy/paste it to your own machine and try it out. Click on the column headings. Click the same one again. Change the <select> choice. Play with it.

Does it do what you would want?

Code:

<!DOCTYPE html>
<html>
<head>
<title>Sort and Filter Demo</title>
<style type="text/css">
thead th {
    background-color: lightblue;
    cursor: pointer;
}
</style>
</head>
<body>
<form>
Show only people in 
   <select id="filterDept">
       <option>All</option>
       <option>Admin</option>
       <option>Engineering</option>
       <option>Sales</option>
   </select> department(s)
<br/>
<table id="myData" border="1" cellpadding="5">
<thead>
    <tr>
        <th id="sortName">Name</th>
        <th id="sortDept">Department</th>
        <th id="sortStart">Start Date</th>
        <th id="sortSalary">Salary</th>
    </tr>
</thead>
<tbody></tbody>
</table>

<script type="text/javascript">
(
  function( )
  {
        // ********************************************************************
        // DATA DEFINITION 
        // ********************************************************************
        
        // Constructor for an Employee object...
        //
        function Employee( nm, dpt, sdate, sal )
        {
            this.EmployeeName = nm;
            this.Department   = dpt;
            sdate = sdate.split("/");
            this.StartDate    = new Date( +sdate[2], sdate[0]-1, +sdate[1] );
            this.Salary       = sal;
            // alert("Constructing Employee:\n" + nm + "\n" + dpt + "\n" + sdate + "\n" + sal);
        }

        // Use PHP (or ASP or JSP or whatever) to create an array of Employees
        //
        var allEmps = [ 
            new Employee('Ferris, Mary','Engineering','7/20/2008',87200),
            new Employee('Forrest, Phil','Sales','1/3/2005',48790),
            new Employee('Harrison, Michael','Admin','3/1/2011',53300.5),
            new Employee('James, Barry','Admin','4/3/2004',33420),
            new Employee('Johnson, Harold','Engineering','10/15/2000',82300),
            new Employee('Morris, Jane','Admin','5/20/2009',38764.12),
            new Employee('Roberts, Bob','Engineering','8/20/2004',91220),
            new Employee('Williams, John','Sales','2/15/2008',42502.73)    
        ];

        // ********************************************************************
        // SORTING CONTROLS
        // ********************************************************************
 
        // We need sort functions for each of the possible fields 
        // in a pair of Employee objects...
        //
        function sortByEmployeeName( em1, em2 )
        {
            if( em1.EmployeeName == em2.EmployeeName ) return 0;
            return ( em1.EmployeeName < em2.EmployeeName ) ? sortLT : sortGT;
        }
        function sortByDepartment( em1, em2 )
        {
            if( em1.Department == em2.Department ) return 0;
            return ( em1.Department < em2.Department ) ? sortLT : sortGT;
        }
        function sortByStartDate( em1, em2 )
        {
            if( em1.StartDate == em2.StartDate ) return 0;
            return ( em1.StartDate < em2.StartDate ) ? sortLT : sortGT;
        }
        function sortBySalary( em1, em2 )
        {
            if( em1.Salary == em2.Salary ) return 0;
            return ( em1.Salary < em2.Salary ) ? sortLT : sortGT;
        }

        // And then a "dictionary" of references to those functions
        // will simplify the "sortBy" code:
        //
        sortFuncs = {
            "sortName" : sortByEmployeeName, 
            "sortDept" : sortByDepartment, 
            "sortStart" : sortByStartDate, 
            "sortSalary" : sortBySalary   
        };

        // Helper function to make dates readable
        //
        mNames = new Array( " Jan "," Feb "," Mar "," Apr "," May "," Jun ",
                            " Jul "," Aug "," Sep "," Oct "," Nov "," Dec "  );
        function showDate( when )
        {
            return "" + when.getDate() + mNames[when.getMonth()] + when.getFullYear();
        }

        // This function first sorts the array of Employee objects
        // by the specified field and then displays the results
        // in the table body, filtering as needed in the process
        //
        // we remember last sort, so if it is hit again we do opposite sort...
        //
        var lastSortBy = "";   // but start with impossible value
        var sortGT = -1;   // sort forwards to start
        var sortLT = 1;  // these can get swapped for reverse sort
        var sortStyle = [" sorted <i>in reverse</i> by "," **impossible** "," sorted by "];

        var tbl = document.getElementById("myData");
        var tbody = tbl.getElementsByTagName("tbody")[0];

        function sortBy( )
        {
            doSort( this.id );
        }
        
        function doSort( fldName )
        {
        
            // first, clear out any existing rows in the table body:
            while ( tbody.hasChildNodes( ) )
            {
                tbody.removeChild( tbody.lastChild );
            } 
            // check to see if we need to reverse sort prior sort...
            if ( fldName != lastSortBy )
            {
                
                // no...so set the sort return values to defaults...
                sortGT = -1; 
                sortLT = 1;
                lastSortBy = fldName; // remember this
            } else {
                // resorting on same field...
                sortGT = - sortGT;
                sortLT = - sortLT; // reverse the sort
            }

            // sort using the appropriate function
            allEmps.sort( sortFuncs[fldName] );
            
            // get the filter value, if any:
            var sel = document.getElementById("filterDept");
            var filter = null;
            if ( sel.selectedIndex != 0 ) { filter = sel.options[sel.selectedIndex].text; }
            
            // and display the results! 
            for ( var rownum = 0; rownum < allEmps.length; ++rownum )
            {
                var curItem = allEmps[rownum];
                if ( filter == null || filter == curItem.Department )
                {
                    var row = tbody.insertRow();
                    var cell1 = row.insertCell(-1);
                    cell1.innerHTML = curItem.EmployeeName;
                    var cell2 = row.insertCell(-1);
                    cell2.innerHTML = curItem.Department;
                    var cell3 = row.insertCell(-1);
                    cell3.innerHTML = showDate(curItem.StartDate);
                    var cell4 = row.insertCell(-1);
                    cell4.innerHTML = "$" + curItem.Salary.toFixed(2);
                }
            }
        }
        
        // *******************************************************
        // ATTACH EVENT HANDLERS 
        // *******************************************************
        document.getElementById("sortName").onclick = sortBy;
        document.getElementById("sortDept").onclick = sortBy;
        document.getElementById("sortStart").onclick = sortBy;
        document.getElementById("sortSalary").onclick = sortBy;
     
        document.getElementById("filterDept").onchange = function( )
        {
            // force a sort by same as last field, but in same ordering:
            var sName = lastSortBy;
            lastSortBy = "";
            doSort( sName );
        }        

        // *******************************************************
        // get things started:
        // *******************************************************
        doSort("sortName");

  }
)( );
</script>
</body>
</html>

Old Pedant · 12-15-2012, 02:42 AM

Okay, so the only part of all that code that PHP needs to be concerned with is here:

Code:

        // Use PHP (or ASP or JSP or whatever) to create an array of Employees
        //
        var allEmps = [
            new Employee('Ferris, Mary','Engineering','7/20/2008',87200),
            new Employee('Forrest, Phil','Sales','1/3/2005',48790),
            new Employee('Harrison, Michael','Admin','3/1/2011',53300.5),
            new Employee('James, Barry','Admin','4/3/2004',33420),
            new Employee('Johnson, Harold','Engineering','10/15/2000',82300),
            new Employee('Morris, Jane','Admin','5/20/2009',38764.12),
            new Employee('Roberts, Bob','Engineering','8/20/2004',91220),
            new Employee('Williams, John','Sales','2/15/2008',42502.73)    
        ];

And that's not hard.

Again, remembering that I don't use PHP, it should go something like this:

Code:

... all the HTML and JavaScript above this point is untouched ...
        // Use PHP (or ASP or JSP or whatever) to create an array of Employees
        //
        var allEmps = [
<?php
... make your db connection here ...

$sql = "SELECT name, department, DATE_FORMAT(startdate,'%e/%m/%Y') AS start, salary FROM employees";
$result = mysql_query($sql) or die( mysql_error() );

$first = true;
while ($row = mysql_fetch_array($result)
{
    if ( ! $first ) 
    { 
        // for all except first row, end the prior row with comma and newline
        echo ",\n"; 
    }
    // get the data
    $first = false;
    $name = $row["name"];
    $dept = $row["department"];
    $start = $row["start"];
    $salary = $row["salary"];
    // output one Employee

    echo "    new Employee('$name','$dept','$start',$salary)";
}
// end of PHP code!
?>
        ]; // end of the JS allEmps array of Employee objects

... rest of the JS/HTML code unchanged ...

And now you have your web page that allows you to sort on any column in either direction and to filter results.

Naturally, it would be easy to add more filters, if you wanted.