Monday 3 September 2018

Ajax & amp; PHP: MySQL query does not retrieve any rows

I have 3 combo boxes set to filter results from a MySQL database. On load, all the results are being shown in their proper order, but when I try to select any 1 out of the 3 combo boxes, or any 2 out of the 3 combo boxes, no results (rows) are displayed. If I select all 3 combo boxes, then results are shown.

I'm hoping someone can figure out what's wrong with my query code. I've tried everything and can't seem to find anything wrong with it. I also wonder if I am doing it in the best way. I'm fairly new to PHP, and so I don't know all the different methods available.
The PHP:
//Define Refine Data Values
$imgFamily = $_GET['imgFamily'];
$imgClass = $_GET['imgClass'];
$imgGender = $_GET['imgGender'];

//Define Refine Values as True of False
$imgFamilyTrue = (($imgFamily != 1) || ($imgFamily != null));
$imgFamilyFalse = (($imgFamily == 1) || ($imgFamily == null));

$imgClassTrue = (($imgClass != 1) || ($imgClass != null));
$imgClassFalse = (($imgClass == 1) || ($imgClass == null));

$imgGenderTrue = (($imgGender != 1) || ($imgGender != null));
$imgGenderFalse = (($imgGender == 1) || ($imgGender == null));

include"db.php";

//Database queries based on refine selections
if($imgFamilyFalse && $imgClassFalse && $imgGenderFalse) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` ".
    "ORDER BY `imgDate` DESC";

} else if($imgFamilyTrue && $imgClassTrue && $imgGenderTrue) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` WHERE imgFamily='$imgFamily' AND imgClass='$imgClass' AND imgGender='$imgGender' ".
    "ORDER BY `imgDate` DESC";

} else if($imgFamilyTrue && $imgClassFalse && $imgGenderFalse) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` WHERE imgFamily='$imgFamily' ".
    "ORDER BY `imgDate` DESC";

} else if($imgFamilyFalse && $imgClassTrue && $imgGenderFalse) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` WHERE imgClass='$imgClass' ".
    "ORDER BY `imgDate` DESC";

} else if($imgFamilyFalse && $imgClassFalse && $imgGenderTrue) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` WHERE imgGender='$imgGender' ".
    "ORDER BY `imgDate` DESC";

} else if($imgFamilyFalse && $imgClassTrue && $imgGenderTrue) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` WHERE imgClass='$imgClass' AND imgGender='$imgGender' ".
    "ORDER BY `imgDate` DESC";

} else if($imgFamilyTrue && $imgClassFalse && $imgGenderTrue) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` WHERE imgFamily='$imgFamily' AND imgGender='$imgGender' ".
    "ORDER BY `imgDate` DESC";

} else if($imgFamilyTrue && $imgClassTrue && $imgGenderFalse) {
    $query_pag_data = "SELECT `imgURL`,`imgTitle` FROM `images` WHERE imgFamily='$imgFamily' AND imgClass='$imgClass' ".
    "ORDER BY `imgDate` DESC";
}

I'm pretty sure the problem is with the PHP as no errors are being called from the javascript side, and everything was working when I only had two combo boxes, but I will post my jQuery Ajax query, in case the problem is on that end.
The Ajax:
function loadData(imgFamily, imgClass, imgGender){
    $.ajax
    ({
        type: "GET",
        url: "filter_test.php",
        data: {imgFamily:imgFamily, imgClass:imgClass, imgGender:imgGender},
        success: function(msg) {
            $("#gallery_container").html(msg);
        },
        error: function(jqXHR, textStatus, errorThrown) {
        },
        complete: function() {
        }
    });
}


What a hideous mess. You should definitely clean that up:
$where_clauses = array();

$where_clauses[] = "1=1"; // default do-nothing clause

if ($_GET['imgFamily']) {
   $where_clauses[] = "imgFamily='$imgFamily'";
}
if ($_GET['imgClass']) {
   $where_clauses[] = "imgClass='$imgClass'";
}
if ($_GET['imgGender']) {
   $where_clauses[] = "imgFamily='$imgFamily'";
}

$clause = implode(' AND ', $where_clauses);
$sql = "SELECT imgURL, imgTitle FROM images WHERE $clause ORDER BY imgDate DESC";

0 comments:

Post a Comment