CODEWITHSUNDEEP
phpmysqlajax
keepitnang
keepitnang

Reputation: 31

AJAX filter MySQL results using checkboxes

I'm attempting to get to grips with displaying filterable MySQL data within a PHP page based on user checkbox selections. I have a database of domain names and the dates on which these will require renewal as per Nominet DAC information and I can get unfiltered data to display, but filtering results by domain extensions is proving tricky for me to accomplish. I should point out at this stage that I am a serious newcomer to many of the ideas I am trying to learn to work with here, so please be gentle. I have tried following some other articles on here also, but no dice.

I have the following so far:

HTML

<input type="checkbox" class="extensions" name="extensions" value=".co.uk">.co.uk</input>
<input type="checkbox" class="extensions" name="extensions" value=".org.uk">.org.uk</input>

Script

$('.extensions').live('click', function() {
var all_boxes = $('.extensions');
var all_boxes_values = [];
var i = 0;
for (var i; i < all_boxes.length; i++) {
    if (all_boxes[i].checked) {
        all_boxes_values.push(all_boxes[i].value)
    }
}   
var all_boxes_values_clean = all_boxes_values.join(", ");
console.log(all_boxes_values_clean);
$.get("sql-test.php", {q: all_boxes_values_clean},
function(result) {
$("div#output").html(result);
}   
    )});

PHP

$g = $_GET['q'];
$extensionsql="";
$extension=1;
if(isset($g))   {
$extension=1;
$param = "" . str_replace(",", "','", $_GET['q']) . "";
}

And that's as far as I have gotten with my limited ability. What I would like to do next is search the column domainName for a string match and return the appropriate results to the user. Something to mimic something like the following but I'm not sure how to achieve it. Any help would be much appreciated:

SELECT * FROM `refresh` WHERE `domainName` LIKE '%.co.uk%' AND renewalDate LIKE '%2012-06-30%' ORDER BY `domainName` ASC

Thanks

Upvotes: 2

Views: 2789

Answers (2)

David Cheung
David Cheung

Reputation: 858

Change name of checkboxes (extensions[]) to make them arrays when POSTed back to PHP:

<input type="checkbox" class="extensions" name="extensions[]" value=".co.uk">.co.uk</input>
<input type="checkbox" class="extensions" name="extensions[]" value=".org.uk">.org.uk</input>

Probably should clean up your javascript a bit:

$('.extensions').live('click', function() {
var all_boxes = $('.extensions');
var checked_boxes = $('.extensions:checked');
var all_boxes_values = [];

checked_boxes.each(function(){
 var cb_value = $(this).val();
 all_boxes_values.push(cb_value);
});

var all_boxes_values_clean = all_boxes_values.join(", ");
console.log(all_boxes_values_clean);
$.get("sql-test.php", {q: all_boxes_values_clean},
      function(result) {
       $("div#output").html(result);
      })
});

Upvotes: 1

Waygood
Waygood

Reputation: 2693

change name="extensions" to name="extensions[]" as this will create an array of elements, not overwrite with the last checked value (IF YOU POST IT NORMALLY)

AND

$g = explode(',', $_GET['q']);

$param='';
while(list($key,$value)=each($g))
{
    $param.="`domainName` LIKE '%".mysql_real_escape_string($value)."' OR ";
}
$param=substr($param,-3); // knock off last OR

$sql="SELECT * FROM `refresh` WHERE renewalDate LIKE '%2012-06-30%' AND (".$param.") ORDER BY `domainName` ASC

Upvotes: 2

Related Questions