Multiple sql conditions based on checkboxes in WordPress

I am developing an advanced search system which searches users according to their skills. These are the various skills.

Each skill is stored in separate column in database with values 0 & 1.
Now when I am searching for a user I am using OR criteria so that when I check Access the users with Access skill would be found. Here is the code for the search criteria:

$query .= "SELECT * FROM wordpress_candidate
WHERE (city = '$cty')
OR (state = '$stt')";
if(isset($s_g_1)){  $query .=  "OR (generala = '$s_g_1')";    }
if(isset($s_g_2)){  $query .=  "OR (generalb = '$s_g_2')";    }
if(isset($s_g_3)){  $query .=  "OR (generalc = '$s_g_3')";    }
if(isset($s_g_4)){  $query .=  "OR (generald = '$s_g_4')";    }
//if(isset($s_g_5)){  $query .=  "OR (generale = '$s_g_5')";    }
if(isset($s_g_6)){  $query .=  "OR (generale = '$s_g_6')";    }
if(isset($s_g_7)){  $query .=  "OR (generalf = '$s_g_7')";    }
if(isset($s_g_8)){  $query .=  "OR (generalg = '$s_g_8')";    }
if(isset($s_g_9)){  $query .=  "OR (generalh = '$s_g_9')";    }
if(isset($s_g_10)){  $query .=  "OR (generali = '$s_g_10')";    }
if(isset($s_g_11)){  $query .=  "OR (generalj = '$s_g_11')";    }
if(isset($s_g_12)){  $query .=  "OR (generalk = '$s_g_12')";    }
if(isset($s_g_other)){  $query .=  "OR (generall = '$s_g_other')";    }
if(isset($s_f_1)){  $query .=  "OR (funda = '$s_f_1')";    }
if(isset($s_f_2)){  $query .=  "OR (fundb = '$s_f_2')";    }
if(isset($s_f_3)){  $query .=  "OR (fundc = '$s_f_3')";    }
if(isset($s_f_4)){  $query .=  "OR (fundd = '$s_f_4')";    }
if(isset($s_f_5)){  $query .=  "OR (funde = '$s_f_5')";    }
if(isset($s_f_6)){  $query .=  "OR (fundf = '$s_f_6')";    }
if(isset($s_f_7)){  $query .=  "OR (fundg = '$s_f_7')";    }
if(isset($s_f_8)){  $query .=  "OR (fundh = '$s_f_8')";    }
if(isset($s_f_9)){  $query .=  "OR (fundi = '$s_f_9')";    }
if(isset($s_f_10)){  $query .=  "OR (fundj = '$s_f_10')";    }
if(isset($s_f_11)){  $query .=  "OR (fundk = '$s_f_11')";    }
if(isset($s_f_12)){  $query .=  "OR (fundl = '$s_f_12')";    }
if(isset($s_f_13)){  $query .=  "OR (fundm = '$s_f_13')";    }
if(isset($s_f_other)){  $query .=  "OR (fund = '$s_f_other')";    }
if(isset($s_a_1)){  $query .=  "OR (acca = '$s_a_1')";    }
if(isset($s_a_2)){  $query .=  "OR (accb = '$s_a_2')";    }
if(isset($s_a_3)){  $query .=  "OR (accc = '$s_a_3')";    }
if(isset($s_a_4)){  $query .=  "OR (accd = '$s_a_4')";    }
if(isset($s_a_5)){  $query .=  "OR (acce = '$s_a_5')";    }
if(isset($s_a_6)){  $query .=  "OR (accf = '$s_a_6')";    }
if(isset($s_a_7)){  $query .=  "OR (accg = '$s_a_7')";    }
if(isset($s_a_8)){  $query .=  "OR (acch = '$s_a_8')";    }
if(isset($s_a_9)){  $query .=  "OR (acci = '$s_a_9')";    }
if(isset($s_a_10)){  $query .=  "OR (accj = '$s_a_10')";    }
if(isset($s_a_11)){  $query .=  "OR (acck = '$s_a_11')";    }
if(isset($s_a_other)){  $query .=  "OR (accl = '$s_a_other')";    }
if(isset($s_p_1)){  $query .=  "OR (puba = '$s_p_1')";    }
if(isset($s_p_2)){  $query .=  "OR (pubb = '$s_p_2')";    }
if(isset($s_p_3)){  $query .=  "OR (pubc = '$s_p_3')";    }
if(isset($s_p_4)){  $query .=  "OR (pubd = '$s_p_4')";    }
if(isset($s_p_5)){  $query .=  "OR (pube = '$s_p_5')";    }
if(isset($s_p_6)){  $query .=  "OR (pubf = '$s_p_6')";    }
if(isset($s_p_7)){  $query .=  "OR (pubg = '$s_p_7')";    }
if(isset($s_p_8)){  $query .=  "OR (pubh = '$s_p_8')";    }
if(isset($s_p_9)){  $query .=  "OR (pubi = '$s_p_9')";    }
if(isset($s_p_other)){  $query .=  "OR (pubj = '$s_p_other')";    }
if(isset($s_i_1)){  $query .=  "OR (ita = '$s_i_1')";    }
if(isset($s_i_2)){  $query .=  "OR (itb = '$s_i_2')";    }
if(isset($s_i_3)){  $query .=  "OR (itc = '$s_i_3')";    }
if(isset($s_i_4)){  $query .=  "OR (itd = '$s_i_4')";    }
if(isset($s_i_5)){  $query .=  "OR (ite = '$s_i_5')";    }
if(isset($s_i_6)){  $query .=  "OR (itf = '$s_i_6')";    }
if(isset($s_i_7)){  $query .=  "OR (itg = '$s_i_7')";    }
if(isset($s_i_8)){  $query .=  "OR (ith = '$s_i_8')";    }
if(isset($s_i_other)){  $query .=  "OR (iti = '$s_i_other')";

This code is working perfect but if both Access And Crystal are checked I want to search users according to both criteria but it only searches according to one . I know I can check condition for both check boxes and add AND in query. But how can I do this for all the checkbox?

HTML

   <form method="post" enctype="multipart/form-data" name="searchform" action="">
<tr>
     <td align="left"><input value="1" <?php echo ($s_g_1=='1')?'checked':'' ?> id="s_g_1" name="s_g_1" type="checkbox"><label for="s_g_1"><span>Access</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_1=='1')?'checked':'' ?> id="s_f_1" name="s_f_1" type="checkbox"><label for="s_f_1"><span>ACT</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_1=='1')?'checked':'' ?> id="s_a_1" name="s_a_1" type="checkbox"><label for="s_a_1"><span>Blackbaud Financial Edge&nbsp;</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_p_1=='1')?'checked':'' ?> id="s_p_1" name="s_p_1" type="checkbox"><label for="s_p_1"><span>Corel Draw</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_i_1=='1')?'checked':'' ?> id="s_i_1" name="s_i_1" type="checkbox"><label for="s_i_1"><span>Mac</span></label></td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_2=='1')?'checked':'' ?> id="s_g_2" name="s_g_2" type="checkbox"><label for="s_g_2"><span>Crystal Reports&nbsp;</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_10=='1')?'checked':'' ?> id="s_f_10" name="s_f_10" type="checkbox"><label for="s_f_10"><span>Convio</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_2=='1')?'checked':'' ?> id="s_a_2" name="s_a_2" type="checkbox"><label for="s_a_2"><span>Fund EZ</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_p_8=='1')?'checked':'' ?> id="s_p_8" name="s_p_8" type="checkbox"><label for="s_p_8"><span>Dreamweaver</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_i_2=='1')?'checked':'' ?> id="s_i_2" name="s_i_2" type="checkbox"><label for="s_i_2"><span>PC</span></label></td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_3=='1')?'checked':'' ?> id="s_g_3" name="s_g_3" type="checkbox"><label for="s_g_3"><span>Excel</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_2=='1')?'checked':'' ?> id="s_f_2" name="s_f_2" type="checkbox"><label for="s_f_2"><span>Donor2</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_3=='1')?'checked':'' ?> id="s_a_3" name="s_a_3" type="checkbox"><label for="s_a_3"><span>Fundware</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_p_2=='1')?'checked':'' ?> id="s_p_2" name="s_p_2" type="checkbox"><label for="s_p_2"><span>Front Page</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_i_3=='1')?'checked':'' ?> id="s_i_3" name="s_i_3" type="checkbox"><label for="s_i_3"><span>Web</span></label></td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_4=='1')?'checked':'' ?> id="s_g_4" name="s_g_4" type="checkbox"><label for="s_g_4"><span>Filemaker Pro</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_3=='1')?'checked':'' ?> id="s_f_3" name="s_f_3" type="checkbox"><label for="s_f_3"><span>Donor Perfect</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_11=='1')?'checked':'' ?> id="s_a_11" name="s_a_11" type="checkbox"><label for="s_a_11"><span>Great Plains</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_p_3=='1')?'checked':'' ?> id="s_p_3" name="s_p_3" type="checkbox"><label for="s_p_3"><span>Illustrator</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_i_4=='1')?'checked':'' ?> id="s_i_4" name="s_i_4" type="checkbox"><label for="s_i_4"><span>Windows XP</span></label></td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_11=='1')?'checked':'' ?> id="s_g_11" name="s_g_11" type="checkbox"><label for="s_g_11"><span>HTML</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_11=='1')?'checked':'' ?> id="s_f_11" name="s_f_11" type="checkbox"><label for="s_f_11"><span>eTapestry</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_4=='1')?'checked':'' ?> id="s_a_4" name="s_a_4" type="checkbox"><label for="s_a_4"><span>MIP</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_p_9=='1')?'checked':'' ?> id="s_p_9" name="s_p_9" type="checkbox"><label for="s_p_9"><span>InDesign</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_i_5=='1')?'checked':'' ?> id="s_i_5" name="s_i_5" type="checkbox"><label for="s_i_5"><span>Help Desk</span></label></td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_6=='1')?'checked':'' ?> id="s_g_6" name="s_g_6" type="checkbox"><label for="s_g_6"><span>Lotus</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_4=='1')?'checked':'' ?> id="s_f_4" name="s_f_4" type="checkbox"><label for="s_f_4"><span>Fundmaster</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_5=='1')?'checked':'' ?> id="s_a_5" id="" name="s_a_5" type="checkbox"><label for="s_a_5"><span>OneWrite Plus</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_p_4=='1')?'checked':'' ?> id="s_p_4" name="s_p_4" type="checkbox"><label for="s_p_4"><span>Pagemaker</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_i_6=='1')?'checked':'' ?> id="s_i_6"name="s_i_6" type="checkbox"><label for="s_i_6"><span>Oracle</span></label></td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_7=='1')?'checked':'' ?> id="s_g_7" name="s_g_7" type="checkbox"><label for="s_g_7"><span>Outlook</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_5=='1')?'checked':'' ?> id="s_f_5" name="s_f_5" type="checkbox"><label for="s_f_5"><span>GiftmakerPRO&nbsp;</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_6=='1')?'checked':'' ?> id="s_a_6" name="s_a_6" type="checkbox"><label for="s_a_6"><span>Peachtree</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_p_5=='1')?'checked':'' ?> id="s_p_5" name="s_p_5" type="checkbox"><label for="s_p_5"><span>Photoshop</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_i_7=='1')?'checked':'' ?> id="s_i_7" name="s_i_7" type="checkbox"><label for="s_i_7"><span>Windows NT</span></label></td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_8=='1')?'checked':'' ?> id="s_g_8" name="s_g_8" type="checkbox"><label for="s_g_8"><span>Powerpoint</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_12=='1')?'checked':'' ?> id="s_f_12" name="s_f_12" type="checkbox"><label for="s_f_12"><span>GIFTS</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_7=='1')?'checked':'' ?> id="s_a_7" name="s_a_7" type="checkbox"><label for="s_a_7"><span>Quickbooks</td>
     <td align="left"><input value="1" <?php echo ($s_p_6=='1')?'checked':'' ?> id="s_p_6" name="s_p_6" type="checkbox"><label for="s_p_6"><span>Quark</td>
     <td align="left"><input value="1" <?php echo ($s_i_8=='1')?'checked':'' ?> id="s_i_8" name="s_i_8" type="checkbox"><label for="s_i_8"><span>Windows 2000</td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_12=='1')?'checked':'' ?> id="s_g_12" name="s_g_12" type="checkbox"><label for="s_g_12"><span>Salesforce</td>
     <td align="left"><input value="1" <?php echo ($s_g_5=='1')?'checked':'' ?> id="s_g_5" name="s_g_5" type="checkbox"><label for="s_g_5"><span>iMIS</td>
     <td align="left"><input value="1" <?php echo ($s_a_8=='1')?'checked':'' ?> id="s_a_8" name="s_a_8" type="checkbox"><label for="s_a_8"><span>Quicken</td>
     <td align="left"><input value="1" <?php echo ($s_p_7=='1')?'checked':'' ?> id="s_p_7" name="s_p_7" type="checkbox"><label for="s_p_7"><span>MS Publisher&nbsp;</td>
     <td align="left"><input value="1" <?php echo ($s_i_other=='1')?'checked':'' ?> id="s_i_other" name="s_i_other" type="checkbox"><label for="s_i_other"><span>Other</td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_9=='1')?'checked':'' ?> id="s_g_9" name="s_g_9" type="checkbox"><label for="s_g_9"><span>Word</td>
     <td align="left"><input value="1" <?php echo ($s_f_6=='1')?'checked':'' ?> id="s_f_6" name="s_f_6" type="checkbox"><label for="s_f_6"><span>Millenium</td>
     <td align="left"><input value="1" <?php echo ($s_a_9=='1')?'checked':'' ?> id="s_a_9" name="s_a_9" type="checkbox"><label for="s_a_9"><span>Real World</td>
     <td align="left"><input value="1" <?php echo ($s_p_other=='1')?'checked':'' ?> id="s_p_other" name="s_p_other" type="checkbox"><label for="s_p_other"><span>Other</td>
     <td align="left">&nbsp;</td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_10=='1')?'checked':'' ?> id="s_g_10" name="s_g_10" type="checkbox"><label for="s_g_10"><span>Word Perfect</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_7=='1')?'checked':'' ?> id="s_f_7" name="s_f_7" type="checkbox"><label for="s_f_7"><span>Paradigm</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_10=='1')?'checked':'' ?> id="s_a_10" name="s_a_10" type="checkbox"><label for="s_a_10"><span>Solomon</span></label></td>
     <td align="left">&nbsp;</td>
     <td align="left">&nbsp;</td>
    </tr>
    <tr>
     <td align="left"><input value="1" <?php echo ($s_g_other=='1')?'checked':'' ?> id="s_g_other" name="s_g_other" type="checkbox"><label for="s_g_other"><span>Other</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_f_8=='1')?'checked':'' ?> id="s_f_8" name="s_f_8" type="checkbox"><label for="s_f_8"><span>Pledgemaker</span></label></td>
     <td align="left"><input value="1" <?php echo ($s_a_other=='1')?'checked':'' ?> id="s_a_other" name="s_a_other" type="checkbox"><label for="s_a_other"><span>Other</span></label></td>
     <td align="left">&nbsp;</td>
     <td align="left">&nbsp;</td>
    </tr>
    <tr>
     <td align="left">&nbsp;</td>
     <td align="left"><input value="1" <?php echo ($s_f_9=='1')?'checked':'' ?> id="s_f_9" name="s_f_9" type="checkbox"><label for="s_f_9"><span>Raiser's Edge&nbsp;</span></label></td>
     <td align="left">&nbsp;</td>
     <td align="left">&nbsp;</td>
     <td align="left">&nbsp;</td>
    </tr>
    <tr>
     <td align="left">&nbsp;</td>
     <td align="left"><input value="1" <?php echo ($s_f_other=='1')?'checked':'' ?> id="s_f_other" name="s_f_other" type="checkbox"><label for="s_f_other"><span>Other</span></label></td>
     <td align="left">&nbsp;</td>
     <td align="left">&nbsp;</td>
     <td align="left">&nbsp;</td>
    </tr>
   </tbody></table>
  </td>
 </tr>
 </form>

Post Views: 0

3 comments

Anonymous says:

July 16, 2023 at 10:48 am
I understand, that you only want to filter users for checked skills. If a checkbox is not checked, it does not matter, whether the user has the skill or not; correct?

Here you filter only for the positive skills:
```
if ($s_g_1) {  $query .=  " AND (generala = 1) "; }
if ($s_g_2) {  $query .=  " AND (generalb = 1) "; }
...
```
So if ‘Access’ and ‘Crystal’ are selected, you get all Users that have both skills (AND), regardless of their other skills.

(I don’t check with isset() in the if condition – I only want the condition in my query, if $s_g_1 is not 0 (= if the checkbox is checked). If you don’t know, whether $s_g_1 is defined, check it with !empty($s_g_1) to avoid a NOTICE.)
Log in to Reply
Anonymous says:

July 16, 2023 at 10:48 am
Use AND clause instead of OR to search for both
```
if(isset($s_g_1)){  $query .=  "AND (generala = '$s_g_1')";    }
if(isset($s_g_2)){  $query .=  "AND (generalb = '$s_g_2')";    }
```
Also I suggest, Use a loop to check these all.
Log in to Reply

I think you need an Input to select search of type “and” or “or”

$query .= "SELECT * FROM wordpress_candidate
WHERE (city = '$cty')
OR (state = '$stt')";
if (searchType = 'AND')
{
    $and_or = 'AND'
}
else 
{
    $and_or = 'OR'
}
if(isset($s_g_1) && ($s_g_1)) {  $query .=  $and_or + " (generala = '$s_g_1')";    }
if(isset($s_g_2) && ($s_g_2)) {  $query .=  $and_or + " (generalb = '$s_g_2')";    }

Multiple sql conditions based on checkboxes

Leave a Reply Cancel reply

3 comments

Social Network

Related posts

Leave a Reply Cancel reply

3 comments

Social Network