Search with multiple filters

Asked

Viewed 210 times

1

I have this structure, how can I do so that I can have a select that gives me higher or lower price options? this script it only lists the data by category, I do not know how to implement so you have the possibility to sort prices, besides it seems 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>
      function makeTable(data){
       var tbl_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 javascript mysql

  • Hello Otavio Fagundes, I wonder if my answer has helped you.

1 answer

0

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

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

SELECT * FROM mobile_phones WHERE price ORDER BY ASC/DESC

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

DESC Classifies the defined result in descending order.

While mounting your SQL code into the file Submit.php, the additional implementation you should do would be to adapt the PHP code to make this query when necessary.

Browser other questions tagged php javascript mysql

You are not signed in. Login or sign up in order to post.