How to conditionally add filters to my query of the rsform_submission_values table?

Question

$FormId=23;
$qustioan1=qka1;

<form method="post" action="#">
    <select name="state" onChange="irancitylist(this.value);">
        <option value="">--select state --</option>
        <option value="mystate">mystate</option>
    </select>

    <select name="city" id="city">
        <option value="">--select city--</option>
        <option value="mycity">mycity</option>
    </select> 


    <button type="submit" name="submit" >go to select</button>

    <?php
    if(isset($_POST["submit"])){
        $state=$_POST["state"];
        $city=$_POST["city"];
    }

If the user selects a state and a city, do this query:

$db->setQuery(SELECT count(DISTINCT a.SubmissionId) 
FROM `jwqt5_rsform_submission_values` a 
JOIN `jwqt5_rsform_submission_values` b 
ON(a.submissionid=b.submissionid) 
JOIN `jwqt5_rsform_submission_values` c 
ON(b.submissionid=c.submissionid) 
WHERE a.FormId= '$FormId' 
AND a.FieldName='state' AND a.FieldValue='$state' //mystate
AND b.FieldName='$qustioan1' AND b.FieldValue='yes' 
AND c.FieldName='city' AND c.FieldValue='$city'"); //mycity
$Q1answer = $db->loadResult();

Otherwise, if the user selects only a state, do this query:

$db->setQuery(SELECT count(DISTINCT a.SubmissionId) 
FROM `jwqt5_rsform_submission_values` a 
JOIN `jwqt5_rsform_submission_values` b 
ON(a.submissionid=b.submissionid) 
WHERE a.FormId= '$FormId' 
AND a.FieldName='state' AND a.FieldValue='$state' //mystate
AND b.FieldName='$qustioan1' AND b.FieldValue='yes' );
$Q1answer = $db->loadResult();

Answer 1

If you implement the code from my earlier code, then you only need to write a dynamic $having array to generate the correct HAVING clause. I must note that your current coding attempt is not stable/secure. You are receiving user-supplied data from $_POST and feeding it directly into your raw sql. This means that there are no validating or sanitising steps to protect your application.

Also, you have some typos to fix. qka1 is a string that must be wrapped in quotes at $qustioan1=qka1;.

To avoid re-posting my previous answer, I'll only show how to build your $having array.

$formId = 23;
$having = ['qka1' => 'yes'];

$input = Factory::getApplication()->input;
$state = $input->post('state', '', 'string');
if ($state) {
    $having['state'] = $state;
}
$city = $input->post('city', '', 'string');
if ($city) {
    $having['city'] = $city;
}

The foreach() loop on the $having array will correctly apply the chosen filters into your query.

This gives you clean, professional, maintainable code without having to dig deeply into your query building block of code.