filter within result set

[mgreiner28]

I currently have a search form on one page that will look up various restaurants within city limits. Once submitted, you will be directed to a second page with the results displayed within a table.

I need to filter based on the results that are produced from the overall search form. So let's say, I search for "restaurant name". The results are displayed with two columns, one being "Restaurant Name" and the other being "City". What I need the filter to do, I need it to refine my search by city within that result set. I have created a simple form to let the user enter a text search, How do I query the search correctly in order to get the result that I need?

 <?php

      $connection=mysql_connect('***','****','****') or die(mysql_error());
      mysql_select_db('****',$connection) or die(mysql_error());

      $per_page = 20;
      $adjacents = 5; 

      $filter = $_GET['filter'];

      $pages_query = mysql_query("SELECT COUNT('id') FROM broadway") or die(mysql_error());

      $pages = ceil(mysql_result($pages_query, 0) / $per_page);

      $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1 ;

      $start = ($page - 1) * $per_page;

      $filter = mysql_real_escape_string($filter);


      $query = mysql_query("SELECT * FROM broadway WHERE TYPE = 'Food Service Establishment' AND LOCATE('".$filter."', Name) <> 0 AND LOCATE('".$filter2."', PCITY) <>0 ORDER BY Name ASC, EDATE ASC LIMIT $start, $per_page") or die(mysql_error());


      echo "<table  data-toggle='table' data-sort-name='name' data-sort-order='desc' >";
      echo "<thead>";
      echo "<tr>";
      echo "<th data-sortable='true'>Date</th><th data-field='Name' data-align='left' data-sortable='true'>Name</th><th>City</th><th>Description</th><th>Description</th>";
      echo "</tr>";
      echo "</thead>";

      while($row = mysql_fetch_assoc($query)){
        echo "<tr>";
        echo "<td>{$row['EDATE']}</td>";
        echo "<td>{$row['Name']}</td>";
        echo "<td>{$row['PCITY']}</td>";
        echo "<td>{$row['CODE']}</td>";
        echo  '<td><a class="description" href="/results2.php?nameID=' .$row['ID'].'">' . substr($row['DESCR'], 0, 35) . '</a></td>';
        }

      echo "</table>";
      ?>

      <?php 

[diafol]

A better solution may be to have two text boxes one for name and one for city. The problem with filtering on a "sent" set, is that it's just that - "sent" and forgotten about by php. So you'd need to make a separate search for name and city (2nd call) anyhow.

There are some javascript solutions that take the recordset (in json format) and you can filter the resultset on that, but if you have hundreds or thousands of returned results, this becomes very unwieldy. If you limit the resultset to say a hundred, then it may be useful. Dunno.

[mgreiner28]

Do you by any chance have an js resources that you recommend?

[diafol]

Not off top of my head sorry. It should be straightforward to build a solution one from a JSON object and some HTML template (possibly using Mustache / Handlebars etc).

Have a look at DataTables - I've not used it.

https://www.datatables.net/

[mgreiner28]

great! thank you, I appreciate it!