Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    If you don't want to submit form use AJAX
    Last edited by BubikolRamios; 12-09-2012 at 09:45 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,354
    Thanks
    23
    Thanked 618 Times in 617 Posts
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Quote Originally Posted by sunfighter View Post
    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.

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

    WHERE aye LIKE '%' ... makes no sense.
    Yes, it does. He is simply *testing* the logic. As he stated
    The default display is all of the records ...
    and
    $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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sunfighter (12-11-2012)

  • #5
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.
    Last edited by Prime8; 12-10-2012 at 11:47 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    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).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #8
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maybe (likely) I've missed something fundamental. What do you mean?
    Last edited by Prime8; 12-14-2012 at 04:16 AM.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.
    Last edited by Prime8; 12-14-2012 at 08:20 PM.

  • #13
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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++;
    			}
    		}
    	}
    Last edited by Prime8; 12-14-2012 at 08:48 PM.

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    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>
    Last edited by Old Pedant; 12-15-2012 at 02:31 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •