Crud with php ajax

Question

I have a table that I want to customize and leaves it a complete crud, with delete, Insert, update and delete multiple records, 1- I wanted to see if it could make this table compatible with jquery 2.2.0 or a more advanced jquery because it only accepts 1.8.1, i was able to insert php com while na td, but the search does not search on all pages of the table, only search on the page where the record is the sample, I mean, I have to next to search on the other page!

    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.1/jquery.min.js"></script>
    <script src="jquery.tablesorter.min.js"></script>
    <script src="jquery.tablesorter.pager.js"></script>
        <link rel="stylesheet" href="custom.css" media="screen" />



    <form method="post" action="../melhorando-exibicao-tabelas-jquery/exemplo.html" id="frm-filtro">
  <p>
    <label for="pesquisar">Pesquisar</label>
    <input type="text" id="pesquisar" name="pesquisar" size="30" />
  </p>
</form>

<table cellspacing="0" summary="Tabela de dados fictícios">
  <thead>
    <tr>
      <th><input type="checkbox" value="1" id="marcar-todos" name="marcar-todos" /></th>
      <th>ID</th>
      <th>Nome</th>
    </tr>
  </thead>
  <tbody>
  <?php
    require_once 'dbconfig.php';
    $conn = Conexao::getInstance();
    $stmt = $conn->prepare("SELECT * FROM tb_cidades ORDER BY id DESC");
    $stmt->execute();
    while($row=$stmt->fetch(PDO::FETCH_ASSOC))
    {
        ?>
    <tr>
      <td><input type="checkbox" value="1" name="marcar[]" /></td>
                <td><?= $row['id']; ?></td>
      <td><?= $row['nome']; ?></td>

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

<div id="pager" class="pager">
    <form>
            <span>
                Exibir <select class="pagesize">
                        <option selected="selected"  value="10">10</option>
                        <option value="20">20</option>
                        <option value="30">30</option>
                        <option  value="40">40</option>
                </select> registros
            </span>

            <img src="first.png" class="first"/>
        <img src="prev.png" class="prev"/>
        <input type="text" class="pagedisplay"/>
        <img src="next.png" class="next"/>
        <img src="last.png" class="last"/>
    </form>
</div>

<script>
$(function(){

  $('table > tbody > tr:odd').addClass('odd');

  $('table > tbody > tr').hover(function(){
    $(this).toggleClass('hover');
  });

  $('#marcar-todos').click(function(){
    $('table > tbody > tr > td > :checkbox')
      .attr('checked', $(this).is(':checked'))
      .trigger('change');
  });

  $('table > tbody > tr > td > :checkbox').bind('click change', function(){
    var tr = $(this).parent().parent();
    if($(this).is(':checked')) $(tr).addClass('selected');
    else $(tr).removeClass('selected');
  });

  $('form').submit(function(e){ e.preventDefault(); });

  $('#pesquisar').keydown(function(){
    var encontrou = false;
    var termo = $(this).val().toLowerCase();
    $('table > tbody > tr').each(function(){
      $(this).find('td').each(function(){
        if($(this).text().toLowerCase().indexOf(termo) > -1) encontrou = true;
      });
      if(!encontrou) $(this).hide();
      else $(this).show();
      encontrou = false;
    });
  });

  $("table") 
    .tablesorter({
      dateFormat: 'uk',
      headers: {
        0: {
          sorter: false
        },
        5: {
          sorter: false
        }
      }
    }) 
    .tablesorterPager({container: $("#pager")})
    .bind('sortEnd', function(){
      $('table > tbody > tr').removeClass('odd');
      $('table > tbody > tr:odd').addClass('odd');
    });

});
</script>

jquery.tablesorter.pager.js

(function($) {
    $.extend({
        tablesorterPager: new function() {

            function updatePageDisplay(c) {
                var s = $(c.cssPageDisplay,c.container).val((c.page+1) + c.seperator + c.totalPages);   
            }

            function setPageSize(table,size) {
                var c = table.config;
                c.size = size;
                c.totalPages = Math.ceil(c.totalRows / c.size);
                c.pagerPositionSet = false;
                moveToPage(table);
                fixPosition(table);
            }

            function fixPosition(table) {
                var c = table.config;
                if(!c.pagerPositionSet && c.positionFixed) {
                    var c = table.config, o = $(table);
                    if(o.offset) {
                        c.container.css({
                            top: o.offset().top + o.height() + 'px',
                            position: 'absolute'
                        });
                    }
                    c.pagerPositionSet = true;
                }
            }

            function moveToFirstPage(table) {
                var c = table.config;
                c.page = 0;
                moveToPage(table);
            }

            function moveToLastPage(table) {
                var c = table.config;
                c.page = (c.totalPages-1);
                moveToPage(table);
            }

            function moveToNextPage(table) {
                var c = table.config;
                c.page++;
                if(c.page >= (c.totalPages-1)) {
                    c.page = (c.totalPages-1);
                }
                moveToPage(table);
            }

            function moveToPrevPage(table) {
                var c = table.config;
                c.page--;
                if(c.page <= 0) {
                    c.page = 0;
                }
                moveToPage(table);
            }


            function moveToPage(table) {
                var c = table.config;
                if(c.page < 0 || c.page > (c.totalPages-1)) {
                    c.page = 0;
                }

                renderTable(table,c.rowsCopy);
            }

            function renderTable(table,rows) {

                var c = table.config;
                var l = rows.length;
                var s = (c.page * c.size);
                var e = (s + c.size);
                if(e > rows.length ) {
                    e = rows.length;
                }


                var tableBody = $(table.tBodies[0]);

                // clear the table body

                $.tablesorter.clearTableBody(table);

                for(var i = s; i < e; i++) {

                    //tableBody.append(rows[i]);

                    var o = rows[i];
                    var l = o.length;
                    for(var j=0; j < l; j++) {

                        tableBody[0].appendChild(o[j]);

                    }
                }

                fixPosition(table,tableBody);

                $(table).trigger("applyWidgets");

                if( c.page >= c.totalPages ) {
                    moveToLastPage(table);
                }

                updatePageDisplay(c);
            }

            this.appender = function(table,rows) {

                var c = table.config;

                c.rowsCopy = rows;
                c.totalRows = rows.length;
                c.totalPages = Math.ceil(c.totalRows / c.size);

                renderTable(table,rows);
            };

            this.defaults = {
                size: 10,
                offset: 0,
                page: 0,
                totalRows: 0,
                totalPages: 0,
                container: null,
                cssNext: '.next',
                cssPrev: '.prev',
                cssFirst: '.first',
                cssLast: '.last',
                cssPageDisplay: '.pagedisplay',
                cssPageSize: '.pagesize',
                seperator: "/",
                positionFixed: true,
                appender: this.appender
            };

            this.construct = function(settings) {

                return this.each(function() {   

                    config = $.extend(this.config, $.tablesorterPager.defaults, settings);

                    var table = this, pager = config.container;

                    $(this).trigger("appendCache");

                    config.size = parseInt($(".pagesize",pager).val());

                    $(config.cssFirst,pager).click(function() {
                        moveToFirstPage(table);
                        return false;
                    });
                    $(config.cssNext,pager).click(function() {
                        moveToNextPage(table);
                        return false;
                    });
                    $(config.cssPrev,pager).click(function() {
                        moveToPrevPage(table);
                        return false;
                    });
                    $(config.cssLast,pager).click(function() {
                        moveToLastPage(table);
                        return false;
                    });
                    $(config.cssPageSize,pager).change(function() {
                        setPageSize(table,parseInt($(this).val()));
                        return false;
                    });
                });
            };

        }
    });
    // extend plugin scope
    $.fn.extend({
        tablesorterPager: $.tablesorterPager.construct
    });

})(jQuery);             

someone has an idea how to do?

Answer 1

just search on the page where the record is the sample, ie I I have to next to search on the other page!

This is really expensive, because the search is only on Frontend. You have to do this search on backend.

For this, you must use a plugin that accepts filter with ajax, see:

https://mottie.github.io/tablesorter/docs/example-pager-ajax.html