Page 1 of 1

Database Query, based on check boxes check or not.

Posted: Fri Aug 25, 2006 3:32 am
by matt1019
Hi Guys,

Top of the Mornin' to ya'll! ;)

okay, here's the situation I am currently facing:

I have 6 checkboxes:

red, blue, brown, black, white, and green.

Based on which one the user "checks", the results would be displayed from the db.

So if user checks red, blue, and brown boxes, the result page should display those three colored shirts and NOT DISPLAY black, white and green colored shirts.

Here's the problem:
How to query this? I mean, if I do IF statements, it would be too many, long/tedious, and error prone!

Any simpler way to approach/tackle this?

Here's my html/php code so far:

index.php

Code: Select all

echo "<form name='mod_color' method='post' action='index.php' enctype='multipart/form-data'>";
echo "<input type='checkbox' name='red' $redcheck onclick=\"reload()\";>&nbsp;<img src='images/options/red.png' alt='red'>&nbsp;Red";
echo "<input type='checkbox' name='blue' $bluecheck onclick=\"reload()\";>&nbsp;<img src='images/options/blue.png' alt='blue'>&nbsp;Blue";
echo "<input type='checkbox' name='brown' $browncheck onclick=\"reload()\";>&nbsp;<img src='images/options/brown.png' alt='brown'>&nbsp;Brown";
echo "<input type='checkbox' name='black' $blackcheck onclick=\"reload()\";>&nbsp;<img src='images/options/black.png' alt='black'>&nbsp;Black";
echo "<input type='checkbox' name='white' $whitecheck onclick=\"reload()\";>&nbsp;<img src='images/options/white.png' alt='white'>&nbsp;White";
echo "<input type='checkbox' name='green' $greencheck onclick=\"reload()\";>&nbsp;<img src='images/options/green.png' alt='green'>&nbsp;Green";
echo "</form>";
on the top of the page, the javascript function:

Code: Select all

echo "<script type=\"text/javascript\"> 
	function reload(){
		document.mod_color.submit();
	}
	</script>";
I am using POST in conjunction with isset to see if the checkboxes were checked or not.... if they were, then I would keep them checked:
here's how I do that:

Code: Select all

$red = (isset($_POST['red']) ? 1 : 0);

if ($red == '1'){
	$redcheck = "checked";
}else{
	$redcheck = "";
}
...
Thanks in advance for the help :)

-Matt

Posted: Fri Aug 25, 2006 3:39 am
by onion2k
I take it you've hardcoded all your T-Shirt colours into the page? That's a pretty bad idea for a start. If you decide to start selling yellow T-Shirts you're going to have to rewrite lots and lots of code.

As for how to get them from the database .. just put the colours selected into an comma seperated list (implode() with an array ideally), then in your SQL use "where colour IN ('red','blue','green')" .. of course, you should be using ids rather than the string for the name.

Posted: Fri Aug 25, 2006 2:07 pm
by matt1019
Hi onion2k!

Ok, I have taken your suggestion and used an array instead of hardcoding the names into the form.

Here's where I am right now,

HTML/Form Part

Code: Select all

echo "<form name='mod_color' method='post' action='index.php' enctype='multipart/form-data'>";
echo "<input type='checkbox' id='color[]' name='red' onclick=\"reload()\";>&nbsp;<img src='images/options/red.png' alt='red'>&nbsp;Red";
echo "<input type='checkbox' id='color[]' name='blue' onclick=\"reload()\";>&nbsp;<img src='images/options/blue.png' alt='blue'>&nbsp;Blue";
echo "<input type='checkbox' id='color[]' name='brown' onclick=\"reload()\";>&nbsp;<img src='images/options/brown.png' alt='brown'>&nbsp;Brown";
echo "<input type='checkbox' id='color[]' name='black' onclick=\"reload()\";>&nbsp;<img src='images/options/black.png' alt='black'>&nbsp;Black";
echo "<input type='checkbox' id='color[]' name='white' onclick=\"reload()\";>&nbsp;<img src='images/options/white.png' alt='white'>&nbsp;White";
echo "<input type='checkbox' id='color[]' name='green' onclick=\"reload()\";>&nbsp;<img src='images/options/green.png' alt='green'>&nbsp;Green";
echo "</form>";
The javascript remains the same @ the top of inex.php page:

Javascript Function

Code: Select all

echo "<script type=\"text/javascript\">
        function reload(){
                document.mod_color.submit();
        }
        </script>";
and here is what I have as far as the sql query goes. (After the javascript function):


SQL QUERY

Code: Select all

$color = $_POST['color']; // Gets the selected colors from the form data
$getuhl = implode(",", $color);

if ($color == ''){
	$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts ORDER BY id DESC LIMIT 100");
}else{
	$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in ('$getuhl') ORDER BY id DESC LIMIT 100");
}
so my page structure looks like this:

  • Javascript Function

    SQL QUERY

    HTML/Form Part
With the above code running, the page gives me this error:
Notice: Undefined index: color in index.php on line 16

Warning: implode() [function.implode]: Bad arguments. in index.php on line 17
What to do? :?

-Matt

Posted: Fri Aug 25, 2006 3:52 pm
by matt1019
Allrighty!!

Great News!!! ok, partly great news!

Great news nonetheless!

Ok, i got the script to work as long as only "one" checkbox is checked! If I select multiple checkboxes (i.e., red & brown) nothing shows up. However, If I de-select brown, the red shirts would show (with appropriate listings that I made using the while loop).

So, to re-phrase the question:

this is the line that does not work for multiple arrays:

Code: Select all

$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in ('$getuhl') ORDER BY id DESC LIMIT 100");
Let's assume I select "red" as the color of choice for shirts... (DONT JUDGE ME! :evil: only an example :? )
When this is interpreted by php, it does this

Code: Select all

$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in red ORDER BY id DESC LIMIT 100");
but if I select "red" and "brown"
This is interpreted by php like this:

Code: Select all

$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in red,brown ORDER BY id DESC LIMIT 100");
which does not work :? :?
anybody can shed light on this?

Other than that, everything else works as expected :) But the script is not complete if the main functionality is not working...

need final guidelines on this one guys, help please?

Posted: Fri Aug 25, 2006 5:22 pm
by RobertGonzalez
Are you using the parantheses?

Code: Select all

$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in (red,brown) ORDER BY id DESC LIMIT 100");

Posted: Fri Aug 25, 2006 5:35 pm
by matt1019
Everah wrote:Are you using the parantheses?

Code: Select all

$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in (red,brown) ORDER BY id DESC LIMIT 100");
Yup. As in:

Code: Select all

$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in ('$getuhl') ORDER BY id DESC LIMIT 100");
this:

Code: Select all

$colorquery = dbquery("SELECT * FROM ".$db_prefix."shirts where shirt_color in red,brown ORDER BY id DESC LIMIT 100");
was just my interpretation of how php would interpret what I wrote.

when making the acutal query, i am not using the second code that I pasted... I am using the code with ('$getuhl')

again, the code works as long as only one check box is checked... if multiple checkboxes are checked, it displays nothing.

Thanks Everah!

-Matt

Posted: Fri Aug 25, 2006 5:42 pm
by matt1019
No worries guys,

I found a LONG AND TEDIOUS way of doing this :(

It's long and tedious, but it's working as of right now.

If you are interested (or if anyone is facing a similar problem as I was) here's the link to the post that helped me:

http://www.phpbuilder.com/board/showpos ... ostcount=6

Look at post #6.

If there is a "better" or shorter way, then please, do let me know....

Thanks for the help guys,

-Matt

Posted: Fri Aug 25, 2006 7:07 pm
by RobertGonzalez
Ok, why not...

Code: Select all

<?php
$color = $_POST['color']; // Gets the selected colors from the form data
$append_sql = '';
if (is_array($color))
{
    $append_sql .= 'WHERE shirt_color IN (\'';
    $color_count = count($color);
    for ($i = 0; $i < $color_count;   $i)
    {
        $delim = ( $i < $color_count - 1 ) ? ', ' : '';
        $append_sql .= $color[$i] . $delim;
    }
    $append_sql .= '\')';
}
else
{
    if (!empty($color))
    {
        $append_sql .= 'WHERE shirt_color = \'$color\'';
    }
}

$colorquery = dbquery('SELECT * FROM ' . $db_prefix . 'shirts ' . $append_sql . ' ORDER BY id DESC LIMIT 100');
?>

Posted: Sat Aug 26, 2006 4:03 am
by onion2k
Everah wrote:Ok, why not...

Code: Select all

if (is_array($color))
{
    $append_sql .= 'WHERE shirt_color IN (\'';
    $color_count = count($color);
    for ($i = 0; $i < $color_count;   $i)
    {
        $delim = ( $i < $color_count - 1 ) ? ', ' : '';
        $append_sql .= $color[$i] . $delim;
    }
    $append_sql .= '\')';
}
That whole block could be replaced with...

Code: Select all

if (is_array($color))
{
    $append_sql .= "WHERE shirt_color IN (".implode(",",$color).")";
}

Posted: Sat Aug 26, 2006 7:29 pm
by matt1019
onion2k wrote:
Everah wrote:Ok, why not...

Code: Select all

if (is_array($color))
{
    $append_sql .= 'WHERE shirt_color IN (\'';
    $color_count = count($color);
    for ($i = 0; $i < $color_count;   $i)
    {
        $delim = ( $i < $color_count - 1 ) ? ', ' : '';
        $append_sql .= $color[$i] . $delim;
    }
    $append_sql .= '\')';
}
That whole block could be replaced with...

Code: Select all

if (is_array($color))
{
    $append_sql .= "WHERE shirt_color IN (".implode(",",$color).")";
}
8O 8O 8O Let me go try that!!!!

-Matt

Posted: Sat Aug 26, 2006 7:42 pm
by RobertGonzalez
Duh... I forgot that I can be simple. I hate it when that happens.