Loading ...
Sorry, an error occurred while loading the content.

Simple Datatable Filter Example

Expand Messages
  • mgossmer
    Hi, is there a simple example out there, showing datatable filtering based on an onchange in a select dropdown, without reloading the site? Regards Markus
    Message 1 of 9 , Jun 2, 2007
    • 0 Attachment
      Hi,

      is there a simple example out there, showing datatable filtering based
      on an "onchange" in a select dropdown, without reloading the site?


      Regards
      Markus
    • Ted Husted
      There an example that uses autocomplete to filter a DataTable w/o reloading. * http://yuiblog.com/sandbox/yui/v222/examples/datatable/filterRows.php HTH, Ted.
      Message 2 of 9 , Jun 2, 2007
      • 0 Attachment
        There an example that uses autocomplete to filter a DataTable w/o
        reloading.

        * http://yuiblog.com/sandbox/yui/v222/examples/datatable/filterRows.php

        HTH, Ted.
        http://yazaar.org/

        --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@...> wrote:
        >
        > Hi,
        >
        > is there a simple example out there, showing datatable filtering based
        > on an "onchange" in a select dropdown, without reloading the site?
        >
        >
        > Regards
        > Markus
        >
      • mgossmer
        Hi, I know this example, but I asked myself if there may be a much more simple example then this, something without autocomplete, dpu ... - for example
        Message 3 of 9 , Jun 2, 2007
        • 0 Attachment
          Hi,

          I know this example, but I asked myself if there may be a much more
          simple example then this, something without autocomplete, dpu ... -
          for example filtering a table just based upon a value selected from a
          <select> box.


          Regards
          Markus



          --- In ydn-javascript@yahoogroups.com, "Ted Husted" <husted@...> wrote:
          >
          > There an example that uses autocomplete to filter a DataTable w/o
          > reloading.
          >
          > * http://yuiblog.com/sandbox/yui/v222/examples/datatable/filterRows.php
          >
          > HTH, Ted.
          > http://yazaar.org/
          >
          > --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@> wrote:
          > >
          > > Hi,
          > >
          > > is there a simple example out there, showing datatable filtering based
          > > on an "onchange" in a select dropdown, without reloading the site?
          > >
          > >
          > > Regards
          > > Markus
          > >
          >
        • Matt Howey
          In MySQL I would maybe use a query like this: SELECT DISTINCT name FROM events WHERE MATCH (column1, column2) AGAINST ( .$GET[ param ]. * IN BOOLEAN MODE)
          Message 4 of 9 , Jun 2, 2007
          • 0 Attachment
            In MySQL I would maybe use a query like this:

            SELECT DISTINCT name FROM events WHERE MATCH (column1, column2)
            AGAINST ('".$GET['param']."*' IN BOOLEAN MODE) LIMIT 60;


            So basically, the SQL query is searching "column1" and "column2" for
            any word in either of those columns that matches our GET request that
            was sent to the script (AJAX using a YUI XHR DataSource...).

            I believe this query will work on MySQL 4.1.2 and higher as that is
            when the MATCH AGAINST showed up.

            Please, by all means, if anyone has a faster way with PHP/MySQL to do
            this pattern match, throw it out!

            Hope this helps someone,

            Matt Howey


            --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@...>
            wrote:
            >
            > Hi,
            >
            > I know this example, but I asked myself if there may be a much more
            > simple example then this, something without autocomplete, dpu ... -
            > for example filtering a table just based upon a value selected from
            a
            > <select> box.
            >
            >
            > Regards
            > Markus
            >
            >
            >
            > --- In ydn-javascript@yahoogroups.com, "Ted Husted" <husted@> wrote:
            > >
            > > There an example that uses autocomplete to filter a DataTable w/o
            > > reloading.
            > >
            > > *
            http://yuiblog.com/sandbox/yui/v222/examples/datatable/filterRows.php
            > >
            > > HTH, Ted.
            > > http://yazaar.org/
            > >
            > > --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@>
            wrote:
            > > >
            > > > Hi,
            > > >
            > > > is there a simple example out there, showing datatable
            filtering based
            > > > on an "onchange" in a select dropdown, without reloading the
            site?
            > > >
            > > >
            > > > Regards
            > > > Markus
            > > >
            > >
            >
          • mgossmer
            Actually the scenario is even more easy: assume that the maximum set of records is already and completely loaded (as a HTML table, on which the datatable
            Message 5 of 9 , Jun 2, 2007
            • 0 Attachment
              Actually the scenario is even more easy: assume that the maximum set
              of records is already and completely loaded (as a HTML table, on which
              the datatable component renders). For example, imagine a table with
              100 products, their name and their price. Now, the user should be able
              to use a simple select box to filter these products via a "less then
              50 dollar", "less then 100 dollar" selection etc.

              This might lead to something using the onchange event in the select
              box, calling some funtions on the datatable component - but which
              functions in what manner? All I found were high-end oversized examples
              in conjunction with AutoComplete.


              --- In ydn-javascript@yahoogroups.com, "Matt Howey" <matthewhowey@...>
              wrote:
              >
              > In MySQL I would maybe use a query like this:
              >
              > SELECT DISTINCT name FROM events WHERE MATCH (column1, column2)
              > AGAINST ('".$GET['param']."*' IN BOOLEAN MODE) LIMIT 60;
              >
              >
              > So basically, the SQL query is searching "column1" and "column2" for
              > any word in either of those columns that matches our GET request that
              > was sent to the script (AJAX using a YUI XHR DataSource...).
              >
              > I believe this query will work on MySQL 4.1.2 and higher as that is
              > when the MATCH AGAINST showed up.
              >
              > Please, by all means, if anyone has a faster way with PHP/MySQL to do
              > this pattern match, throw it out!
              >
              > Hope this helps someone,
              >
              > Matt Howey
              >
              >
              > --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@>
              > wrote:
              > >
              > > Hi,
              > >
              > > I know this example, but I asked myself if there may be a much more
              > > simple example then this, something without autocomplete, dpu ... -
              > > for example filtering a table just based upon a value selected from
              > a
              > > <select> box.
              > >
              > >
              > > Regards
              > > Markus
              > >
              > >
              > >
              > > --- In ydn-javascript@yahoogroups.com, "Ted Husted" <husted@> wrote:
              > > >
              > > > There an example that uses autocomplete to filter a DataTable w/o
              > > > reloading.
              > > >
              > > > *
              > http://yuiblog.com/sandbox/yui/v222/examples/datatable/filterRows.php
              > > >
              > > > HTH, Ted.
              > > > http://yazaar.org/
              > > >
              > > > --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@>
              > wrote:
              > > > >
              > > > > Hi,
              > > > >
              > > > > is there a simple example out there, showing datatable
              > filtering based
              > > > > on an "onchange" in a select dropdown, without reloading the
              > site?
              > > > >
              > > > >
              > > > > Regards
              > > > > Markus
              > > > >
              > > >
              > >
              >
            • Marcus Engene
              Hi, Sorry for stating the obvious here but if db newcomers read this they might have some trouble in the pipe. All data should be escaped using the escape
              Message 6 of 9 , Jun 2, 2007
              • 0 Attachment
                Hi,

                Sorry for stating the obvious here but if db newcomers read this they
                might have some trouble in the pipe.

                All data should be escaped using the escape function from the vendor in
                question. In this case it should be something like...

                $dbquery = "SELECT DISTINCT name FROM events WHERE MATCH (column1,
                column2) " .
                "AGAINST ('" . mysql_escape_string($_GET['param']) . "*' IN BOOLEAN
                MODE) LIMIT 60;";

                Do not rely on the automatic escaping thing (addslashes/stripslashes)
                magic_quotes_gpc <ref.info.html#ini.magic-quotes-gpc> because it is not
                covering everything and it is going away in newer PHP versions.

                Or better yet, use bind variables. That is, one has a placeholder for
                the variable in the query and then provide the variable separately. I
                use postgres, which should be similar, and there it looks something like...

                $dbquery = " select a from b where c = $1 and e = $2 ";
                pg_query_params ($database, $dbquery, array('apa', 'banan'));

                ...using PHP 5.1.x+, and you can sleep well ever after. On some
                databases (f.ex Oracle) this is also faster when called multiple times
                since oracle caches the Parse and Optimizer for queries. See f.ex:
                http://www.engene.se/oracle_perf.txt

                Google for sql injection for more info.

                Best regards,
                Marcus

                Matt Howey wrote:
                > In MySQL I would maybe use a query like this:
                >
                > SELECT DISTINCT name FROM events WHERE MATCH (column1, column2)
                > AGAINST ('".$GET['param']."*' IN BOOLEAN MODE) LIMIT 60;
                >
                >
                > So basically, the SQL query is searching "column1" and "column2" for
                > any word in either of those columns that matches our GET request that
                > was sent to the script (AJAX using a YUI XHR DataSource...).
                >
                > I believe this query will work on MySQL 4.1.2 and higher as that is
                > when the MATCH AGAINST showed up.
                >
                > Please, by all means, if anyone has a faster way with PHP/MySQL to do
                > this pattern match, throw it out!
                >
                > Hope this helps someone,
                >
                > Matt Howey
                >
                >
              • Ted Husted
                ... Are you asking about filtering the records after they have been retrieved, or using the select box to obtain input that would filter the list returned from
                Message 7 of 9 , Jun 2, 2007
                • 0 Attachment
                  --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@...>
                  > wrote:
                  > I know this example, but I asked myself if there may be a much more
                  > simple example then this, something without autocomplete, dpu ... -
                  > for example filtering a table just based upon a value selected from
                  > a
                  > <select> box.

                  Are you asking about filtering the records after they have been
                  retrieved, or using the select box to obtain input that would filter
                  the list returned from the database?

                  If it's the former, the DataTable is backed by a RecordSet, and the
                  system could scroll through the RecordSet and delete whatever record
                  didn't meet the criteria. Or, it could create a new RecordSet and copy
                  every record that met the criteria, and then replace the DataTable's
                  RecordSet. The RowFilter example does the latter, and it also keeps
                  the original RecordSet cached, so it can undo the filter. The overall
                  architecture would still be very similar to the RowFilter example.

                  -Ted.
                • mgossmer
                  Hmm, no ideas?
                  Message 8 of 9 , Jun 3, 2007
                  • 0 Attachment
                    Hmm, no ideas?


                    --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@...> wrote:
                    >
                    > Actually the scenario is even more easy: assume that the maximum set
                    > of records is already and completely loaded (as a HTML table, on which
                    > the datatable component renders). For example, imagine a table with
                    > 100 products, their name and their price. Now, the user should be able
                    > to use a simple select box to filter these products via a "less then
                    > 50 dollar", "less then 100 dollar" selection etc.
                    >
                    > This might lead to something using the onchange event in the select
                    > box, calling some funtions on the datatable component - but which
                    > functions in what manner? All I found were high-end oversized examples
                    > in conjunction with AutoComplete.
                    >
                    >
                    > --- In ydn-javascript@yahoogroups.com, "Matt Howey" <matthewhowey@>
                    > wrote:
                    > >
                    > > In MySQL I would maybe use a query like this:
                    > >
                    > > SELECT DISTINCT name FROM events WHERE MATCH (column1, column2)
                    > > AGAINST ('".$GET['param']."*' IN BOOLEAN MODE) LIMIT 60;
                    > >
                    > >
                    > > So basically, the SQL query is searching "column1" and "column2" for
                    > > any word in either of those columns that matches our GET request that
                    > > was sent to the script (AJAX using a YUI XHR DataSource...).
                    > >
                    > > I believe this query will work on MySQL 4.1.2 and higher as that is
                    > > when the MATCH AGAINST showed up.
                    > >
                    > > Please, by all means, if anyone has a faster way with PHP/MySQL to do
                    > > this pattern match, throw it out!
                    > >
                    > > Hope this helps someone,
                    > >
                    > > Matt Howey
                    > >
                    > >
                    > > --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@>
                    > > wrote:
                    > > >
                    > > > Hi,
                    > > >
                    > > > I know this example, but I asked myself if there may be a much more
                    > > > simple example then this, something without autocomplete, dpu ... -
                    > > > for example filtering a table just based upon a value selected from
                    > > a
                    > > > <select> box.
                    > > >
                    > > >
                    > > > Regards
                    > > > Markus
                    > > >
                    > > >
                    > > >
                    > > > --- In ydn-javascript@yahoogroups.com, "Ted Husted" <husted@> wrote:
                    > > > >
                    > > > > There an example that uses autocomplete to filter a DataTable w/o
                    > > > > reloading.
                    > > > >
                    > > > > *
                    > > http://yuiblog.com/sandbox/yui/v222/examples/datatable/filterRows.php
                    > > > >
                    > > > > HTH, Ted.
                    > > > > http://yazaar.org/
                    > > > >
                    > > > > --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@>
                    > > wrote:
                    > > > > >
                    > > > > > Hi,
                    > > > > >
                    > > > > > is there a simple example out there, showing datatable
                    > > filtering based
                    > > > > > on an "onchange" in a select dropdown, without reloading the
                    > > site?
                    > > > > >
                    > > > > >
                    > > > > > Regards
                    > > > > > Markus
                    > > > > >
                    > > > >
                    > > >
                    > >
                    >
                  • Ted Husted
                    ... One common scenario might be to filter a DataTable based on the distinct values in a given field. For example, if there is a large list of employees, there
                    Message 9 of 9 , Jun 3, 2007
                    • 0 Attachment
                      --- In ydn-javascript@yahoogroups.com, "mgossmer" <mgossmer@...> wrote:
                      >
                      > Hmm, no ideas?

                      One common scenario might be to filter a DataTable based on the
                      distinct values in a given field. For example, if there is a large list of
                      employees, there might be a select box with the unique last names. One
                      could then select "Gossmer" to filter the list to all employees named
                      Gossmer. Likewise, another column might indicate home office, in which
                      case we could filter for all employees in the "Tuscon" office or the
                      "Delhi" office.

                      In either case, the underlying RecordSet would not be replaced, but a
                      new "filtered" RecordSet created. If the filter is relaxed, then the
                      original RecordSet could be restored.

                      Likewise, the select box could be populated from the original
                      RecordSet. One might be able to mark a column "filterable" in the same
                      we mark a column "sortable". The system could automatically create a
                      list of distinct entries by looping through the RecordSet.

                      I expect that the architecture would be similar to the RowFilter
                      example, except that instead of using the autocomplete to filter the
                      rows, we'd apply a simplier test, like the one used to sort columns.

                      I haven't yet seen an example like this, but I think it could
                      represent a common use case. It would be certainly worthwhile to post
                      a "distinct filter" example in the YUI Sandbox or on Planet Yazaar, if
                      someone were to code it.

                      HTH, Ted.
                      http://planetyazaar.org/
                    Your message has been successfully submitted and would be delivered to recipients shortly.