运行过滤器时的查询不正确 - 查询未执行

Question

I have a form which allows a user to find any user based on the gender and age they are searching for. Not both filters are required for the search, for example, a user can search by gender alone and expect to find a random user whose gender is what was searched for.

Here is my approach to achieve the above:

$refined_gender = (htmlentities (strip_tags(@$_POST['gender'])) != 'any' ? htmlentities (strip_tags(@$_POST['gender'])) : '%');
$age_from       = (htmlentities (strip_tags(@$_POST['age_from'])) != 'none' ? htmlentities (strip_tags(@$_POST['age_from'])) : '17');
$age_to         = (htmlentities (strip_tags(@$_POST['age_to'])) != 'none' ? htmlentities (strip_tags(@$_POST['age_to'])) : '50');

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

if (isset($_POST['submit_form'])){

    $sql = "SELECT * FROM users WHERE 
              gender like ? AND 
              age BETWEEN ? AND ? AND 
              username != ? AND 
              account_type != 'admin'
            ORDER BY RAND() 
            LIMIT 1";
    $defined_chat = mysqli_prepare ($connect, $sql);
    mysqli_stmt_bind_param($defined_chat, "ssss",$refined_gender,$age_from,$age_to,$username);
    mysqli_stmt_execute ($defined_chat);

    while ($get_user = mysqli_fetch_assoc($defined_chat)){
        $rand_name = $get_user['username'];
        header ("Location: /messages.php?u=$rand_name");
    } // while closed
    echo "No user found fitting those requirements.";
    mysqli_stmt_close($defined_chat);

    echo $sql;
}

Note: I want to be able to search even without any selection on both gender and age (1 filter can be used and search should work), I am using a combination of the wildcard %, the operator like and the ternary operator of PHP (I have also tried gender = ? - still the same issues though).

However, the search never worked, which caused me to echo $sql. If I search for a male user, and click search, $sql echo's the following:

SELECT * FROM users WHERE gender like ? AND age BETWEEN ? AND ? AND username != ? AND account_type != 'admin' ORDER BY RAND() LIMIT 1

Same idea applies for age. If I search for someone aged between 26 - 30, $sql echo's this:

SELECT * FROM users WHERE gender = ? AND age BETWEEN ? AND ? AND username != ? AND account_type != 'admin' ORDER BY RAND() LIMIT 1

Meaning that neither variables are being passed into the query, which I dont understand why not.

It should also be noted that I get a mysqli_fetch_assoc() expects parameter 1 to be mysqli_result error on this line when searching:

 while ($get_user = mysqli_fetch_assoc($defined_chat)){

Which indicates their may be something wrong with the query? But the field names and logic seems fine to me so I am unsure.

Answer 1

There is this tiny issue with prepared statements. You can't really easily (at this time) do adjustable queries with them. So what ends up happening is you're creating and preparing your statement that looks like:

SELECT * FROM users WHERE gender = ? AND age BETWEEN ? AND ? AND account_type != 'admin' ORDER BY RAND() LIMIT 1

but actually it contains something like:

SELECT * FROM users WHERE gender =  AND age BETWEEN 26 AND 30 AND account_type != 'admin' ORDER BY RAND() LIMIT 1

This means you get an error in the execute. It won't output unless you catch it and is more a warning I guess that way. But that is why you can get so far.

I did get around this in one of my projects because I was determined. I did a lot of if != "" sets to do this and it is UGLY UGLY code. But it works.

if($gender != ""){
    array_push($selectParams,"gender = ?");
    $bindString .= "s"; 
}

$where = join(" AND ", $selectParams);

This is basically how I got around it and you may have to modify it to your needs. Again... super ugly and probably SUPER prone to error. This is a way to do it, but I'm not confident it really SHOULD be done.