Search with multiple filters

Question

Search with multiple filters

Navigation

#1 by (1 votes)

0

I have this structure, how can I do it so that I can have a select that gives me higher or lower price options? this script it just lists the data by category, I do not know how to implement so that it has the possibility to sort the prices, in addition it seems to only work with checkbox:

Database:

CREATE TABLE IF NOT EXISTS 'mobile_phones' (
 'id' int(11) NOT NULL AUTO_INCREMENT,
 'name' varchar(255) DEFAULT NULL,
 'price' int(11) DEFAULT NULL,
 'samsung' tinyint(1) DEFAULT NULL,
 'iphone' tinyint(1) DEFAULT NULL,
 'htc' tinyint(1) DEFAULT NULL,
 'lg' tinyint(1) DEFAULT NULL,
 'nokia' tinyint(1) DEFAULT NULL,
 PRIMARY KEY ('id')
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table:

INSERT INTO 'mobile_phones' ('id', 'name', 'price', 'samsung', 'iphone',
'htc', 'lg', 'nokia') VALUES
(1, 'Samsung Galaxy S 1', 180, 1, 0, 0, 0, 0),
(2, 'Samsung Galaxy S 2', 220, 1, 0, 0, 0, 0),
(3, 'Samsung Galaxy S 3', 300, 1, 0, 0, 0, 0),
(4, 'Samsung Galaxy S 4', 450, 1, 0, 0, 0, 0),
(5, 'Samsung Galaxy S 4 mini', 400, 1, 0, 0, 0, 0),
(6, 'Iphone 3GS', 150, 0, 1, 0, 0, 0),
(7, 'Iphone 4', 200, 0, 1, 0, 0, 0),
(8, 'Iphone 4S', 250, 0, 1, 0, 0, 0),
(9, 'Iphone 5', 300, 0, 1, 0, 0, 0),
(10, 'Iphone 5S', 350, 0, 1, 0, 0, 0),
(11, 'Htc Desire', 150, 0, 0, 1, 0, 0),
(12, 'Htc Desire200', 200, 0, 0, 1, 0, 0),
(13, 'Htc Desire500', 250, 0, 0, 1, 0, 0),
(14, 'Htc One', 400, 0, 0, 1, 0, 0),
(15, 'Htc One mini', 250, 0, 0, 1, 0, 0),
(16, 'Lg Optimus L3', 150, 0, 0, 0, 1, 0),
(17, 'Lg Optimus L5', 250, 0, 0, 0, 1, 0),
(18, 'Lg Optimus L7', 350, 0, 0, 0, 1, 0),
(19, 'Lg Optimus L9', 400, 0, 0, 0, 1, 0),
(20, 'Lg Optimus G2', 450, 0, 0, 0, 1, 0),
(21, 'Nokia 100', 50, 0, 0, 0, 0, 1),
(22, 'Nokia E72', 100, 0, 0, 0, 0, 1),
(23, 'Nokia E6', 150, 0, 0, 0, 0, 1),
(24, 'Nokia Lumia 520', 200, 0, 0, 0, 0, 1),
(25, 'Nokia Lumia 620', 250, 0, 0, 0, 0, 1);

submit.php:

<?php 
  $pdo = new PDO('mysql:host=localhost;dbname=sitepoint', 'root', '');
  $select = 'SELECT *';
  $from = ' FROM mobile_phones';
  $where = ' WHERE ';
  $opts = $_POST['filterOpts'];

  if (empty($opts)){
    // 0 checkboxes checked
    $where .= 'TRUE';
  } else {
    if(count($opts) == 1){
      // 1 checkbox checked
      $where .= $opts[0] . ' = 1';
    } else {
      // 2+ checkboxes checked
      $where .= implode(' = 1 OR ', $opts) . ' = 1';
    }
  }

  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode($results);
  echo($json);
?>

index.php:

<html>
  <head>
    <meta charset="utf-8">
    <title>AJAX filter demo</title>
    <style>
      body {
        padding: 10px;
      }

      h1 {
          margin: 0 0 0.5em 0;
          color: #343434;
          font-weight: normal;
          font-family: 'Ultra', sans-serif;   
          font-size: 36px;
          line-height: 42px;
          text-transform: uppercase;
          text-shadow: 0 2px white, 0 3px #777;
      }

      h2 {
          margin: 1em 0 0.3em 0;
          color: #343434;
          font-weight: normal;
          font-size: 30px;
          line-height: 40px;
          font-family: 'Orienta', sans-serif;
      }

      #phones {
        font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
        font-size: 12px;
        background: #fff;
        margin: 15px 25px 0 0;
        border-collapse: collapse;
        text-align: center;
        float: left;
        width: 700px;
      }

      #phones th {
        font-size: 14px;
        font-weight: normal;
        color: #039;
        padding: 10px 8px;
        border-bottom: 2px solid #6678b1;
      }

      #phones td {
        border-bottom: 1px solid #ccc;
        color: #669;
        padding: 8px 10px;
      }

      #phones tbody tr:hover td {
        color: #009;
      }

      #filter {
        float:left;
      }
    </style>
  </head>
  <body> 
    <h1>Phones database</h1>

    <table id="phones">
      <thead>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Price</th>
          <th>Samsung</th>
          <th>iPhone</th>
          <th>HTC</th>
          <th>LG</th>
          <th>Nokia</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>

    <div id="filter">
      <h2>Filter options</h2>
      <div>
        <input type="checkbox" id="samsung">
        <label for="samsung">Samsung</label>
      </div>
      <div>
        <input type="checkbox" id="iphone">
        <label for="iphone">iPhone</label>
      </div>
      <div>
        <input type="checkbox" id="htc">
        <label for="htc">HTC</label>
      </div>
      <div>
        <input type="checkbox" id="lg">
        <label for="lg">LG</label>
      </div>
      <div>
        <input type="checkbox" id="nokia">
        <label for="nokia">Nokia</label>
      </div>
    </div>

    <script src="http://code.jquery.com/jquery-latest.js"></script><script>functionmakeTable(data){vartbl_body="";
          $.each(data, function() {
            var tbl_row = "";
            $.each(this, function(k , v) {
              tbl_row += "<td>"+v+"</td>";
            })
            tbl_body += "<tr>"+tbl_row+"</tr>";                 
          })

        return tbl_body;
      }

      function getPhoneFilterOptions(){
        var opts = [];
        $checkboxes.each(function(){
          if(this.checked){
            opts.push(this.id);
          }
        });

        return opts;
      }

      function updatePhones(opts){
        $.ajax({
          type: "POST",
          url: "submit.php",
          dataType : 'json',
          cache: false,
          data: {filterOpts: opts},
          success: function(records){
            $('#phones tbody').html(makeTable(records));
          }
        });
      }

      var $checkboxes = $("input:checkbox");
      $checkboxes.on("change", function(){
        var opts = getPhoneFilterOptions();
        updatePhones(opts);
      });

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

php mysql javascript

asked by anonymous 18.08.2018 / 16:45

1 answer

Print BSON of MongoDB with several different structures Multiple bootstrap modal calls

score 1 · Answer 1

How can I do so that I can have a select that gives me higher or lower price options?

The answer to your question would be to use the ORDER BY ASC or DESC clause. Example:

SELECT * FROM mobile_phones WHERE price ORDER BY ASC/DESC

Tip: ASC Sorts the results in ascending order, if you do not add a value (asc or desc) by default the asc will be used.

DESC Sorts the result set in descending order.

During the assembly of your SQL code in the submit.php file, the additional implementation you should do would be to adapt the PHP code to make this query when needed.