...

View Full Version : using checkboxes in php to pull data from mysql db



Battleship40
09-13-2011, 07:37 PM
Ok, I feel like I'm so close, but could use some help. I am trying to retrieve data from mySQL database via use of a form with checkboxes. This form is working somewhat except that it is only pulling one selection, not all that are selected. Can someone look at this and let me know how to fix this? Would greatly appreciate it!!





<html>
<head>
<title>Checkbox retrieval test</title>
</head>
<body>

<form method='post'>
<input type=checkbox name=fc[] value="red" class=2> red<br>
<input type=checkbox name=fc[] value="yellow" class=2> yellow<br>
<input type=checkbox name=fc[] value="orange" class=2> orange<br>
<input type=checkbox name=fc[] value="burgundy" class=2> burgundy<br>
<input type=submit name=submit>
</form>

<?

if(isset($_POST['submit']))
{
foreach($_POST['fc'] As $fc)
printf("%s<br>", $fc);
}

$fcList = "";

foreach($_POST['fc'] As $fc)
$fcList .= $fc . " ";


// Make a MySQL Connection
mysql_connect("", "", "") or die(mysql_error());
mysql_select_db("") or die(mysql_error());


$query = "SELECT * FROM findplantsdb WHERE Color='$fc'";


$result= mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);


echo "<h4> ", $row['Name'], " &nbsp; ", $row['Patent'], "</h4> ",$row['Common'], "<p> Height: ", $row['Hname'], "<br> Spread: ", $row['Sname'], "<br> Color: ", $row['Color'], "<br> Light: ", $row['Light'], "<br> Zone: ", $row['Zone'], "<p> <img src=http://www.domain.com/mobile/",$row['Picname'], " /> <p><p>", $row['Notes'], "<p><p> <hr width='50%' size='1' color='#A3A3A3'> <p>";
}


?>

</body>
</html>

mlseim
09-13-2011, 08:21 PM
Here's my untested script ...



<html>
<head>
<title>Checkbox retrieval test</title>
</head>
<body>

<form method='post'>
<input type=checkbox name=fc[] value="red" class=2> red<br>
<input type=checkbox name=fc[] value="yellow" class=2> yellow<br>
<input type=checkbox name=fc[] value="orange" class=2> orange<br>
<input type=checkbox name=fc[] value="burgundy" class=2> burgundy<br>
<input type=submit name=submit>
</form>

<?php

// start building the query string.
$query = "SELECT * FROM findplantsdb WHERE ";

foreach($_POST['fc'] As $fc){
// add each color choice onto the query string
$query .= "Color='$fc' OR ";
}
// terminate the OR with a blank.
$query .= "Color='xxx'";

// Make a MySQL Connection
mysql_connect("", "", "") or die(mysql_error());
mysql_select_db("") or die(mysql_error());

$result= mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);


echo "<h4> ", $row['Name'], " &nbsp; ", $row['Patent'], "</h4> ",$row['Common'], "<p> Height: ", $row['Hname'], "<br> Spread: ", $row['Sname'], "<br> Color: ", $row['Color'], "<br> Light: ", $row['Light'], "<br> Zone: ", $row['Zone'], "<p> <img src=http://www.domain.com/mobile/",$row['Picname'], " /> <p><p>", $row['Notes'], "<p><p> <hr width='50%' size='1' color='#A3A3A3'> <p>";
}


?>

</body>
</html>






.

BluePanther
09-13-2011, 08:45 PM
Here's my untested script ...



<html>
<head>
<title>Checkbox retrieval test</title>
</head>
<body>

<form method='post'>
<input type=checkbox name=fc[] value="red" class=2> red<br>
<input type=checkbox name=fc[] value="yellow" class=2> yellow<br>
<input type=checkbox name=fc[] value="orange" class=2> orange<br>
<input type=checkbox name=fc[] value="burgundy" class=2> burgundy<br>
<input type=submit name=submit>
</form>

<?php

// start building the query string.
$query = "SELECT * FROM findplantsdb WHERE ";

foreach($_POST['fc'] As $fc){
// add each color choice onto the query string
$query .= "Color='$fc' OR ";
}
// terminate the OR with a blank.
$query .= "Color='xxx'";

// Make a MySQL Connection
mysql_connect("", "", "") or die(mysql_error());
mysql_select_db("") or die(mysql_error());

$result= mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);


echo "<h4> ", $row['Name'], " &nbsp; ", $row['Patent'], "</h4> ",$row['Common'], "<p> Height: ", $row['Hname'], "<br> Spread: ", $row['Sname'], "<br> Color: ", $row['Color'], "<br> Light: ", $row['Light'], "<br> Zone: ", $row['Zone'], "<p> <img src=http://www.domain.com/mobile/",$row['Picname'], " /> <p><p>", $row['Notes'], "<p><p> <hr width='50%' size='1' color='#A3A3A3'> <p>";
}


?>

</body>
</html>






.

Your script will work, but i would implement a couple changes before using it. Instead of the for loop and num_rows, just use a while loop - it will loop through every row for you.


// Replace
$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);


echo "<h4> ", $row['Name'], " &nbsp; ", $row['Patent'], "</h4> ",$row['Common'], "<p> Height: ", $row['Hname'], "<br> Spread: ", $row['Sname'], "<br> Color: ", $row['Color'], "<br> Light: ", $row['Light'], "<br> Zone: ", $row['Zone'], "<p> <img src=http://www.domain.com/mobile/",$row['Picname'], " /> <p><p>", $row['Notes'], "<p><p> <hr width='50%' size='1' color='#A3A3A3'> <p>";
}

// With
while($row = mysql_fetch_array($result)){
echo "<h4> ", $row['Name'], " &nbsp; ", $row['Patent'], "</h4> ",$row['Common'], "<p> Height: ", $row['Hname'], "<br> Spread: ", $row['Sname'], "<br> Color: ", $row['Color'], "<br> Light: ", $row['Light'], "<br> Zone: ", $row['Zone'], "<p> <img src=http://www.domain.com/mobile/",$row['Picname'], " /> <p><p>", $row['Notes'], "<p><p> <hr width='50%' size='1' color='#A3A3A3'> <p>";
}

Also, instead of your code to terminate the or, you can just replace $query with a sub string of itself to cut off the or, instead of adding an unneccesary condition:


// Replace
$query .= "Color='xxx'";
// with
$query = substr($query, 0, -4);

mlseim
09-13-2011, 09:33 PM
I wasn't sure if the extra query condition takes less processing than the substr function.
Just for kicks ... it would be interesting to compare them somehow.

BluePanther
09-13-2011, 09:45 PM
I would say the condition would take longer, as it's basically kind of a search (basically looks at each row and checks if the fields match the condition, I think lol) whereas the sub-string is merely knocking a bit off the string. I'm not sure, would be quite interesting to compare them with microtime() :) Can't do it just now though, if you do let me know the results!

mlseim
09-13-2011, 09:48 PM
I always come across situations like this. I usually choose the method
that is easiest to see (what is happening). Makes explaining and troubleshooting
easier for the novice scripters that are asking the questions. I would guess that
my choices for many methods are not the most efficient :o

BluePanther
09-13-2011, 10:28 PM
As long as your code is well commented everything should be fine :P Commenting was drummed into me something rotten at school - I had to comment every single line, even if I was just initialising a variable. Ridiculous, but it's made me very loose with commenting so I suppose it was a win... haha

Battleship40
09-14-2011, 02:35 PM
You guys are wonderful! I just got into the office and will test this out now. Quick question: if I wanted to add more checkboxes such as sun, shade for a LIGHT column in my db, how would I add that to this? Folks like searching for a number of factors when dealing with plants and I would like to pull from different db columns such as LIGHT. Thanks again for all your help!!

mlseim
09-14-2011, 03:12 PM
Add the column 'Light' to the query as well ....

This would be for one set of checkboxes.



// start building the query string.
$query = "SELECT * FROM findplantsdb WHERE ";

foreach($_POST['fc'] As $fc){
// add each color choice onto the query string
$query .= "Color='$fc' OR Light='$fc' OR ";
}
// terminate the OR with a blank.
$query .= "Color='xxx'";




For 2 sets of checkboxes ...



// start building the query string.
$query = "SELECT * FROM findplantsdb WHERE ";

//checkbox array for color, - name=fc[]
foreach($_POST['fc'] As $fc){
// add each color choice onto the query string
$query .= "Color='$fc' OR ";
}

//checkbox array for light, - name=fl[]
foreach($_POST['fl'] As $fl){
// add each color choice onto the query string
$query .= "Light='$fl' OR ";
}

// terminate the OR with a blank.
$query .= "Color='xxx'";





.

Battleship40
09-15-2011, 02:57 PM
Thank you! Quick question: how do I keep the checkboxes checked?

mlseim
09-15-2011, 06:29 PM
Some choices to make for that ....

1) Remain checked for only when the user is viewing the site "at this time".
PHP SESSION method

2) Remain checked for any time it's viewed on "that computer".
COOKIE method

In either case, an array can be saved in a COOKIE or SESSION.
You have your checkboxes in an array.

The main scripting you will be doing is when you display the form itself.
You'll have to do some comparing of the checkbox values to the array,
determining if you should set the "checked" or not.



.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum