Multiple checkbox mysql query

Codewalkers Forums Sponsor:

July 10th, 2008, 09:10 AM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Multiple checkbox mysql query

I am not sure the best way to do this:

I have a form with about 50 tick box's of different issues which will link to a field in the database.

What's the best way to make this form search the database for these issues, some being ticked and some not.

here is an example of the form:

PHP Code:


		
			
<input name="criteria[Animal Testing Medical Discovery & Development]" type="checkbox" id="Animal Testing Medical Discovery & Development" value="1">

<input name="criteria[Animal Testing Medical Ingredients]" type="checkbox" id="Animal Testing Medical Ingredients" value="1">

<input name="criteria[Animal Testing Non-Medical Discovery & Development]" type="checkbox" id="Animal Testing Non-Medical Discovery & Development" value="1">

<input name="criteria[Animal Testing Non-Medical Ingredients]" type="checkbox" id="Animal Testing Non-Medical Ingredients" value="1">

Thanks!!

July 10th, 2008, 09:55 AM

Osiris

Contributing User

Join Date: May 2008

Location: Sussex

Posts: 566

Time spent in forums: 1 Week 2 Days 19 h 54 m 33 sec
Reputation Power: 2

what exactly do u want the search results to display? and what are in the fields to which the checkboxes correspond? Sorry, just trying to understand what you have/want

__________________
~~==~~ Whoever said nothing is impossible never tried pushing a revolving door ~~==~~

July 10th, 2008, 01:18 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Quote:

Originally Posted by Osiris

what exactly do u want the search results to display? and what are in the fields to which the checkboxes correspond? Sorry, just trying to understand what you have/want

The data that will display is anything which corresponds to the checkbox for example if these check box's are ticked:

PHP Code:


		
			
<input name="criteria[Animal Testing Medical Discovery & Development]" type="checkbox" id="Animal Testing Medical Discovery & Development" value="1">

<input name="criteria[Animal Testing Medical Ingredients]" type="checkbox" id="Animal Testing Medical Ingredients" value="1">

when searching the query would return anything in the database which where the criteria field = Animal Testing Medical Ingredients OR Animal Testing Medical Discovery & Development.

Hope this helps thanks!

July 10th, 2008, 04:23 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

Looks like you already have it setup so each checkbox is part of an array. checkboxes only pass the value and name if it is checked so something like this might work:

PHP Code:


		
			
if(isset($_POST['criteria']) && !empty($_POST['criteria'])){

    foreach($_POST['criteria'] as $key=>$value){

        if($value==1) $criteria[] = "`columnName`='".mysql_escape_string($key)."'";

    }

    $criteria = implode(' OR ', $criteria);

}

$query = "SELECT * FROM `tableName` WHERE $criteria";

echo $query;

July 10th, 2008, 04:43 PM

drewj2k

Contributing User

Join Date: Jul 2007

Posts: 162

Time spent in forums: 2 Days 5 h 14 m 42 sec
Reputation Power: 3

Quote:

Originally Posted by IAmALlama

Looks like you already have it setup so each checkbox is part of an array. checkboxes only pass the value and name if it is checked so something like this might work:

PHP Code:


		
			
if(isset($_POST['criteria']) && !empty($_POST['criteria'])){

    foreach($_POST['criteria'] as $key=>$value){

        if($value==1) $criteria[] = "`columnName`='".mysql_escape_string($key)."'";

    }

    $criteria = implode(' OR ', $criteria);

}

$query = "SELECT * FROM `tableName` WHERE $criteria";

echo $query;

That worked perfect thanks !!

October 19th, 2009, 06:08 AM

smartans

Registered User

Join Date: Oct 2009

Posts: 3

Time spent in forums: 20 m 7 sec
Reputation Power: 0

Assistance

Hi. Can i have the full code? I need that script too but cant seem to get them work. I need to display the output within the same page as the checkbox search filter.
Thanks in advance!

October 19th, 2009, 03:11 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

the code I gave should be all that is needed to get it working. what exactly are you having trouble with? the form posting to the code I provided should look something similar to:

Code:

<form method='post' action='somePage.php'>
<input type='checkbox' name='criteria[1]' value=1>
<input type='checkbox' name='criteria[2]' value=1>
<input type='checkbox' name='criteria[3]' value=1>
<input type='checkbox' name='criteria[4]' value=1>
<input type='checkbox' name='criteria[5]' value=1>
<input type='submit' value='Go'>
</form>

The value on each one should be 1 to basically say "i'm checked" and the name should be criteria[someNumber] where "someNumber" is the id value for the specific row from the database.

October 20th, 2009, 12:50 AM

smartans

Registered User

Join Date: Oct 2009

Posts: 3

Time spent in forums: 20 m 7 sec
Reputation Power: 0

RE:Assistance

I am a newbie in php and mysql. I want the search filter to filter out according to the column name. My column name in mysql is 'category'. What i need to do is to change 'criteria' to 'category'

My Search Form:

Code:

<table width="280" border="1">
                    <tr>
                      <td valign="top" height="36"><p>&nbsp;</p></td>
                    </tr>
                    <tr>
                      <td align="left"><font face="Lucida Calligraphy" color="#8d0104">Search Filter:</font></td>
                    </tr>
                    <tr>
                      <td align="left"><font face="Lucida Calligraphy" color="#310100">&nbsp;&nbsp;&nbsp;&nbsp;Product Type</font></td>
                    </tr>
                    <tr>
                      <td><table width="243" height="132" align="right">
                        <tr>
                          <td width="311"><label>
                            <input type="checkbox" name="Product_Type" value="Single Wine" id="Product_Type_0" />
                            Single Wine</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Product_Type" value="Wine Bottle Opener" id="Product_Type_1" />
                            Wine Bottle Opener</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Product_Type" value="Wine Clipper" id="Product_Type_2" />
                            Wine Clipper</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Product_Type" value="Wine Glass" id="Product_Type_3" />
                            Wine Glass</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Product_Type" value="Wine Stopper" id="Product_Type_4" />
                            Wine Stopper</label></td>
                        </tr>
                      </table></td>
                    </tr>
                    <tr>
                      <td><img src="Horizontal_Divider.png" width="280" height="8" /></td>
                    </tr>
                    <tr>
                      <td><font face="Lucida Calligraphy" color="#310100">&nbsp;&nbsp;&nbsp;&nbsp;Price Range</font></td>
                    </tr>
                    <tr>
                      <td><table align="right" width="243">
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Price_Range" value=">50" id="Price_Range_0" />
                            €0 - €49</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Price_Range" value="50-99" id="Price_Range_1" />
                            €50 - €99</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Price_Range" value="100-149" id="Price_Range_2" />
                            €100 - €149</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Price_Range" value="150-199" id="Price_Range_3" />
                            €150 - €199</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Price_Range" value="200-249" id="Price_Range_4" />
                            €200 - €249</label></td>
                        </tr>
                        <tr>
                          <td><label>
                            <input type="checkbox" name="Price_Range" value="<250" id="Price_Range_5" />
                            €250 and Above</label></td>
                        </tr>
                      </table></td>
                    </tr>
                    <tr>
                      <td>&nbsp;</td>
                    </tr>
                    <tr>
                      <td align="center">
                      <script type="text/javascript" charset="utf-8">
    $(document).ready(function(){
        $('#submit').hover(
            function(){ // Change the input image's source when we "roll on"
                $(this).attr({ src : 'Search_FilterBtnMO.png'});
            },
            function(){ // Change the input image's source back to the default on "roll off"
                $(this).attr({ src : 'Search_FilterBtn.png'});             }
        );
    });
</script>
                      <input name="submit" type="image" id="submit" src="Search_FilterBtn.png" width="95" height="34" ></td>
                    </tr>
                  </table>

I also need my search filter to be in a range for the prices. My field name for prices is 'price'.

My DB connection is only:

Code:

mysql_connect('localhost','username','userpass');
mysql_select_db('dbname');
$query = "SELECT * FROM tablename";

I would like to thank you for ur assistance!

October 21st, 2009, 12:38 PM

smartans

Registered User

Join Date: Oct 2009

Posts: 3

Time spent in forums: 20 m 7 sec
Reputation Power: 0

Code:

<body>
<form method="post" action="" >
<input name="categories[BottleOpener]" type="checkbox" id="BottleOpener" value="1">Bottle Opener<br /> 

<input name="categories[WineClipper]" type="checkbox" id="WineClipper" value="1">Wine Clipper<br />

<input name="categories[WineGlass]" type="checkbox" id="WineGlass" value="1">Wine Glass<br /> 

<input name="categories[WineBottle]" type="checkbox" id="WineBottle" value="1">Wine Bottle<br />

<input type='submit' value='Search'>

</form>

<?php
mysql_connect('localhost','winecom1_bizbyte','jeri  jeri');
mysql_select_db('winecom1_wine7000');



if(isset($_POST['categories']) && !empty($_POST['categories'])){ 
    foreach($_POST['categories'] as $key=>$value){ 
        if($value==1) $criteria[] = "category='".mysql_escape_string($key)."'"; 
    } 
    $criteria = implode(' OR ', $criteria); 
} 
$query = "SELECT * FROM wineaccessories WHERE $criteria"; 
echo $criteria;  
?>
</body>

Im able to get my code running, but however, it is not from my database. I need the output to be from my database where the category="the category in my database".

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: