multiple checkboxes and SELECT LIKE

Anon

Hi,

I have a page with a big number of checkboxes (right now around 20). A visitor to this page is supposed to check them (as many as he/she wishes) and perform a search on the database for all items that match his choice.

I use the following code:

and so on...

In the database I have two columns "e_mail" and "section". The first column is self-explanatory. In the "section" column all data is separated by comma e.g. one field can contain "hardware, multimedia" and another "software, multimedia".

Then the searching code:

$result_user = mysql_query("SELECT DISTINCT (e_mail) FROM test WHERE (section LIKE '%$section1%' OR section LIKE '%$section2%' OR section LIKE '%$section3%' OR section LIKE '%$section4%' OR section LIKE '%$section5%' OR section LIKE '%$section6%' OR section LIKE '%$section7%' OR section LIKE '%$section8%' OR section LIKE '%$section9%' OR section LIKE '%$section10%')");

The idea is to list all e_mails where at least one item from the "section" field is true. For some reason the query displays all e-mails regardless of the checkboxes that were checked.

Any idea?

Witold

Anon

Hi,

that's because unchecked fieldvariables are empty.

OR section LIKE '%$section8%'

evals with unchecked section8 to

OR section LIKE '%%'

That matches every entry containing at least two chars.
How to go: build a dynamic query which depends only on set (checked) fields
Best regards,

King Louie

Anon

OK. I've used the following code:

if (isset($section))
{
$keyword = explode(" ",$section);
$item = current($keyword);

$item1 = $item[0];
$item2 = $item[1];
$item3 = $item[2];
$item4 = $item[3];
$item5 = $item[4];
$item6 = $item[5];
$item7 = $item[6];
$item8 = $item[7];
$item9 = $item[8];
$item10 = $item[9];
}
else
echo "no data in variables ";
/this message doesn't show up so some variables are passed on from the form, but
when I wrote echo $keyword; it turned out that it was only the last variable no matter how many had been checked/
if ($socket) {
$result_user = mysql_query("SELECT DISTINCT (e_mail) FROM test WHERE (section LIKE '%$item1%' ) OR (section LIKE '%$item2%' ) OR (section LIKE '%$item3%' ) OR (section LIKE '%$item4%' ) OR (section LIKE '%$item5%' ) OR (section LIKE '%$item6%' ) OR (section LIKE '%$item7%' ) OR (section LIKE '%$item8%' ) OR (section LIKE '%$item9%' ) OR (section LIKE '%$item10%' )");

What am I doing wrong? Any tips?

Witold

Anon

Same problem. $item[3] is empty, then so is $item3, and you're back to "section LIKE '%$item3%'" becoming "section LIKE '%%'".

If you're desperate to avoid building the query dynamically for some reason, preinitialise all your $item1 etc. with something that cannot appear as an item. Then continue with the code you have (or replace $item with $section and use the code you had at first).

This will result in a horribly inefficient query, but that's the price you'll be paying.

Me, I'd do something more closer to:

/ If there is a $setion array, break it up, wrap each part in a LIKE subclause, then OR all the parts together into one big WHERE clause.
/
if (isset($section))
{
$parts = explode(" ",$section);
foreach($parts as $part)
$part="(section LIKE '%$part%')";
$sections = join(' OR ', $parts);
}

$sql="SELECT DISTINCT e_mail FROM mytable WHERE ".$sections;

I think that looks about right...

Anon

Hayley,

It's not that I insist on doing it this way, but because I've no idea how to do it dynamically:-)

Besides, I've tried to run the code you proposed but I got an error message 'not a valid result resource'.

Regards,

Witold

Anon

I have rewritten the code into something like:

if (isset($section))
{
$parts = explode(" ",$section);
for($i = 0; $i < sizeof($parts); $i++) {
$part = $section[$i];
}

$sql = "SELECT DISTINCT(e_mail) FROM test2 WHERE (section LIKE '%$part%')";

To my surprise, only 2 variables get passed on to the sql query, even if I check 3,4 or 5 checkboxes!!

What do you think about it?

Witold

Anon

Your code makes no sense; you're looking at the size of the $parts array, and assuming that the string $section is the same size (and an array).

Furthermore, as you go through the loop, you set and reset the value of $part multiple times, but never use it. All that's left in $part after the loop ends is the final value inserted into it.

Try echoing $sql instead of or before calling mysql_query() on it (and my code) to see exactly what is being generated. That can help in determining what's going on and can often help in determining what needs fixing.

Anon

"Furthermore, as you go through the loop, you set and reset the value of $part multiple times, but never use it. All that's left in $part after the loop ends is the final value inserted into it."

'course, my code has the same problem - I forgot the old value/reference distinction.

So I rewrite it with a wee subsidiary function:
function wrap_part($a)
{ return "(section LIKE '%$a%')";
}

...
if (isset($section))
{
$parts = explode(" ",$section);
$parts = array_map('wrap_part', $parts);
$sections=join(' OR ', $parts);
}
$sql="SELECT DISTINCT (e_mail) FROM mytable WHERE ".$sections;
...

That's what happens when I can't experiment or test.

Anon

Thanks for help anyway. I'm new to arrays so I'm sort of moving in the dark::-). If the code I rewrote is incorrect how to hell do I get the desired result?!

I've experimented with checking different checkboxes and the result is 100% correct. I get e_mails that match criteria from the form! What do you say to it?

Witold

Anon

I can't say how to rework the code you wrote because I don't understand it. But if you say it's 100% correct, what's the problem? What's the value of $sql? Did you try my corrected version? Have you read the manual?

Anon

Hayley Watson wrote:

I only hoped someone could explain which line does what so that in the end I get the desired result:-)
Hayley,

This is the final piece of code that is fully functional. Any explanation from you?

if ($socket) {

$parts = explode(" ",$keyword);
$sql = "SELECT DISTINCT(e_mail) FROM test2 WHERE section LIKE '%$keyword%' OR (";
for($i = 0; $i < sizeof($keyword); $i++) {
if($i > 0) {
$sql .= " OR ";
}
$sql .= "(section LIKE '$keyword[$i]')";
}
$sql .= ");";

witold

Anon

Me explain it? You wrote it :-). If it works I don't see how.

First off, you're not using the $parts array at all.

If $keyword is something like "foo bar baz" then you'll have "...WHERE section LIKE '%foo bar baz%'..." followed by something else I suspect will be bogus.

What does $sql equal at the end of it?

Anon

This code is a cut-and-paste of a working example:

------------8<-----------------
<?

function wrap_part($a)
{ return "(section LIKE '%$a%')";
}

$section='foo bar baz blarg wibble';

if (isset($section))
{
$parts = explode(" ",$section);
$parts = array_map('wrap_part', $parts);
$sections=join(' OR ', $parts);
}
$sql="SELECT DISTINCT (e_mail) FROM mytable WHERE ".$sections;

echo $sql;

?>
------------>8-----------------

Produces:

SELECT DISTINCT (e_mail) FROM mytable WHERE (section LIKE '%foo%') OR (section LIKE '%bar%') OR (section LIKE '%baz%') OR (section LIKE '%blarg%') OR (section LIKE '%wibble%')