Two checkboxes - PHP Query

[invision2]

Hi,

I have 2 check boxes on a form on my web site.

<input name="permanent" id="permanent" value="1" />
<input name="temp" id="temp" value="2" />

Now I'm basically using the values so I can query my database for any permanent or temp jobs (WHERE type = '1').

However, I'm a little unsure. What if the user selects both or none?

Would this mean using an IF statement, and how would I then use this with query in my db.

So I've got 4 instances.

Only selected temp
Only selected permanent
Selected both
Selected none


Any takers?


Many thanks

[AnthonySterling]

Quick stab before lunch.

<?php
$sSQL = 'SELECT * FROM jobs WHERE 1 = 1';

switch(count($_POST['contract_type']))
{
    case 2:
        $sSQL .= " AND contract_type = 'permanent' AND contract_type = 'temporary'";
    break;
    
    case 1:
        $sSQL .= sprintf(
            " AND contract_type = '%s'",
            array_key_exists('permanent', $_POST['contract_type']) ? 'permanent' : 'temporary'
        );
    break;

    default:
        #nowt.
    break;
}
?>

Not very flexible though.

Or, maybe...

<?php
$sSQL = 'SELECT * FROM jobs WHERE 1 = 1';
if(is_array($_POST['contract_type']))
{
    foreach(array_keys($_POST['contract_type']) as $sCondition)
    {
        $sSQL .= sprintf(
            " AND contract_type = '%s'",
            mysql_real_escape_string($sCondition)
        );
    }
}
?>

[invision2]

Hey Anthony,

Thanks for this.

Will give it a crack and get back.

Off for lunch in a mo.

[AnthonySterling]

Additionally, I guess the 'AND' could also be an 'OR' dependent on your requirements of course.

Enjoy.

[TomB]

I'd store the job type as a number, that way you can do better checks against it:

E.g.

1 = Temp
2 = Permenent

Now use your checkboxes like this:

<input name="type[]" id="permanent" value="1" />
<input name="type[]" id="temp" value="2" />

And in PHP bitwise OR the numbers:

$type = 0;
foreach ($_POST['type'] as $currentType) {
    $type |= $currentType;
} 

Now your query is simplified to:

$sql = 'SELECT * FROM jobs WHERE contract_type & ' . $type; 

Your data will look like

Job1 1
Job2 2

[AnthonySterling]

Hold on, whats this then?

 |= 

[TomB]

Bitwise or

http://us3.php.net/manual/en/languag...rs.bitwise.php

|= works the same as += or .= but with |

[AnthonySterling]

Bitwise or

http://us3.php.net/manual/en/languag...rs.bitwise.php

|= works the same as += or .= but with |

Thanks!

[UFTimmy]

You can also store the values in a comma delimited string and take advantage of MySQL's FIND_IN_SET function to find just the value you're looking for.

The bit stuff works great and I use it often, but it does have a limit to the number of options you can have depending on the maximum integer size in both MySQL and PHP.. it's usually 32 options.

[TomB]

Yeah, but for 2 checkboxes the 32 item limitation isn't going to be an issue

Even if it were, it's easy enough to add more possible options by using multiple columns.

[mmj]

In SilverBulletUK's example, the AND must be changed to OR, or selecting both won't return anything.

This may be simpler:

$options = array();
if (!empty($_POST['permanent']))
  $options[] = "contract_type='permanent'";
if (!empty($_POST['temp']))
  $options[] = "contract_type='temp'";

if (empty($options))
  trigger_error('Must specify at least one of \'permanent\', \'temp\'', E_USER_ERROR);

$sql = "SELECT * FROM jobs WHERE " . implode(' OR ', $options); 

[AnthonySterling]

In SilverBulletUK's example, the AND must be changed to OR, or selecting both won't return anything.

Thanks mmj.

[Lochan Yulian]

\n is the newline character used throughout all software.More precisely, it should be put \r\n depending on the platform, it could be \r or \n which is the character for a new line.

[invision2]

Many thanks for all the replies.
I managed to do it myself with a fairly brutal bit of ugly code. Will post soon

Thanks again.