Checkbox form data and a MYSQL query

[numnum]

Okay, I'm stuck. After 3 days of stubbornly trying to figure out something that's probably fairly basic, I'm posting this message.

I'm teaching myself how to use PHP to pass HTML form data to a MYSQL db table and return the data to the browser according to the MYSQL query. Here's where I'm stumped: How do I successfully pass multiple values -- either via the CHECKBOX or SELECT (listbox) control -- and return all records matching those values? For example, assume field 1 ("name") lists 10 people and field 2 lists four depts: A, B, C and D (each person is a member of one dept):

"name" | "dept"
Name1 | B
Name2 | C
Name3 | B
Name4 | A
Name5 | A
Name6 | D
Name7 | C
Name8 | B
Name9 | D
Name10 | A

Either a listbox or a checkbox control can provide four selections and allow the user to select one or more departments (dept), submit the selection(s), and view all matching rows. Using the listbox (select) control:

<form method="get" action="page_name.php">
<SELECT name="a[]" multiple="true">
<OPTION value="A">Dept A</OPTION>
<OPTION value="B">Dept B</OPTION>
<OPTION value="C">Dept C</OPTION>
<OPTION value="D">Dept D</OPTION>
</SELECT>
<input type="SUBMIT" value="Submit">
</form>

Or, using the checkbox control:

<form method="get" action="page_name.php">
<INPUT name="myform[]" type="checkbox" value="A">Dept A
<INPUT name="myform[]" type="checkbox" value="B">Dept B
<INPUT name="myform[]" type="checkbox" value="C">Dept C
<INPUT name="myform[]" type="checkbox" value="D">Dept D
<input type="SUBMIT" value="Submit">
</form>

I have no trouble simply echoing each checked value:

foreach($myform as $value) {
echo $value;
}


A MYSQL query must assign a single variable (such as $a) to the entire array of value(s) the user selects. But how do I associate the 'dept' field with the array ($a)? The following query doesn't work (I'm pasting it here just to show where I am in this task):

$a = $_GET['myform'];
$query= "SELECT * FROM mytable WHERE dept='$myform' ";
$result = mysql_query($query);
foreach($a as $value) {
while ($row=mysql_fetch_array($result)) {
echo $row['name'] , " | " , $row['dept'] , "<br>";
}
}

How must I revise this query?

[Dinkar]

Forget all computer languages and tell in simple English, what your script should do with database.

Then translate it in MySQL query. It's that simple :)

Just try!

[httpwebwitch]

debugging tip: on the "action" page "page_name.php", add this:

print_r($_GET);

...and see what it outputs.
If the data you want is in there, then you're half way to winning. If it's not, then it's the form that's the problem, not the handling script. :)
And you'll also see the array structure of the $_POST, which will help you figure out how to iterate through the data and construct your queries properly.

another debugging tip:
instead of this:
$result = mysql_query($query);
do this:
$result = mysql_query($query) or die(mysql_error());

if there's an error in your SQL query, the "die" and mysql_error() will tell you what that error is. Adjust your code until the query is correct. Then make sure you remove that die() before you launch the site... you don't want SQL errors being displayed to the public!

another thing... and this is a general bit of advice:

$query= "SELECT * FROM mytable WHERE dept='$myform' ";

Do not get in the habit of writing SQL queries this way. if $myform contains any malicious single-quotes and SQL commands, that's a "SQL injection" vulnerability - a serious security problem. Any string value that is inserted into a SQL query (in quotes) MUST be escaped, and any numeric value (not in quotes) must be validated as being numeric.

your query will look like:

$query= "SELECT * FROM mytable WHERE dept='" . mysql_real_escape_string($myform) . "'";

If you get in the habit of escaping *as* you write queries, you won't have to go back in later and find all the places where SQL injection can occur. When your codebase gets large, finding security holes can be a daunting/massive task. It's better & easier to build impregnable queries while the cement is wet than to go back later and patch the holes.

Do a search for "SQL injection" and you'll find lots of information about preventing it.

Good luck!

[httpwebwitch]

>> you'll also see the array structure of the $_POST

oops, I meant $_GET

btw you could use POST for this instead of get; GET is good when you want a search result to be bookmarkable and if the page acts like a "SELECT"; POST is better when you're posting stuff that will update/insert/delete your data

[numnum]

Thanks very much for your responses, httpwebwitch. I had already used print_r($_GET) and confirmed that there are no problems with my form. Here's the output:

Array ( [myform] => Array ( [0] => A [1] => B [2] => C [3] => D ) )

And earlier I was including or die(mysql_error()) in my query, and there are no syntax errors.

My best guess is that the problem involves nesting a WHILE loop in a FOR EACH loop, as highlighted below:

$a = $_GET['myform'];
$result = mysql_query("SELECT * FROM mytable where dept='$a' ") or die(mysql_error());
foreach($a as $value) {
while ($row=mysql_fetch_array($result)) {
echo $row['name'] , " | " , $row['dept'] , "<br>";
}
}

I don't think nesting loops works, but I just cannot think of the proper way to return only those records for which the value in a particular field ("dept") matches the values that the user has checked in the form. This feature is so basic and commonplace on the Web that I cannot imagine it's tricky to implement. What in the world am I missing here? (Curiously, none of the PHP/MYSQL books or online tutorials I've consulted provide any examples.)

BTW, I'm using GET instead of POST just so I can view the URL string in action while I troubleshoot.

[Dinkar]

Instead of this:

SELECT * FROM mytable where dept='$a'

Try this:

SELECT * FROM mytable where dept='A'

Is it working?

[numnum]

Dinkar,

No, that won't work. A variable must be used in order to represent the entire array of checkbox values: A, B, C and D.

In the meantime, I've made some progress. The following query successfully returns all records matching any single checked value (but not more than one value, and that's the remaining problem):

$a = $_GET['myform'];
$b = join($a);
$query="SELECT * FROM mytable WHERE dept='$b' ";
$result = mysql_query($query) or die(mysql_error());
while ($row=mysql_fetch_array($result)) {
echo $row['name'] , " | " , $row['dept'] , "<br>";
}
}

Look at the bolded portions of the above code. The variable $b represents the entire array myform[]. I removed the foreach loop because it didn't do anything. Now, checking off only the "Group D" box (for example) returns the correct result:

name6 | D
name9 | D

Now I need to figure out how to make this work when more than one checkbox is marked. (As it stands, I get a blank screen upon submitting more than one checked box.) The key is probably the $b = join($a) statement. I just don't know how to revise it so it functions properly for multiple selections.

[Dinkar]

No, that won't work.

Won't? So you didn't tried it.

I'm teaching myself...

It means you are at learning stage. In such case you should try to do things in simple way. Take a break. After break elaborate in simple English, what your script should do with database. Then translate it into MySQL query. It's very easy, you can do it. Just, Keep It Simple Silly :)

[httpwebwitch]

ahh. So by the time $a is gotten from $_GET, it's an array. And you want to select rows where the dept column is in that array.

try this instead.

Use a MySQL "IN" clause in your WHERE.

$in_clause = array();
foreach($a as $v){
// this is just so each element gets wrapped
//in quotes, because it's a string
$in_clause[] = "'" . $v . "'";
}

$query = "SELECT * FROM mytable WHERE dept IN (".implode(',',$in_clause).")";

The result of this should look like:

SELECT * FROM mytable WHERE dept IN ('A','B','C','D')

then execute that query.

FWIW, "in" is a really good SQL technique to know. They're far better optimized than doing single "where" queries in a loop!

[httpwebwitch]

I believe the "IN" technique is what you were imagining with this attempt

$b = join($a);
$query="SELECT * FROM mytable WHERE dept='$b' ";

you were totally on the right track, believing that it should be possible to select matching an array - and yes MySQL does let you do that... the only mistake was using "=".

The column doesn't *equal* an array. But you can match a column value being *in* an array.

Don't be discouraged. A lot of the things you think MySQL should be able to do, it actually does! But there's a long learning curve ahead to discover all the commands and syntax.

[Dinkar]

"IN" is cool. You can also use logical "OR", which is good for beginners :)

SELECT * FROM mytable where dept='A' OR dept='B' OR dept='C'

[numnum]

Thanks, httpwebwitch, for alerting me to the implode function. (I had been trying to use join instead.) As for the query, I had already tried IN for my query and it wasn't working either.

I finally figured this out -- mainly through a lot of trial and error. Recall that I was trying to use a checkbox list of values for one field (in my example the field name is dept) to show records containing only those "dept" values that have been checked off by the user.

Here's what works (as you can see, I was able to reduce it to a single statement, along with the SELECT...WHERE...IN... query):

$a = implode("','",$_GET['myform']);

$query="SELECT * FROM mytable WHERE dept IN ('$a') ";
$result = mysql_query($query) or die(mysql_error());
while ($row=mysql_fetch_array($result)) {
echo $row['name'] , " | " , $row['dept'] , "<br>";
}

Initially, the implode function wasn't working because none of the values in the string were enclosed in single quote marks (as they must be when using IN). So I added those:

The standard form with a comma separator:
$a = implode(",",$field);

Adding single quotes before and after each value in the array (no space between the single and double quotes -- otherwise, it won't work!):
$a = implode("','",$field);

Curiously, it doesn't matter that there's no single-quote mark before the first value or after the last value in the string. And, interestingly, your proposed method of supplying those single quotes wasn't working either, although I'm not sure why not. Believe me, I tried and tried to make that work.

In any event, I hope this post helps others who are trying to solve the same problem.