Ajax & PHP: MySQL Query Not Fetching Any Rows

Question

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() {
        }
    });
}

@Jakub Trust me, it gave me nightmares as well. But thanks to the help of the individuals below, I'll never create a nightmare like this one again! It was a result of attempting to figure out PHP for myself. — stefmikhail, Aug 25 '11 at 17:04

score 2 · Answer 1 · answered Aug 24 '11 at 17:37

2

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";

answered Aug 24 '11 at 17:37

Marc B

356,200
43
426
500

See this is why I love Stack Overflow! I knew the method I was using was *messy*, but I had no idea what other method I could use. I have to run right now, but I will test this in a bit and let you know. Much appreciated Marc! – stefmikhail Aug 24 '11 at 17:39
You'll definitely want to add some sanitization though. Using raw user-provided data in a query string is NEVER a good idea. I didn't put that in, so the query is wide open to sql injection attacks. – Marc B Aug 24 '11 at 17:40
see this link for the sanitization http://stackoverflow.com/questions/1314518/sanitizing-users-data-in-get-by-php/1315779#1315779 – Rafay Aug 24 '11 at 17:43
@Marc B Oh, and could you just clarify (as this is syntax I'm not familiar with). The `array()` you mention; would I enter something specific there? Where am I placing the `$_GET` definitions? Sorry, I just want to understand what you're doing a bit better. – stefmikhail Aug 24 '11 at 17:44
@Marc B Th data is coming from a combo box which I am planning on populating with distinct values from the database columns, so am I right in assuming that if I'm doing so, there's no need for sanitization? – stefmikhail Aug 24 '11 at 17:47
Sorry, change the assigments inside the if()s to `$where_clauses[] = 'imgFamily=' . mysql_real_escape_string($_GET['imgFamily']);` (changing the var names for each one, of course). – Marc B Aug 24 '11 at 17:51
You still need to sanitize - nothing says a malicious user can't twiddle with the form and submit bad data, even if you output "ok" data into the form initially. NEVER trust ANYTHING the user sends. – Marc B Aug 24 '11 at 17:52
@Marc B Thank again for the help. I got it to work with a little tweaking. Please look below for my final result. – stefmikhail Aug 25 '11 at 17:06

score 1 · Answer 2 · answered Aug 24 '11 at 17:41

1

In your true condition you say "if the value is not equal to 1 OR the value is not null". However, if the value IS equal to one it is not null, so you get true. If the value is null, it's not equal to one so you get true.

Problem:

$imgFamilyTrue = (($imgFamily != 1) || ($imgFamily != null));
$imgFamilyFalse = (($imgFamily == 1) || ($imgFamily == null));

Fix:

$imgFamilyTrue = $imgFamily != 1 && $imgFamily != null;
$imgFamilyFalse = !$imgFamilyTrue;

answered Aug 24 '11 at 17:41

James

20,957
5
26
41

Interesting. I always understood `||` to be one or the other case. But I see what you're saying. Wouldn't `&&` make both conditions have to be true? In this case, when no result is chosen, the value is either `1` or `null`. It would never be both. But correct me if I misunderstand. – stefmikhail Aug 24 '11 at 17:50
Right but you are using negatives. imgFamily == null satisfies imgFamily != 1, and imgFamily == 1 satisfies imgFamily != null, see? So the joined or condition returns true for any value of imgFamily. – James Aug 24 '11 at 17:55

score 0 · Answer 3 · answered Aug 24 '11 at 17:31

0

the reason why you dont get the results is because you are using the AND operator you are saying

if its A and B and C then fetch the results so when you choose 1 out of 3 or 2 out of the 3 combo-box values the condition is failed and you get no results

EXAMPLE

if(A && B && C)
  // do something

and you select two values B and C so the condition returns false

answered Aug 24 '11 at 17:31

Rafay

30,950
5
68
101

I'm guessing you're referring to the `if else` conditions? I get what you're saying, but those variables that are false, i.e. `$imgFamilyFalse` are defined above as either being `== 1` or `||` `== null` which they would be if not selected on the client side. Would that not then solve the problem you are suggesting? – stefmikhail Aug 24 '11 at 17:36

score 0 · Accepted Answer · answered Aug 25 '11 at 17:18

I couldn't choose a "correct" response from the above answers, as both Marc B's & James' answers were instrumental in creating a working code.

James' answer alone would have corrected the original code, however Marc B helped immensely in cleaning up the code, and making it more efficient. Props to both.

On top of their suggestions, I had to wrap the $where_clauses[] = "1=1" in an if condition where there was no usable data from the Ajax.

I also had to wrap the mysql_real_escape_string($_GET[]'s in single quotes to be usable in the MySQL query.

My final 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 = !$imgFamilyTrue;

$imgClassTrue = $imgClass != 1 && $imgClass != null;
$imgClassFalse = !$imgClassTrue;

$imgGenderTrue = $imgGender != 1 && $imgGender != null;
$imgGenderFalse = !$imgGenderTrue;



//where clauses
$where_clauses = array();

if ($imgFamilyFalse && $imgClassFalse && $imgGenderFalse) {
    $where_clauses[] = "1=1"; // default do-nothing clause
}

if ($imgFamilyTrue) {
   $where_clauses[] = 'imgFamily=' . "'" . mysql_real_escape_string($_GET['imgFamily']) . "'";
}
if ($imgClassTrue) {
   $where_clauses[] = 'imgClass=' . "'" . mysql_real_escape_string($_GET['imgClass']) . "'";
}
if ($imgGenderTrue) {
   $where_clauses[] = 'imgGender=' . "'" . mysql_real_escape_string($_GET['imgGender']) . "'";
}



include"db.php";


$clause = implode(' AND ', $where_clauses);


$query_pag_data = "SELECT imgURL, imgTitle FROM images WHERE $clause ORDER BY imgDate DESC";


$result_pag_data = mysql_query($query_pag_data) or die('MySql Error' . mysql_error());


$num_rows = mysql_num_rows($result_pag_data);


if(!$result_pag_data) {
    echo "Cannot retrieve information from database.";
} else if($num_rows == 0) {
    echo "<div id='no_result'>Sorry, there are no items matching your request.</div>";
} else { 
    echo "<ul class='new_arrivals_gallery'>";
    while($row = mysql_fetch_assoc($result_pag_data)) { 
        echo "<li><a target='_blank' href='new_arrivals_img/".$row['imgURL']."' class='gallery' title='".$row['imgTitle']."'><img src='new_arrivals_img/thumbnails/".$row['imgURL']."'></a></li>";
      }
    echo "</ul>";   
}

Ajax & PHP: MySQL Query Not Fetching Any Rows

4 Answers4