SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 98
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2013
    Posts
    26
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Use AJAX to filter MySQL results with multiple checkbox option

    Hello,

    I have a tabel with different kind of phones, loaded from a MYSQL database (Ex: samsung,apple,htc,lg,nokia)...where the user have the possibility to filter this entries with multiple checkboxes. If the user click un "Samsung" the tabel will show only the phones from samsung, if the user click on "iphone" will show only the iphones.
    Everything is ok if the user select only one checkbox, my problem is ...if the user select multiple checkboxes, for example show all phones from "Samsung" AND "Apple" the code is not working anymore and nothing show up in the table.

    Here is my code:

    1. Create database table:


    Code MySQL:
    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;


    2. Populate the mysql table:

    Code MySQL:
    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);

    3. My index.php file

    Code HTML4Strict:
    <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>Temporary 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" name="samsung">
            <label for="car">samsung</label>
          </div>
          <div>
            <input type="checkbox" name="iphone">
            <label for="language">iphone</label>
          </div>
          <div>
            <input type="checkbox" name="htc">
            <label for="nights">htc</label>
          </div>
          <div>
            <input type="checkbox" id="4" name="lg">
            <label for="student">lg</label>
          </div>
    	        <div>
            <input type="checkbox" id="5" name="nokia">
            <label for="student">nokia</label>
          </div>
        </div>
     <script>
     
     </script>
        <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 getEmployeeFilterOptions(){
            var opts = [];
            $checkboxes.each(function(){
              if(this.checked){
                opts.push(this.name);
              }
            });
     
            return opts;
          }
     
          function updateEmployees(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 = getEmployeeFilterOptions();
            updateEmployees(opts);
          });
     
          updateEmployees();
        </script> 
      </body> 
    </html>


    4. My submit.php file

    Code HTML4Strict:
     
     <?php 
      $pdo = new PDO('mysql:host=localhost;dbname=test_database', 'root', '');
      $select = 'SELECT *';
      $from = ' FROM mobile_phones';
      $where = ' WHERE TRUE';
      $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
     
     
     
      if (in_array('samsung', $opts)){
        $where .= " AND samsung = 1 ";
      }
     
      if (in_array('iphone', $opts)){
        $where .= " AND iphone = 1 ";
      }
     
        if (in_array('htc', $opts)){
        $where .= " AND htc = 1 ";
      }
        if (in_array('lg', $opts)){
        $where .= " AND lg = 1 ";
      }
          if (in_array('nokia', $opts)){
        $where .= " AND nokia = 1 ";
      }
     
     
      $sql = $select . $from . $where;
      $statement = $pdo->prepare($sql);
      $statement->execute();
      $results = $statement->fetchAll(PDO::FETCH_ASSOC);
      $json = json_encode($results);
      echo($json);
    ?>


    Thank you

  2. #2
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Hi there,

    I just wanted to let you know that I saw your thread (you contacted me via my blog).
    I'm a bit tied up today, but will have a look at your question soon and post back here.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2013
    Posts
    26
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it`s ok,
    thanks

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,191
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Instead of AND use OR.

    Also, your phones table shouldn't really have a flag indicating brands. Those brands should be factored out into a separate table like brands than use a 1:m relationship to the phones table. Also, price should probably be something like MSRP considering price can vary based on distributor, sales, etc. Though that gets more into the business purpose of this project.
    The only code I hate more than my own is everyone else's.

  5. #5
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Hi there,

    Basically, oddz has given you the answer - use OR.

    However, there were a couple of things about your code that I have improved.

    • I know this is just a demo, but make the effort to name things properly. E.g. I have changed "updateEmployees" to "updatePhones"
    • I sorted out the ids for the checkboxes. This means that when you click on a label, it selects the checkbox.
    • Because of the way I had constructed the original $sql string in mm PHP code, it wasn't sufficient to just use OR instead of AND. I rewrote the PHP for you.

    Hopefully now it'll all work

    index.php

    Code:
    <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>
    submit.php

    Code:
    <?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);
    ?>
    @oddz ;
    PHP isn't my strongest suit and I can't help thinking that the way I build the $where part of the query is a bit clumsy.
    Could you think of a better way to do it?

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2013
    Posts
    26
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    tonight when i get home, i will try it.
    Thank you oddz for your reply, and thank you Pullo for making time to help me with this.

    (sorry for my bad english)

  7. #7
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    No probs!

    Quote Originally Posted by norbertfarkas View Post
    sorry for my bad english
    It's not bad
    Plus the fact that it's better than my <insert whichever language you speak here />

    Let us know how you get on.

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2013
    Posts
    26
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it`s probably a syntax error in the submit.php, because it`s not populate the table with the content from the MYSQL database ... but i don`t see the error after a long day of 11 hour work ) ...i will figure it out in the morning

  9. #9
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Hi, did you alter the database connection details?

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2013
    Posts
    26
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, i made that...
    i know is wrong ...but if i replace:

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


    with:

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


    the tabel is populated...but the checkbox filter is not workin...if a select a checkbox....nothing show up...

    is ther something wrong with the $opts ?

  11. #11
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Hi there,

    If you copied and pasted the code I posted above and altered the DB details accordingly, it should work for you.

    I'm tied up right now, but I'll post the demo online later on just to exclude that I made a mistake somewhere.

    I'm sure it's something simple

  12. #12
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,191
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    You really need to normalize those brands properly and not store them as separate columns. Otherwise, god help the next developer/you if another brand is ever needed. Something like adding another brand in a properly normalized database would take minutes where as with your approach would take days or weeks depending on the scale of what is being built. Just saying… if this is for a real-world project. Even if it isn't you should really be practicing the proper way to do things regardless. I know you asked a JavaScript question but there is a huge flaw in your database architecture that really needs to be corrected. Especially, if this is for a paying client. These are the types of amateur mistakes that want to make more junior/senior developers that might inherit something like this want to strangle the person who made such a decision because if things would have been properly done in the first place a significant amount of work could be avoided. Especially, if someone where to provide a quote for adding a brand without seeing the database and code. In general most developers who think it is a matter of simple adding a row to a table… only to learn that they have to add a column to a table than update god knows how many lines of code due to lack of skill of the previous developer. Even a client would probably wonder why it would take so long than I would have to somehow have to explain to them while being as professional as possible that the person who developed the database architecture was incompetent. I don't mean that in an offensive way we all start somewhere the real crime is knowing something is wrong and not acting on it. Especially, if this is something new and not a tangled mess of legacy code.
    The only code I hate more than my own is everyone else's.

  13. #13
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Ok, just to check I understood you correctly, do you mean move brands out into their own table and implement a 1:n relationship between phones and brands?

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,191
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Pullo View Post
    Ok, just to check I understood you correctly, do you mean move brands out into their own table and implement a 1:n relationship between phones and brands?
    of course

    something like:

    brands
    -----------
    id
    name

    phones
    -----------
    id
    brand_id
    name
    price

    There should also be a discussion at this point whether this is just for phones or might one day support multiple types of products. If supporting multiple types of products is not out of question than the database architecture should be further refined to support those requirements without a significant amount of overhaul. Again though all this tends to get into the business purpose of the entire program and I'm not even sure at this point if this is a real-world project or merely just a learning exercise.
    The only code I hate more than my own is everyone else's.

  15. #15
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    This whole thing started out life as a blog post which was meant to demonstrate how to use AJAX to send requests to a PHP script and display the results.
    For me this was never a real world project (I cannot speak for the OP), but that's immaterial in so far as you are correct that one should practice the proper way to do things regardless.

    When I get a minute, I'll update my solution to use the db architecture you suggest.

    Thanks for pointing this out!!

  16. #16
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    I'll jump in and support what @oddz ; says. It all boils down to indexing.

    The way the table is defined right now, for a simple brand filter to be reasonably fast it will need five indexes on brand fields. These indexes will also hurt every other aspect of the CRUD. And they will look cartoonish.

    Also, storing `0` for `null` values is a sign of poor understanding of data type nuances and differences.

    A personal opinion is that there is a plague of poor code-quality sub-contractors that gets the job done fishing for ready-made solutions in forums like this one.

    A personal wish is to also have higher level topics in this forums, to counter this plague, and to generate more meaningful knowledge and discussion. It will certainly make SPF more attractive. As it is, is more of a bus stop for homework on a plate.

    http://i.imgur.com/MgQGg.png

  17. #17
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Cheers @myty ; - you make some fair points
    I'll update my demo as soon as I have time, then maybe you could let me know what you think.

    @norbertfarkas ;
    Here's the demo as it stands.
    All I did was copy/paste my code from my previous post, so I guess there's something else at play on your end.

  18. #18
    SitePoint Enthusiast
    Join Date
    Dec 2013
    Posts
    26
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    everything is ok ... it was my mistake (as usual) ...
    wamp server, or XAMPP is not suporting AJAX ....after i uploaded the script online, everything is working fine with no errors.
    Thank you again, and happy new year to everyone !!!

  19. #19
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Hey Norbert,

    Both wamp and xampp do support AJAX, you must just make sure that you access your files via http://localhost/whatever.html and that you don't access them directly in your browser.

    Also, it is still on my todo list to incorporate oddz' and myty's suggestions into the answer (I just didn't get round to it yet), so please be sure to check back again soon

  20. #20
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    So then, as promised, here is an updated answer.

    We'll start off by altering our tables as oddz suggested:

    Code:
    CREATE TABLE IF NOT EXISTS `mobile_phone` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `model` varchar(255) DEFAULT NULL,
      `price` int(11) DEFAULT NULL,
      `brand_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    
    INSERT INTO `mobile_phone` (`id`, `model`, `price`, `brand_id`) VALUES
    (1, 'Galaxy S 1', 180, 1),
    (2, 'Galaxy S 2', 220, 1),
    (3, 'Galaxy S 3', 300, 1),
    (4, 'Galaxy S 4', 450, 1),
    (5, 'Galaxy S 4 mini', 400, 1),
    (6, '3GS', 150, 2),
    (7, '4', 200, 2),
    (8, '4S', 250, 2),
    (9, '5', 300, 2),
    (10, '5S', 350, 2),
    (11, 'Desire', 150, 3),
    (12, 'Desire200', 200, 3),
    (13, 'Desire500', 250, 3),
    (14, 'One', 400, 3),
    (15, 'One mini', 250, 3),
    (16, 'Optimus L3', 150, 4),
    (17, 'Optimus L5', 250, 4),
    (18, 'Optimus L7', 350, 4),
    (19, 'Optimus L9', 400, 4),
    (20, 'Optimus G2', 450, 4),
    (21, '100', 50, 5),
    (22, 'E72', 100, 5),
    (23, 'E6', 150, 5),
    (24, 'Lumia 520', 200, 5),
    (25, 'Lumia 620', 250, 5);
    Then the brand table:

    Code:
    CREATE TABLE IF NOT EXISTS `brand` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    
    INSERT INTO `brand` (`id`, `name`) VALUES
    (1, 'Samsung'),
    (2, 'iPhone'),
    (3, 'HTC'),
    (4, 'LG'),
    (5, 'Nokia');
    After that we need to alter our filter options a little, so that the actual names of the brand are passed to the PHP script.
    Also, let's make it so that they're checked when the page loads and removing the tick from the check box removes phones of that brand from the results.
    I find that more intuitive.

    Code:
    <div id="filter">
      <h2>Filter options</h2>
      <div>
        <input type="checkbox" id="Samsung" checked>
        <label for="Samsung">Samsung</label>
      </div>
      <div>
        <input type="checkbox" id="iPhone" checked>
        <label for="iPhone">iPhone</label>
      </div>
      <div>
        <input type="checkbox" id="HTC" checked>
        <label for="HTC">HTC</label>
      </div>
      <div>
        <input type="checkbox" id="LG" checked>
        <label for="LG">LG</label>
      </div>
      <div>
        <input type="checkbox" id="Nokia" checked>
        <label for="Nokia">Nokia</label>
      </div>
    </div>
    Okay, now let's look at the PHP:
    We have to join the "mobile_phone" table and the "brand" table accordingly, then select the fields we want.
    Also, instead of using OR we can use IN and pass PDO the $opts array

    By way of a refresher:

    Code:
    SELECT * FROM table WHERE id IN (1,2,3,4);
    translates to:

    Code:
    SELECT * FROM table WHERE id='1' or id='2' or id='3' or id='4';
    which as you will see, we can use to our advantage.

    Code:
    <?php 
      $pdo = new PDO('mysql:host=localhost;dbname=sitepoint', 'root', '*****');
      $opts = $_POST['filterOpts'];
      $qMarks = str_repeat('?,', count($opts) - 1) . '?';
      $statement = $pdo->prepare("SELECT mobile_phone.id, name, model, price FROM mobile_phone INNER JOIN brand ON brand_id = brand.id WHERE name IN ($qMarks)");
      $statement -> execute($opts);
      $results = $statement -> fetchAll(PDO::FETCH_ASSOC);
      $json = json_encode($results);
      echo($json);
    ?>
    I found this method of binding an array to an IN condition here: http://stackoverflow.com/questions/9...n-in-condition
    It is important to use placeholders in this way, as otherwise we could open ourselves up to a SQL injection attack.

    And that's it, really.
    You should prefer this to my original solution, as otherwise, as was pointed out, you will get into all kinds of trouble when your product list grows and you have to start adding different brands.

    You can find an updated demo here.

    Here's the complete code:

    index.php

    Code:
    <!DOCTYPE HTML>
    <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: 300px;
          }
     
          #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>Brand</th>
              <th>Model</th>
              <th>Price</th>
            </tr>
          </thead>
          <tbody>
          </tbody>
        </table>
     
        <div id="filter">
          <h2>Filter options</h2>
          <div>
            <input type="checkbox" id="Samsung" checked>
            <label for="Samsung">Samsung</label>
          </div>
          <div>
            <input type="checkbox" id="iPhone" checked>
            <label for="iPhone">iPhone</label>
          </div>
          <div>
            <input type="checkbox" id="HTC" checked>
            <label for="HTC">HTC</label>
          </div>
          <div>
            <input type="checkbox" id="LG" checked>
            <label for="LG">LG</label>
          </div>
          <div>
            <input type="checkbox" id="Nokia" checked>
            <label for="Nokia">Nokia</label>
          </div>
        </div>
    
        <script src="http://code.jquery.com/jquery-latest.js"></script> 
        <script>
          function makeTable(data){
            console.log(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);
          });
     
          $checkboxes.trigger("change");
        </script> 
      </body> 
    </html>
    submit.php

    Code:
    <?php 
      $pdo = new PDO('mysql:host=localhost;dbname=sitepoint', 'root', '*****');
      $opts = $_POST['filterOpts'];
      $qMarks = str_repeat('?,', count($opts) - 1) . '?';
      $statement = $pdo->prepare("SELECT mobile_phone.id, name, model, price FROM mobile_phone INNER JOIN brand ON brand_id = brand.id WHERE name IN ($qMarks)");
      $statement -> execute($opts);
      $results = $statement -> fetchAll(PDO::FETCH_ASSOC);
      $json = json_encode($results);
      echo($json);
    ?>
    @oddz ; and @myty ; I would be interested to hear your thoughts on this updated version.

  21. #21
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Good job @Pullo ;!

    One thing that I would add is the FOREIGN KEY in the `mobile_phone` table:
    Code:
    CREATE TABLE IF NOT EXISTS mobile_phone (
      id int(11) NOT NULL AUTO_INCREMENT,
      model varchar(255) DEFAULT NULL,
      price int(11) DEFAULT NULL,
      brand_id int(11) DEFAULT NULL,
      PRIMARY KEY (id)
      FOREIGN KEY (brand_id) 
            REFERENCES brand(id)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    This way, besides creating the actual constraint that will ensure valid data and database level validation for the data in the brand_id field (only ids that already exist in the brand table), you will also make it easier for a possible PHP CRUD auto generator.

    I've also removed the backticks, you're not using reserved keywords for column names, and no whitespace characters are present in those names either.

  22. #22
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Thanks

    Quote Originally Posted by myty View Post
    One thing that I would add is the FOREIGN KEY in the `mobile_phone` table:
    Code:
    CREATE TABLE IF NOT EXISTS mobile_phone (
      id int(11) NOT NULL AUTO_INCREMENT,
      model varchar(255) DEFAULT NULL,
      price int(11) DEFAULT NULL,
      brand_id int(11) DEFAULT NULL,
      PRIMARY KEY (id)
      FOREIGN KEY (brand_id) 
            REFERENCES brand(id)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    This way, besides creating the actual constraint that will ensure valid data and database level validation for the data in the brand_id field (only ids that already exist in the brand table), you will also make it easier for a possible PHP CRUD auto generator.
    Good tip with the foreign key. I didn't know this.

    Could you elaborate slightly on what you mean with "make it easier for a possible PHP CRUD auto generator".
    Do you mean something like scaffolding in Rails.

    Actually, most of the server-side stuff I've done recently involves Rails.
    On the one hand, this is nice as Rails abstracts away things like database relations (one or two lines in your models suffice to generate a bunch of additional methods), but on the other, it makes you quite lazy

    Quote Originally Posted by myty View Post
    I've also removed the backticks, you're not using reserved keywords for column names, and no whitespace characters are present in those names either.
    Ah, thanks. I didn't know this either.

  23. #23
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    If you don't use database native mechanisms to map out the relational model (that's what foreign keys are about), you will lose out on several fronts.

    First off, you'll have to take care of the data validation and integrity on your own, separately. Which means unnecessary insert_your_server-side_language_here code, even more so when this type of matters should be handled directly with database logic instead.

    Then you'll have to keep track of any change and provide manual support for the cascaded ripple effect CRUD operations have on tables.

    Then you'll have to constantly update and maintain a separate database specification in order for other users/developers to understand the relational model for that specific database (column names don't count as documentation ).

    Being specific about the relational model when creating the database elements has also the benefit that any automated tool will be able to follow table definition and build the relational model, the ER-model.

    Based on that model, the tools can create rich interfaces that need little to none additional work from your part, while also securing the database integrity.

    And just how powerful are those automatically created rich interfaces (if you build your database the right way) you can easily fathom from this feature matrix, for example: http://www.sqlmaestro.com/products/m...eature_matrix/

    Building a proper relational model in a database makes it possible for one of the most elegant mechanism: updatable and insertable views.

    The implementation differs from vendor to vendor. Here's the MySQL link: http://dev.mysql.com/doc/refman/5.6/...atability.html

  24. #24
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    6,045
    Mentioned
    219 Post(s)
    Tagged
    12 Thread(s)
    Thank you for an informative answer
    I just spent a while reading up on updatable and insertable views.
    I have really learned a lot in this thread!

  25. #25
    SitePoint Enthusiast
    Join Date
    Dec 2013
    Posts
    26
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Pullo, when you have time, upload this to your blog ...because this is quality stuff ... helping alot of people...this is how i found you and this forum


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •