SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    AJAX filter problem

    Hi, I am a newbie in SitePoint. I followed the samples in SitePoint then made my test.

    I checked one brand and one price, everything are OK. However, when I checked one more brand (checked two brands), this is no result appear.

    Below is my test sample.

    CREATE TABLE IF NOT EXISTS `shoetest` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `brand` varchar(20) NOT NULL,
    `pPrice` int(4) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

    --
    -- Dumping data for table `shoetest`
    --

    INSERT INTO `shoetest` (`id`, `brand`, `pPrice`) VALUES
    (1, 'Adidas', 105),
    (2, 'Adidas', 130),
    (3, 'PUMA', 115),
    (4, 'PUMA', 108),
    (5, 'New Banlance', 85),
    (6, 'New Banlance', 140);

    HTML
    HTML Code:
    <!DOCTYPE HTML>
    <html>
      <head>
        <meta charset="utf-8">
        <title>AJAX filter demo</title>
      </head>
      <body> 
      <h1>Phones database</h1>
     
        <table id="shoe">
          <thead>
            <tr>
              <th>ID</th>
              <th>Brand</th>
              <th>Price</th>
            </tr>
          </thead>
          <tbody>
          </tbody>
        </table>
    
     
    <div id="filter">
    <h2>Filter options</h2>
    <div>
      <input type="checkbox" id="Adidas" value="Adidas">
      <label for="Adidas">Adidas</label>
    </div>
    <div>
      <input type="checkbox" id="NB" value="New Banlance">
      <label for="NB">New Banlance</label>
    </div>
    <div>
      <input type="checkbox" id="PUMA" value="PUMA">
      <label for="PUMA">PUMA</label>
    </div>
    
    <div>
      <input type="checkbox" id="p1" value="1">
      <label for="p1">< 100</label>
    </div> 
    <div>
      <input type="checkbox" id="p2" value="2">
      <label for="p2">$101 - $150</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 getShoeOptions(){
            var opts = [];
            $checkboxes.each(function(){
                if(this.checked){
                    opts.push(this.value);
                }
            
            });
            
            return opts;
        }
    
        function updateShoe(opts){
            $.ajax({
                type: "POST",
                url: "shoe.php",
                dataType : 'json',
                cache: false,
                data: {filterOpts: opts},
                success: function(data){
                    $('#shoe tbody').html(makeTable(data));
                }
            });
        }
    
        var $checkboxes = $("input:checkbox");
        $checkboxes.on("change", function(){
            var opts = getShoeOptions();
            updateShoe(opts);
        });
    
    
        updateShoe();
    
        </script> 
      </body> 
    </html>
    PHP Code:
    <?php 
        $pdo 
    = new PDO('mysql:host=localhost;dbname=filtertest''root''******');
        
    $select 'SELECT id,brand,pPrice';
        
    $from ' FROM shoetest';
        
    $where ' WHERE TRUE';
      
        
    $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
        
        
    //brand
        
    if (in_array("Adidas"$opts)){
        
    $where .= " AND brand ='Adidas'";
        }
        if (
    in_array("New Banlance"$opts)){
        
    $where .= " AND brand ='New Banlance'";
        }
        if (
    in_array("PUMA"$opts)){
        
    $where .= " AND brand ='PUMA'";
        }
        
        
    //price
        
    if (in_array("1"$opts)){
        
    $where .= " AND pPrice > 0 AND pPrice < 100";
        }
        if (
    in_array("2"$opts)){
        
    $where .= " AND pPrice > 101 AND pPrice < 150";
        }

        
    $sql $select $from $where;
        
    $statement $pdo->prepare($sql);
        
    $statement->execute();
        
    $results $statement->fetchAll(PDO::FETCH_ASSOC);
        
    $json json_encode($results);
        echo(
    $json);
    ?>
    Thanks
    Last edited by ScallioXTX; Jun 2, 2014 at 12:04. Reason: fixed typo

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,069
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    If I select 2 brands your query would become:

    Code sql:
    SELECT id,
           brand,
           pPrice
    FROM shoetest
    WHERE TRUE
      AND brand ='Adidas'
      AND brand ='New Banlance'

    Which won't work because a shoo can't be from the brand 'Adidas' and 'New balance' at the same time.

    I'd do something like this:

    PHP Code:
    $allBrands = array('Adidas''New Balance''PUMA');
    $selectedBrands = array();
    foreach (
    $allBrands as $brand) {
        if (
    in_array($brand$opts)) {
            
    $selectedBrands[] = $brand;
        }
    }

    if (
    count($selectedBrands)) {
        
    $where .= " AND brand IN ('".implode("', '"$selectedBrands)."')";

    Which for the two brands above will yield

    Code sql:
    SELECT id,
           brand,
           pPrice
    FROM shoetest
    WHERE TRUE
      AND brand IN ('Adidas',
                    'New Banlance')

    which does work
    Rmon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks ScallioXTX.

    How about the price part?

    Thanks a lot

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,069
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by chungmx5 View Post
    Thanks ScallioXTX.

    How about the price part?

    Thanks a lot
    Same deal, you should be able to figure it out from the code in my previous post.
    Rmon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally, I made the price option to radio button.

    I have based on this sample and add more function but no effect. It is using same DB.

    Could you please help me again?

    html part
    HTML Code:
    <!DOCTYPE HTML>
    <html>
      <head>
        <meta charset="utf-8">
        <title>AJAX filter demo</title>
    <script src="http://code.jquery.com/jquery-latest.js"></script> 
      </head>  
      <body> 
      <h1>Phones database</h1>
     
        <table id="shoe">
    	  <thead>
    		<tr>
    		  <th>ID</th>
    		  <th>Brand</th>
    		  <th>Price</th>
    		</tr>
    	  </thead>
    	  <tbody>
    	  </tbody>
    	</table>
    
     
    <div id="filter">
    <h2>Filter options</h2>
    <div>
      <input type="checkbox" id="Adidas" value="Adidas">
      <label for="Adidas">Adidas</label>
    </div>
    <div>
      <input type="checkbox" id="NB" value="New Balance">
      <label for="NB">New Balance</label>
    </div>
    <div>
      <input type="checkbox" id="PUMA" value="PUMA">
      <label for="PUMA">PUMA</label>
    </div>
    
    <div>
      <input type="radio" id="p1" name="price" value="1">
      <label for="p1">< 100</label>
    </div> 
    <div>
      <input type="radio" id="p2" name="price" value="2">
      <label for="p2">$101 - $150</label>
    </div>
    
    <!--new add these -->
    		<div class="sort" id="atoz">A to Z</div>
            <div class="sort" id="ztoa">Z to A</div>
            <div class="sort" id="htol">High price to Low price</div>
            <div class="sort" id="ltoh">Low price to High price</div>
    
    </div>
    
    
      <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 getShoeOptions(){
    		var opts = [];
    		$checkboxes.each(function(){
    			if(this.checked){
    				opts.push(this.value);
    			}
    		
    		});
    		$radioes.each(function(){
    			if(this.checked){
    				opts.push(this.value);
    			}
    		});
    		return opts;
    	}
    
    	function updateShoe(opts){
    		$.ajax({
    			type: "POST",
    			url: "shoe2.php",
    			dataType : 'json',
    			cache: false,
    			data: {filterOpts: opts},
    			success: function(data){
    				$('#shoe tbody').html(makeTable(data));
    			}
    		});
    	}
    
    	var $checkboxes = $("input:checkbox");
    	$checkboxes.on("change", function(){
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    	var $radioes = $("input:radio");
    	$radioes.on("change", function(){
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    
    	//new add this sort function
    	var $sort = $(".sort");
    
    	$sort.on("click", function(){
    		var opts = "";
    		opts.push(this.id);
    		updateShoe(opts);
    	});
    
    
    
    	updateShoe();
    
        </script> 
      </body> 
    </html>

    PHP Code:
    <?php 
        $pdo 
    = new PDO('mysql:host=localhost;dbname=filtertest''root''******');
        
    $select 'SELECT id,brand,pPrice';
        
    $from ' FROM shoetest';
        
    $where ' WHERE TRUE';
      
        
    $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
        
        
    //brand
        
    $allBrands = array('Adidas''New Balance''PUMA');
        
    $allPrice = array('1','2','3','4','5','6','7');

        
    $selectedBrands = array();
        foreach (
    $allBrands as $brand) {
            if (
    in_array($brand$opts)) {
                
    $selectedBrands[] = $brand;
            }
        }

        if (
    count($selectedBrands)) {
            
    $where .= " AND brand IN ('".implode("', '"$selectedBrands)."')";
        }
        
        
    //price
        
    if (in_array(1$opts)){
        
    $where .= " AND pPrice>=0 AND pPrice<=100";
        }
        else if (
    in_array(2$opts)){
        
    $where .= " AND pPrice>=101 AND pPrice<=150";
        }

        if (
    in_array("atoz"$opts)){
        
    $order .= " ORDER BY brand ASC";
        }
        else if (
    in_array("ztoa"$opts)){
        
    $order .= " ORDER BY brand DESC";
        }
        else if (
    in_array("htol"$opts)){
        
    $order .= " ORDER BY pPrice DESC";
        }    
        else if (
    in_array("ltoh"$opts)){
        
    $order .= " ORDER BY pPrice ASC";
        }
        else {
        
    $order .= " ORDER BY pPrice ASC";
        }


        
    $sql $select $from $where $order;
        
    $statement $pdo->prepare($sql);
        
    $statement->execute();
        
    $results $statement->fetchAll(PDO::FETCH_ASSOC);
        
    $json json_encode($results);
        echo(
    $json);
    ?>
    Thanks a lot

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

    Quote Originally Posted by chungmx5 View Post
    I have based on this sample and add more function but no effect. It is using same DB.
    In your JS you have:

    Code:
    var opts = "";
    opts.push(this.id);
    but push is an method defined on Array, not on String.

    Change it to this:

    Code:
    var opts = [];
    opts.push(this.id);
    and your code will work

  7. #7
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Pullo.

    It's work, but the function seems separated.

    I tried group the sort function to function getShoeOptions, the console has return [Object, Object], but the table has no effect. below is my code.

    Code:
      <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 getShoeOptions(){
    		var opts = [];
    		$checkboxes.each(function(){
    			if(this.checked){
    				opts.push(this.value);
    			}
    		
    		});
    		$radioes.each(function(){
    			if(this.checked){
    				opts.push(this.value);
    			}
    		});
    		$sort.one(function(){
    			if(this.clicked){
    				opts.push(this.id);
    			}
    		});
    		return opts;
    	}
    
    
    
    	function updateShoe(opts){
    		$.ajax({
    			type: "POST",
    			url: "shoe2.php",
    			dataType : 'json',
    			cache: false,
    			data: {filterOpts: opts},
    			success: function(data){
    				$('#shoe tbody').html(makeTable(data));
    console.log(data);
    			}
    		});
    	}
    
    	var $checkboxes = $("input:checkbox");
    	$checkboxes.on("change", function(){
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    	var $radioes = $("input:radio");
    	$radioes.on("change", function(){
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    
    	//new add this sort function
    	var $sort = $(".sort");
    
    	$sort.on("click", function(){
    		//var opts=[];
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    
    
    
    
    	updateShoe();
    
        </script>
    Thank you for your help!!

  8. #8
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,938
    Mentioned
    214 Post(s)
    Tagged
    12 Thread(s)
    Hey,

    I don't really understand your question, but this should work for you:

    Code:
    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 getShoeOptions(){
      var opts = [];
      $checkboxes.each(function(){
        if(this.checked){
          opts.push(this.value);
        }
    
      });
      $radioes.each(function(){
        if(this.checked){
          opts.push(this.value);
        }
      });
      return opts;
    }
    
    function updateShoe(opts){
      $.ajax({
        type: "POST",
        url: "shoe2.php",
        dataType : 'json',
        cache: false,
        data: {filterOpts: opts},
        success: function(data){
          //console.log(data);
          $('#shoe tbody').html(makeTable(data));
        }
      });
    }
    
    var $checkboxes = $("input:checkbox");
    $checkboxes.on("change", function(){
      var opts = getShoeOptions();
      updateShoe(opts);
    });
    
    var $radioes = $("input:radio");
    $radioes.on("change", function(){
      var opts = getShoeOptions();
      updateShoe(opts);
    });
    
    //new add this sort function
    var $sort = $(".sort");
    $sort.on("click", function(){
      var opts = [];
      opts.push(this.id);
      updateShoe(opts);
    });
    
    updateShoe();
    If I have misunderstood what you are asking, please rephrase your question

  9. #9
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Pullo,

    I uploaded the test page to server, it's easy to understand what I said.

    If I checked the brand and radio button, it's run perfect. but when I click the sort area(A to Z), the table will show all records.

    http://www.dunya.com.hk/filtertest/shoe3.html

    Therefore, I tried made the shoe4.html (http://www.dunya.com.hk/filtertest/shoe4.html), the script is changed as below

    Code:
        <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 getShoeOptions(){
    		var opts = [];
    		$checkboxes.each(function(){
    			if(this.checked){
    				opts.push(this.value);
    			}
    		
    		});
    		$radioes.each(function(){
    			if(this.checked){
    				opts.push(this.value);
    			}
    		});
    
    		$sort.one(function(){
    			if(this.clicked){
    				opts.push(this.id);
    			}
    		});
    
    		return opts;
    	}
    
    
    
    	function updateShoe(opts){
    		$.ajax({
    			type: "POST",
    			url: "shoe2.php",
    			dataType : 'json',
    			cache: false,
    			data: {filterOpts: opts},
    			success: function(data){
    				$('#shoe tbody').html(makeTable(data));
    console.log(data);
    			}
    		});
    	}
    
    	var $checkboxes = $("input:checkbox");
    	$checkboxes.on("change", function(){
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    	var $radioes = $("input:radio");
    	$radioes.on("change", function(){
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    
    
    	var $sort = $(".sort");
    
    	$sort.on("click", function(){
    		var opts = getShoeOptions();
    		updateShoe(opts);
    	});
    I want put the sort click to function getShoeOptions, but not effect.

    Thanks

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

    I have understood your problem to be the following:

    When you filter your data set by brand or by price, then everything works fine.
    However, when (in a second step) you try to sort the filtered records (either alphabetically or by price), the filter is ignored and all of the records in your database are displayed.
    This behaviour is incorrect. You only want to sort the already filtered records.

    Could you let me know if I got that right.

  11. #11
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Pullo,

    Yes, you are right.

    Thanks,

    chungmx5

  12. #12
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,938
    Mentioned
    214 Post(s)
    Tagged
    12 Thread(s)
    Ok, in that case, I would have thought that you could achieve that in your PHP script using:
    Code:
    ORDER BY column_name(s) ASC|DESC

  13. #13
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry! I don't understand how should I change the script.

    Could you please help me?

    Code:
    <?php 
    	$pdo = new PDO('mysql:host=localhost;dbname=filtertest', 'root', '248633');
    	$select = 'SELECT id,brand,pPrice';
    	$from = ' FROM shoetest';
    	$where = ' WHERE TRUE';
      
    	$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
    	
    	//brand
    	$allBrands = array('Adidas', 'New Balance', 'PUMA');
    	$allPrice = array('1','2','3','4','5','6','7');
    
    	$selectedBrands = array();
    	foreach ($allBrands as $brand) {
    		if (in_array($brand, $opts)) {
    			$selectedBrands[] = $brand;
    		}
    	}
    
    	if (count($selectedBrands)) {
    		$where .= " AND brand IN ('".implode("', '", $selectedBrands)."')";
    	}
    	
    	//price
    	if (in_array(1, $opts)){
    	$where .= " AND pPrice>=0 AND pPrice<=100";
    	}
    	else if (in_array(2, $opts)){
    	$where .= " AND pPrice>=101 AND pPrice<=150";
    	}
    
    	if (in_array("atoz", $opts)){
    	$order .= " ORDER BY brand ASC";
    	}
    	else if (in_array("ztoa", $opts)){
    	$order .= " ORDER BY brand DESC";
    	}
    	else if (in_array("htol", $opts)){
    	$order .= " ORDER BY pPrice DESC";
    	}	
    	else if (in_array("ltoh", $opts)){
    	$order .= " ORDER BY pPrice ASC";
    	}
    	else {
    	$order .= " ORDER BY pPrice ASC";
    	}
    
    
    	$sql = $select . $from . $where . $order;
    	$statement = $pdo->prepare($sql);
    	$statement->execute();
    	$results = $statement->fetchAll(PDO::FETCH_ASSOC);
    	$json = json_encode($results);
    	echo($json);
    ?>

  14. #14
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,938
    Mentioned
    214 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by chungmx5 View Post
    Sorry! I don't understand how should I change the script.
    Could you please help me?
    OK then. It's relatively simple.
    You have:

    Code:
    $sort.on("click", function(){
      var opts = [];
      opts.push(this.id);
      updateShoe(opts);
    });
    With this code, every time one of the secondary filter options is selected (such as sort A-Z), you are passing your PHP script an array, which contains the id filter option, e.g.:

    Code:
    ["atoz"]
    or

    Code:
    ["ztoa"]
    As you are not passing the selected primary filter options to the script (e.g. the brand), it is returning all records sorted accordingly.

    To remedy this, instead of initializing your opts variable to an empty array, initialize it to contain the correct filter values:

    Code:
    var $sort = $(".sort");
    $sort.on("click", function(){
      var opts = getShoeOptions();
      opts.push(this.id);
      updateShoe(opts);
    });
    And that should work as expected.

  15. #15
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Pullo,

    It's works.

    thanks a lot.

    chungmx5

  16. #16
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Pullo,

    one more question. If the data is Chinese characters, the result will echo "????????".

    How can I change the data to normal?

    Thanks,

    Chungmx5

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

    Quote Originally Posted by chungmx5 View Post
    If the data is Chinese characters, the result will echo "????????".

    How can I change the data to normal?

    Make sure you store the characters in the database with an appropriate encoding.
    This should help: http://www.haidongji.com/2007/10/04/...chinese-mysql/

  18. #18
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Pullo,

    I added the character code when connect the PDO, the problem is fixed.

    Thanks a lot,

    chungmx5

  19. #19
    SitePoint Wizard Stomme poes's Avatar
    Join Date
    Aug 2007
    Location
    Netherlands
    Posts
    10,276
    Mentioned
    50 Post(s)
    Tagged
    2 Thread(s)
    Hey Pullo (or anyone),
    I have kinda an unrelated question:
    if the db table is created like this
    `id` int(11) NOT NULL AUTO_INCREMENT,

    then does a number actually need to be added to values here:
    INSERT INTO `shoetest` (`id`, `brand`, `pPrice`) VALUES
    (1, 'Adidas', 105),
    (2, 'Adidas', 130),
    (3, 'PUMA', 115),
    etc?

    With auto-increment the db makes new ids for you, right? Or does that depend on the db, or is it assumed that one should try to set numbers anyway and auto-increment is more of a sort of backup, or..?

    Could you do
    INSERT INTO `shoetest` VALUES ('Adidas', 105), ( 'Adidas', 130), ( 'PUMA', 115), etc ;

    Also, I thought the table name didn't have to be a string? Or again is that because of this particular db? (I dunno what the OP used but I thought Sitepoint does tutorials in MySQL).

    I'm learning SQL but the lessons are based on sqlite3, not mySQL or anything.

  20. #20
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,069
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Hi Mallory,

    You are right that if you leave out the value of an auto increment field the database will fill it for you. At least this goes for MySQL and sqlite auto increments.
    If however you supply a value yourself the database will use that one instead of the next auto increment value. There aren't a lot of use cases for this (I can't think of a single one off the top of my head) and I wouldn't recommend it either.
    Table names don't need to be quoted, but you can do that if you wish to be explicit or if your table name is a reserved word (like "order") (again, I wouldn't recommend that).
    Rmon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  21. #21
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,938
    Mentioned
    214 Post(s)
    Tagged
    12 Thread(s)
    Hi poes,

    Quote Originally Posted by Stomme poes View Post
    Also, I thought the table name didn't have to be a string?
    You mean the backticks, right?
    These also allow you to use spaces and other special characters in table/column names.
    If you follow sensible rules for naming tables and columns, they should be unnecessary.

    See also: http://stackoverflow.com/questions/1...-mysql-queries


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
  •