Checkboxes to make sql query's

[CoreyM]

I've been reading treads and I haven't found anything along the lines that I'm trying.

I have a database of hockey players, I can build a query via html form to search by first and last name or a combination but I want to be able to narrow down the search. Say by position, so along with the first and last name I made checkboxes for the various positions.

When I pass to the query the first 3 kinda work but I can't get the last 2 to go at all.

<table width="60%" border="0" align="center" cellpadding="3" cellspacing="0">
<tr>
<td align='right'><strong><font face="Arial, Helvetica, sans-serif">Centre:</font></strong></td>
<td><input name="checkBox[0]" type="Hidden" id="C" value="">
<input name="checkBox[0]" type="checkbox" id="C" value="C">
</td>
<td align='right'><strong><font face="Arial, Helvetica, sans-serif">Left Wing:</font></strong></td>
<td><input name="checkBox[1]" type="Hidden" id="LW" value="">
<input name="checkBox[1]" type="checkbox" id="LW" value="LW">
</td>
<td align='right'><strong><font face="Arial, Helvetica, sans-serif">Right Wing:</font></strong></td>
<td><input name="checkBox[2]" type="Hidden" id="RW" value="">
<input name="checkBox[2]" type="checkbox" id="RW" value="RW">
</td>
<td align='right'><strong><font face="Arial, Helvetica, sans-serif">Defense:</font></strong></td>
<td><input name="checkBox[3]" type="Hidden" id="D" value="">
<input name="checkBox[3]" type="checkbox" id="D" value="D">
</td>
<td align='right'><strong><font face="Arial, Helvetica, sans-serif">Goalie:</font></strong></td>
<td><input name="checkBox[4]" type="Hidden" id="G" value="">
<input name="checkBox[4]" type="checkbox" id="G" value="G">
</td>
</tr>
</table>

I used the hidden box method, I don't know any other. All of these seem to be exactly the same but when I send the info to my php script again I say the first three work, C, LW and RW but the D and G just won't go.

This is my query...
I add the query works find from command line.

$query = "SELECT * FROM Players_Year_6 WHERE First like '${first_name}%' and Last like '${last_name}%' and (PO = '$checkBox[0]' or PO = '$checkBox[1]' or PO = '$checkBox[2]' or PO = 'checkBox[3]' or PO = 'checkBox[4]')";

Any info would be great.

Corey

[floR]

You are using the same name for more than 1 form field, which is a very Bad Idea. This could be causing the unexpected behaviour.

What I would do is lose the hidden fields and check server-side which fields are left unchecked and take the necessary steps.

[CoreyM]

It's an array, should it matter how many I use?

[floR]

Well okay, so not the name that matters, but the reserved space in the namespace. What I'm trying to say is that you may use 'checkBox' as the array name, but you shouldn't assign 2 different values to, for example, checkBox[2], which you are doing.

You can't just expect PHP or even MSIE or HTTP to correctly handle your double fields checkBox[2] and checkBox[2]. I wouldn't rely on any of those technologies to give me the field that actually has a value, albeit the checkbox or the hidden.

Using...
<form>
<input type="hidden" name="checkBox[2]" value="a">
<input type="checkbox" name="checkBox[2]" value="b">
</form>
... is just asking for trouble.

Hmm, am I making myself clear?

[CoreyM]

Fixed my problem and I'll let people know how....

You CAN have the same name repeated as many times as you want.

Within the HTML;

<input name="checkBox[]" type="checkbox" value="C">
<input name="checkBox[]" type="checkbox" value="LW>
<input name="checkBox[]" type="checkbox" value="RW>
<input name="checkBox[]" type="checkbox" value="D>
<input name="checkBox[]" type="checkbox" value="G">

The trick is passing that information to an array;

$position = $_POST['checkBox'];

And then you call each key;

$position[0]; //this would give you "C"
$position[3]; //this would give you "RW"

etc...

No need for hidden fields...

Thanks to Nate187 on irc for the info...

[CoreyM]

ps;

floR, Am I making myself clear?

[CoreyM]

Actuall the whole proccess of;

$position = $_POST['checkBox'];

isn't needed....

The;

<input name="checkBox[]" type="checkbox" value="C">

"checkBox[]" Seems to create the array.

You still call them the same though;

$checkBox[0]; //would give you "C" etc...

Hope this helps someone...

Later...

[floR]

Yes, you're making yourself clear, but perhaps I wasn't. If you declare the fields as checkBox[], you're telling PHP that you want an array and it figures out how to fill it. PHP will make new array elements itself. But when you're telling the page explicitly that a field is called checkBox[0], a next declaration of checkBox[0] will (probably) override the first.

So you can have:

<input name="checkBox[]" value="a">
<input name="checkBox[]" value="b">
<input name="checkBox[]" value="c">

which will result in a 3-element array.
But not:

<input name="checkBox[0]" value="a">
<input name="checkBox[0]" value="b">
<input name="checkBox[0]" value="c">

which will result in a 1-element array with uncertain value (probably 'c')

[thompsonsco]

I am having a similar problem as you did. My issue is in building the SQL statement. I am passing the array fine from my checkboxes on the input page to my results page and have printed them using the foreach() statement to verify.

Users select checkboxes representing data in a table. I want to find the data and return the row of info.

Do you know how to build the SQL query?

Here is my inputs:

Wind Speed 
                  <input type="checkbox" name="SensorList[]" value="WindSpd">
                  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Wind Gust 
                  <input type="checkbox" name="SensorList[]" value="WindGust">
                  &nbsp;&nbsp;&nbsp;&nbsp; Wind Direction 
                  <input type="checkbox" name="SensorList[]" value="WindDir">
                  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Air Temperature 
                  <input type="checkbox" name="SensorList[]" value="AirTemp">
                </p>
                <p>Visibility 
                  <input type="checkbox" name="SensorList[]" value="Visibility">
                  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Air Pressure 
                  <input type="checkbox" name="SensorList[]" value="AirPress">
                  &nbsp;&nbsp;&nbsp;&nbsp; Box Temperature 
                  <input type="checkbox" name="SensorList[]" value="BoxTemp">
                  &nbsp;&nbsp;&nbsp;&nbsp; Precipitation 1 
                  <input type="checkbox" name="SensorList[]" value="Precip1">
                </p>
                <p>Rainfall 
                  <input type="checkbox" name="SensorList[]" value="Rainfall">
                  &nbsp;&nbsp; Precipitation 2 
                  <input type="checkbox" name="SensorList[]" value="Precip2">
                  &nbsp;&nbsp; Relative Humidity 
                  <input type="checkbox" name="SensorList[]" value="RelHumd">
                  &nbsp;&nbsp; Housing Temperature 
                  <input type="checkbox" name="SensorList[]" value="HsngTmp">
                </p>
                <p>
                  <input type="submit" name="Submit" value="Submit">
                </p>
              </form>

Here is my post:

$sensors = $_POST['SensorList']

I know the SELECT...FROM... but I am not sure about creating a WHERE statement that will be flexable, dependent on the variable.

Any ideas?

[CoreyM]

You don't actually have to pass the array to $sensors, you already have the array $SensorList[]. When building an SQL statement your possibilities are almost endless. You might want to read some info on this subject. I'll show you what I did with mine and it might get you on the right track.

I have a database of Hockey players that I want to search through. I can search by first name and last name but I wanted to search also for just specific positions or a combination of first/last name and position, anyway. I have a text field for first and last name and checkboxes for the various positions. When you pass a SQL statement you can break it down like to you a math equation with () etc...

The select statement I built was;

$query = "SELECT * FROM Players_Year_6 WHERE (First like '${first_name}%' and Last like '${last_name}%') and (PO = '$checkBox[0]' or
PO = '$checkBox[1]' or PO = '$checkBox[2]' or PO = '$checkBox[3]' or PO = '$checkBox[4]')";

So with my query you can see it first looks at the first name and last name, and the goes on to do a breakdown by position.

My form was posted in an earlier post.

I'm not sure how much this will help you but SQL queries options are quite extensive.

[thompsonsco]

Thanks for the input on that. I think I may have to write something a little more complex as I will have multiple checkboxes so an OR statement will not work for me and the AND may not be right either for this application because of the need for flexability. I will search for material on SQL structure. Thanks again for helping.

[OctoberAsh]

I was faced with much the same problem - lots of inputs to query a MySQL db from a single HTML form, and the user could search on any/all of them. One of the nice things I've learned (from folks' postings here) was how to breakdown and rebuild variables to achieve what you want.

I solved my problem this way. I was using text fields and two radio button arrays to build my query, and, after dealing with the same thing you came across, I figured out the following schema (forgive the newb coding...you should've seen it before I cleaned it up...):

Variables/Inputs
FileType (radio button choice of four archive types)
CaseNumber (file number)
CliCompany (radio button choice of seven company names)
CliDOB
CliLName
CliFName
BatchDate //date batch was archived

Code:

<!--
Move through variables from HTML form and set query strings checking for NULL/blank responses
-->

if ($Array["CaseNumber"]=='') {
	$qryCaseNumber = '';
} else {
$qryCaseNumber = "AND CaseNumber = " . "'" . $Array["CaseNumber"] . "' ";
}

if (is_null($Array["CliCompany"])) {
	$qryCliCo = '';
} else {
$qryCliCo = "AND CliCompany = " . "'" . $Array["CliCompany"] . "' ";
}

if ($Array["CliDOB"]=='') {
	$qryCliDOB = '';
} else {
$qryCliDOB = "AND CliDOB = " . "'" . $Array["CliDOB"] . "' ";
}

if (is_null($Array["CliLName"])) {
	$qryCliLName = '';
} else {
$qryCliLName = "AND CliLName = " . "'" . $Array["CliLName"] . "' ";
}

if ($Array["BatchDate"]=='') {
	$qryBatchDate = '';
} else {
$qryBatchDate = "AND BatchDate = " . "'" . $Array["BatchDate"] . "' ";
}

mysql_connect("hostname","user","password") or
				die(mysql_error());
mysql_select_db("databasename") or
				die(mysql_error());

<!--These two parts were static-->
$qryHead = "SELECT * FROM tblSCANRECORDS";
$qryWhere = "WHERE FileType = " . "'" . $Array["FileType"] . "' ";

<!--This puts the entire query together from all elements, returning NULLs if that part of the query isn't used in the WHERE statement (make sure the spacing is good between CONCATs-->
$query = $qryHead . ' ' . $qryWhere . ' ' . $qryCaseNumber . ' ' . $qryCliCo . ' ' . $qryCliDOB . ' ' . $qryCliLName . ' ' . $qryBatchDate;  

$result = safe_query($query);

The results were then displayed in a table. I don't know if it's very efficient PHP coding, but it does work. As time goes on in the app I'm building, I'll probably have to rethink how to do this with less complicated coding.

Good luck!