Use Ajax Filter to filter MySQL results - Having problems

[Codenoob2]

Hi,
thanks for this very nice script. I am experiencing some trouble, though.

I have installed it on my server. The DB connection is fine. I can call the submit.pgp directly and see the data but I cannot use the HTML page to trigger submit.php.

As if Jquery is not working. I can call your demo page and see that it works and I copied your HTML page and checked everything but AJAX will not pull the data using submit.php.

I used to console to check for errors:

The console shows the same in both versions (your server, my server). It seems that AJAX does not show the results of submit.php in the browser window as I think AJAX can pull the data. Maybe I have a wrong setup and we can figure it out. I have 2 files:

get_employee_data.html (with CSS, JQUERY) and the submit.php with the DB details.

Do I need more? I did not edit anything. I even copied the script to another server with lower restrictions.

Here is the server link to the installed script:

http://texlabserver2.de/justtestin3/get_employee_data.html

The submit.php just has the login data. I did not change that file except for the login to my DB.

http://texlabserver2.de/justtestin3/submit.php

Thanks for the help.

Any help is appreciated

[James_Hibbard]

Hi,

Just for anyone else seeing this thread, the script in question is here.

@Codenoob2 ; I’ve got to go out now, but I’ll have a look at this later on and get back to you.

[Codenoob2]

Ok thanks for the help.

This looks like a very useful script. So far I only need a script to check, whether a checkbox is checked or not and accordingly changing an entry in a MySQL DB.
But if you can figure out what my problem is, I will manage the rest.

Thanks

[James_Hibbard]

Hey,

I had a look at this, and the weird thing is that I copied and pasted the code you link to onto my PC and it runs as expected.
Really strange!

Let’s try sticking a couple of log statements in the ajax request:

function updateEmployees(opts){
  [COLOR="#FF0000"]console.log("Opts are: " + opts);[/COLOR]
  $.ajax({
    type: "POST",
    url: "submit.php",
    dataType : 'json',
    cache: false,
    data: {filterOpts: opts},
    success: function(records){
      [COLOR="#FF0000"]console.log("Records contain: " + records);[/COLOR]
      $('#employees tbody').html(makeTable(records));
    }
  });
}

Please alter your original page accordingly, then let me know.

[Codenoob2]

This is, what the console says:

Opts are: hasCar get_employee_data.html:133
Records contain: [object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object] 

Maybe this is a server thing?

[James_Hibbard]

Could you make the changes on this page: http://texlabserver2.de/justtestin3/get_employee_data.html
That way, we can both see what we are working with.

[James_Hibbard]

Also, that query should only return nine records, not eleven.
Could you post the contents of your PHP file, please.

[Codenoob2]

Yes I did…

[Codenoob2]

<?php
  $pdo = new PDO('mysql:host=localhost;dbname=XXXX', 'XXXX', '*********');
  $select = 'SELECT *';
  $from = ' FROM workers';
  $where = ' WHERE TRUE';
  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results=$statement->fetchAll(PDO::FETCH_ASSOC);
  $json=json_encode($results);
  echo($json);
?>

and here is the DB:

CREATE TABLE IF NOT EXISTS `workers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `hasCar` tinyint(1) DEFAULT NULL,
  `speaksForeignLanguage` tinyint(1) DEFAULT NULL,
  `canWorkNights` tinyint(1) DEFAULT NULL,
  `isStudent` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;



INSERT INTO `workers` (`id`, `name`, `age`, `address`, `hasCar`, `speaksForeignLanguage`, `canWorkNights`, `isStudent`) VALUES
(1, 'Jim', 39, '12 High Street, London', 1, 1, 1, 1),
(2, 'Fred', 29, '13 High Street, London', 1, 1, 1, 0),
(3, 'Bill', 19, '14 High Street, London', 1, 1, 0, 0),
(4, 'Tom', 39, '15 High Street, London', 1, 0, 0, 0),
(5, 'Cathy', 29, '16 High Street, London', 1, 0, 0, 1),
(6, 'Petra', 19, '17 High Street, London', 1, 0, 1, 0),
(7, 'Heide', 39, '18 High Street, London', 1, 1, 0, 0),
(8, 'William', 29, '19 High Street, London', 1, 1, 0, 1),
(9, 'Ted', 19, '20 High Street, London', 0, 0, 0, 1),
(10, 'Mike', 19, '21 High Street, London', 1, 0, 0, 1),
(11, 'Jo', 19, '22 High Street, London', 0, 1, 0, 1);

[James_Hibbard]

Ah, that’s the problem.
In your PHP script you’re just selecting everything from the workers table and returning it.
You’ll need to adjust the PHP script as per the end of the blog post:

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

  if (in_array("hasCar", $opts)){
    $where .= " AND hasCar = 1";
  }

  if (in_array("speaksForeignLanguage", $opts)){
    $where .= " AND speaksForeignLanguage = 1";
  }

  if (in_array("canWorkNights", $opts)){
    $where .= " AND canWorkNights = 1";
  }

  if (in_array("isStudent", $opts)){
    $where .= " AND isStudent = 1";
  }

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

And that should work as expected.

[Codenoob2]

Super cool…thanks. I must have overread that.

Now I am wondering if it is possible to have this the “other way round”. I want to have some checkboxes:

Checkbox A checked
Checkbox B not checked

when one or more of those are checked I want to update a MySQL entry. For example:

Checkbox A status true
Checkbox B status false

This might be very easy…sorry for the question, but I don’t know JS at all.

[James_Hibbard]

Not sure I follow.

You want the checkboxes checked to start with, then the selection to be filtered when you uncheck them?

[Codenoob2]

I don’t need a filter at all.

If checkbox checked set value in DB to TRUE else FALSE. Without page realoading. I can realise the update db function myself but not without a page refresh.

[James_Hibbard]

Attach an event handler from within which you get the checkboxes’ state then make an AJAX request:

var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
  var checked = [];
  $checkboxes.each(function(){
    if($(this).is(":checked")){
      checked.push(this.id);
    }
  });
  makeAjaxRequest(checked);
});

Make the request:

function updateEmployees(opts){
  $.ajax({
    type: "POST",
    url: "submit.php",
    cache: false,
    data: {checkBoxes: opts},
    success: function(response){
      // Do something with the response
    }
  });
}

Then, on the server, you can retrieve the array of checked checkboxes from $_POST['checkBoxes']

[Codenoob2]

I haven’t been working on this project for a little while so sorry for my late reply. Here is my suggestion on the code:

//this is submit.php

$checkbox = $_POST['checkBoxes'];

	for ($i=0; $i<sizeof($checkbox);$i++)
		{
		$query="INSERT INTO ischecked (name) Values ('".$checkbox[$i]."')";
		mysql_query($query) or die(mysql_error());
		}
		echo "DB filled with data";

I added your suggestions to my JS section:

var $checkboxes = $("input:checkbox");
    $checkboxes.on("change", function(){
    var checked = [];
    $checkboxes.each(function(){
    if($(this).is(":checked")){
    checked.push(this.id);
    }
  });
  makeAjaxRequest(checked);
});

function updateEmployees(opts){
  $.ajax({
    type: "POST",
    url: "submit.php",
    cache: false,
    data: {checkBoxes: opts},
    success: function(response){
      // Do something with the response
    }
  });
}

Nothing is entered in the DB so far.

The purpose of this script is a color chooser. Here is my other JS which is working fine:

<script type="text/javascript">
    $(document).ready(function(){
        $('input[type="checkbox"]').click(function(){
		
			if($(this).attr("value")=="ash"){
                $(".ash").toggle();
				
							
				
            }
						
			if($(this).attr("value")=="black"){
                $(".black").toggle();
            }
			
						
			if($(this).attr("value")=="bottlegreen"){
                $(".bottlegreen").toggle();
            }
			
			if($(this).attr("value")=="brown"){
                $(".brown").toggle();
            }
			
			if($(this).attr("value")=="charcoal"){
                $(".charcoal").toggle();
            }
						
			if($(this).attr("value")=="green"){
                $(".green").toggle();
            }
			
			if($(this).attr("value")=="marine"){
                $(".marine").toggle();
            }
			
			if($(this).attr("value")=="navy"){
                $(".navy").toggle();
            }
			
			
			if($(this).attr("value")=="olive"){
                $(".olive").toggle();
            }
			
            if($(this).attr("value")=="orange"){
                $(".orange").toggle();
            }
			
			
			if($(this).attr("value")=="red"){
                $(".red").toggle();
            }
			
			if($(this).attr("value")=="violet"){
                $(".violet").toggle();
            } 
			 
			 
			if($(this).attr("value")=="white"){
                $(".white").toggle();
            }
			
			if($(this).attr("value")=="yellow"){
                $(".yellow").toggle();
            }
            
			
			
			
        });
    });

</script>

When I uncheck a checkbox a DIV is called, saying: you have unchecked “black” (for example). Here is the CSS:

 <div>
	
		<label><input type="checkbox" name="colorCheckbox[]" value="ash" checked> graumeliert</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="black" checked> schwarz</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="bottlegreen" checked> flaschengr&uuml;n</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="brown" checked> braun</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="charcoal" checked> grau</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="green" checked> gr&uuml;n</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="marine" checked> marine (mittelblau)</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="navy" checked> navy (dunkelblau)</label>
	    <label><input type="checkbox" name="colorCheckbox[]" value="olive" checked> oliv</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="orange" checked> orange</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="red" checked> rot</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="violet" checked> violett</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="white" checked> wei&szlig;</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="yellow" checked> gelb</label>
		<input type="submit" name="Submit" value="Weiter">
        
    </div>
	
	<div class="ash box">Du hast <strong>graumeliert</strong> abgew&auml;hlt</div>
	<div class="black box">Du hast <strong>schwarz</strong> abgew&auml;hlt</div>
	<div class="bottlegreen box">Du hast <strong>flaschengr&uuml;n</strong> abgew&auml;hlt</div>
    <div class="brown box">Du hast <strong>braun</strong> abgew&auml;hlt</div>
	<div class="charcoal box">Du hast <strong>grau</strong> abgew&auml;hlt</div>
	<div class="green box">Du hast <strong>gr&uuml;n</strong> abgew&auml;hlt</div>
	<div class="marine box">Du hast <strong>marine (mittelblau)</strong> abgew&auml;hlt</div>
	<div class="navy box">Du hast <strong>navy (dunkelblau)</strong> abgew&auml;hlt</div>
	<div class="olive box">Du hast <strong>oliv</strong> abgew&auml;hlt</div>
	<div class="orange box">Du hast <strong>orange</strong> abgew&auml;hlt</div>
	<div class="red box">Du hast <strong>rot</strong> abgew&auml;hlt</div>
    <div class="violet box">Du hast <strong>violett</strong> abgew&auml;hlt</div>
	<div class="white box">Du hast <strong>wei&szlig;</strong> abgew&auml;hlt</div>
	<div class="yellow box">Du hast <strong>gelb</strong> abgew&auml;hlt</div>
	

[Codenoob2]

I haven’t had time to work on this project for a while, sorry for the late reply.
What I want to build is a color chooser. I have several checkboxes called “red, green, blue” and so on. The regular status is: checked. When the user unchecks a DIV is called that informs the user: you have unchecked “blue”.

So far so good. This works. Now I want to expand this using your script. Example:

Checkbox green is checked so I want the value “green” in my DB. I only have one row in my database called “name”. If “green” is unchecked, I don’t want the value “green” in my DB.

I tried to solve this problem with calling the state of all my checkboxes and then submitting the form by pressing a button. Here is the code:

<form action="mfuminus.php" method="post">

	<!--onoffswitch -->
	<!-- <div class="onoffswitch">
    <input type="checkbox" name="onoffswitch" class="onoffswitch-checkbox" id="myonoffswitch" checked>
	
        <label class="onoffswitch-label" for="myonoffswitch">
        <div class="onoffswitch-inner"></div>
        <div class="onoffswitch-switch"></div>
    </label>
    </div> -->



    <div>
	
		<label><input type="checkbox" name="colorCheckbox[]" value="ash" checked> graumeliert</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="black" checked> schwarz</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="bottlegreen" checked> flaschengr&uuml;n</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="brown" checked> braun</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="charcoal" checked> grau</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="green" checked> gr&uuml;n</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="marine" checked> marine (mittelblau)</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="navy" checked> navy (dunkelblau)</label>
	    <label><input type="checkbox" name="colorCheckbox[]" value="olive" checked> oliv</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="orange" checked> orange</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="red" checked> rot</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="violet" checked> violett</label>
		<label><input type="checkbox" name="colorCheckbox[]" value="white" checked> wei&szlig;</label>
        <label><input type="checkbox" name="colorCheckbox[]" value="yellow" checked> gelb</label>
		<input type="submit" name="Submit" value="Weiter">
        
    </div>
	
	<div class="ash box">Du hast <strong>graumeliert</strong> abgew&auml;hlt</div>
	<div class="black box">Du hast <strong>schwarz</strong> abgew&auml;hlt</div>
	<div class="bottlegreen box">Du hast <strong>flaschengr&uuml;n</strong> abgew&auml;hlt</div>
    <div class="brown box">Du hast <strong>braun</strong> abgew&auml;hlt</div>
	<div class="charcoal box">Du hast <strong>grau</strong> abgew&auml;hlt</div>
	<div class="green box">Du hast <strong>gr&uuml;n</strong> abgew&auml;hlt</div>
	<div class="marine box">Du hast <strong>marine (mittelblau)</strong> abgew&auml;hlt</div>
	<div class="navy box">Du hast <strong>navy (dunkelblau)</strong> abgew&auml;hlt</div>
	<div class="olive box">Du hast <strong>oliv</strong> abgew&auml;hlt</div>
	<div class="orange box">Du hast <strong>orange</strong> abgew&auml;hlt</div>
	<div class="red box">Du hast <strong>rot</strong> abgew&auml;hlt</div>
    <div class="violet box">Du hast <strong>violett</strong> abgew&auml;hlt</div>
	<div class="white box">Du hast <strong>wei&szlig;</strong> abgew&auml;hlt</div>
	<div class="yellow box">Du hast <strong>gelb</strong> abgew&auml;hlt</div>
	


	

	

	
</form>

[Codenoob2]

Is there a problem with answering in this forum? When I reloaded the page, my whole answer was deleted. This happened two times. Also I could not edit a post.

[Codenoob2]

Now all my posts reappeared. This looks really messy now. Sorry for that, but I have trouble writing here as it seems.

[James_Hibbard]

Hi there,

Sorry that you had trouble posting. Our auto-mod system is a little random sometimes and for whatever reason your posts got placed in the mod queue.
However, as we’re moving to Discourse soon, I don’t think this will be addressed.

Anyway, you can write this:

$('input[type="checkbox"]').click(function () {
  if ($(this).attr("value") == "ash") {
      $(".ash").toggle();
  }

  if ($(this).attr("value") == "black") {
      $(".black").toggle();
  }

  if ($(this).attr("value") == "bottlegreen") {
      $(".bottlegreen").toggle();
  }

  if ($(this).attr("value") == "brown") {
      $(".brown").toggle();
  }

  if ($(this).attr("value") == "charcoal") {
      $(".charcoal").toggle();
  }

  if ($(this).attr("value") == "green") {
      $(".green").toggle();
  }

  if ($(this).attr("value") == "marine") {
      $(".marine").toggle();
  }

  if ($(this).attr("value") == "navy") {
      $(".navy").toggle();
  }

  if ($(this).attr("value") == "olive") {
      $(".olive").toggle();
  }

  if ($(this).attr("value") == "orange") {
      $(".orange").toggle();
  }

  if ($(this).attr("value") == "red") {
      $(".red").toggle();
  }

  if ($(this).attr("value") == "violet") {
      $(".violet").toggle();
  }

  if ($(this).attr("value") == "white") {
      $(".white").toggle();
  }

  if ($(this).attr("value") == "yellow") {
      $(".yellow").toggle();
  }
});

Like this:

$('input[type="checkbox"]').on("click", function () {
  selector = "." + this.value;
  $(selector).toggle();
});

Apart from that, it seems like you are trying to remember user preferences.
A database, might be a little heavy handed here. Have you considered using cookies instead?