Making a MySQL query with a checkbox array

thompsonsco

Hello,

I have passed an array of checkbox values to a results page and verified they exist by echoing them. I would like to use the values from the checkboxes to form a query in MySQL. I can make queries, but I have not figured out how to use variables in an array.

The checkboxes represent data input in a table. I was looking to return the instances of the checked items and all the data in the row. I know it should be a WHERE statement.

Here is the checkbox array:

$sensors = $_POST['SensorList'];

Would I incorporate this into the SQL Statement:

foreach ($sensors as $loop ){
       echo $loop.", ";
	   }

I do not think I would use "ECHO", but I need a push in the right direction.

Any help would be great.

goa103

hi,

foreach ($sensors as $loop ){

$loop doesn't mean anything name it $sensor, better to name it properly, don't you think ?

foreach ($sensors as $sensor) or $s :p

first you have to init a var that will contain your Where definition. then use the foreach loop to add statement to it. I don't know what you really want to do so... Let's say that and id of your table must be equal to one of these sensors. we will use the OR operator.

$WhereDef = "";

foreach ($sensors as $s)
{
$sWhereDef .= "idSensor=$s OR ";
}

big problem using a foreach, we always have a useless OR in the end. so we can only use a simple for statement:

$WhereDef = "";
$NbSensor = count ($sensors);

for ($i = 0; $i < $NbSensor; $i ++)
{
$sWhereDef .= "idSensor=" . $sensors [$i];

if ($i < ($NbSensor - 1)
{
$sWhereDef .= " OR ";
}
}

if it's not last sensors then we add a OR string to it. if you click checkbox 1 and 3 of 3 then:
WhereDef = "idSensor=1 OR idSensor=3". see ?

thompsonsco

Wow, thanks for the input. I am going to jump into it now and try to work your ideas into my code.

One thing I would like to ask. You said you did not know what I was trying to do so you assumed the sensors were an ID of my table. Will this method work if they are not IDs. The sensors are just data in a column

IDMain Site# Date Time Value SensorName
2 1 3/13/2001 11:52:00 PM 7.77 "WindSpd
"
3 1 3/13/2001 11:52:00 PM 9.71 "WindSpd
"
4 1 3/13/2001 11:52:00 PM 13.02 "WindSpd
"
5 1 3/13/2001 11:52:00 PM 8.55 "WindSpd
"
6 1 3/13/2001 11:52:00 PM 61.23 "WindSpd
"
7 1 3/13/2001 11:52:00 PM 147.92 "WindSpd
"
8 1 3/13/2001 11:52:00 PM 149.09 "WindSpd
"
9 1 3/13/2001 11:52:00 PM 149.09 "WindSpd

Where you see windspd there are 12 other sensors appearing randomly in the column. I hope to have a way of finding the occurences of the sensors that the user selects through the checkboxes and then displaying just those sensors.

I will continue to work on the path you have given me, but please let me know if this new information changes your recommendations. Also I am going to use AND instead of OR as I want all of the checkbox values used. I do not see any problems with using AND, do you?

Thanks again for all your help. I will return the favor to others when I am up to speed with PHP.

goa103

hi,

no id ?
a table always have at least one id. use that id to identify a row so you can handle it properly. moreover I think you should really split up your information: date, force, name... things like that.

atomic_table: id (INT) PRIMARY KEY

thompsonsco

Hi,

Sorry I did not make a better display of the table. When I pasted the table it was condensed, but the first column is an ID field. Also, the data is separated into individual columns.

IDMain    Site#     Date             Time      Value        SensorName 
2           1       3/13/2001   11:52:00 PM     7.77        WindSpd
3           1       3/13/2001   11:52:00 PM     9.71        WindSpd
4           1       3/13/2001   11:52:00 PM    13.02        WindSpd
5           1       3/13/2001   11:52:00 PM     8.55        WindSpd

Sorry for the confusion. I am still working on it, I will send a reply and let you know how it went. Please let me know if you have any more input on this as I would love to read it. Thanks.

OctoberAsh

Are you dealing with large datasets? If you are, you may want to consider dropping OR altogether - SQL has to chug through a lot more cycles to get the result if you have more than one OR.

Try using IN('x','y','z',..'i') instead. You could use the same approach the previous poster mentioned, but instead use the FOR..EACH to fill the IN() statement with the data you read in. For large datasets, the difference in response times can be dramatic.

Good luck!

thompsonsco

Thanks Jack,

I tried to find info on the IN() statement, but all I found was how to use it to see if a value was in a set group of values

a IN(a,b,c,d)

Returns true if "a" equals any value in the list or if value is NULL it returns unknown.

I may have multiple values because the client can choose any number of 12 checkboxes.

I could not find an example of using the IN() in a foreach loop so I guessed at this and failed.

<?php foreach ($Sensors as $Sensor ){
       $Sensor=IN();
	   }?>
$query_DataRequest = "SELECT * FROM main WHERE 
(main.`Date` BETWEEN '$StartDate' AND '$EndDate') AND (
 main.`SensorName`='$Sensor') ORDER BY main.`Date`, main.`Time` ";

Then I also tried

foreach ($Sensors as $Sensor ){
       $IDSensor.="$Sensor=IN()";
	   } 
$query_DataRequest = "SELECT * FROM main WHERE
 (main.`Date` BETWEEN '$StartDate' AND '$EndDate') AND 
(main.`SensorName`='$IDSensor') ORDER BY main.`Date`,
 main.`Time` ";

And

foreach ($Sensors as $Sensor ){
       $TheWhere.="$IDSensor=IN(Sensor)";
	   } 

$query_DataRequest = "SELECT * FROM main WHERE 
(main.`Date` BETWEEN '$StartDate' AND '$EndDate') AND 
(main.`SensorName`='$TheWhere') ORDER BY main.`Date`,
 main.`Time` ";

Can you give me a hint as to how to structure the IN() suggestion you made? Thanks.

OctoberAsh

No problem, Scott. I should have been more clear that IN() is a SQL function, not PHP. What it's useful for is in a WHERE statement to test if what you're SELECTing has a value you're looking for (as you mentioned in the True/False explanation in your post), like this:

SELECT ClientID
FROM tblClient
WHERE ClientFirstName IN('Damien',Tami','Pedro','Sleater')

This example is pure pseudocode, so bear with. I hope it can help:

(basic SELECT query)
$queryhead = "SELECT * FROM tblTableName";

(WHERE clause starting out the IN comparison)
$queryWhere = "WHERE dataelementtocompare IN("

(Close the IN array)
$queryCloseQuote = ")";

(this bit snipped from Goa's example with alterations)
//cycle through array
for ($i = 0; $i < $NbSensor; $i ++)
{
if($i = 0) //for the first element in the array, wrap the dataelement in single quotes, no comma
{
$insertFirstDataPoint .= "'\n" . $sensors [$i] . "'\n"; -- should yield 'dataelement'
}
else
{
$insertSubsequentDataPoints .= ",'\n" . $sensors [$i] . "'\n"; -- should yield ,'dataelement'

$query = $queryHead . $queryWhere . $insertFirstDataPoint . $insertSubsequentDataPoints . $queryCloseQuote

What this should do (when written properly) will construct the following example:
$query = "SELECT * FROM tblTableName WHERE dataelementtocompare IN('data_1','data_2','data_3','data_4'...'data_i')"

The loop part will take some work to make sure it captures all data elements, but I think this would work.

thompsonsco

Thanks Jack for all the help you have been giving me on this problem.

I have been reading what you wrote and I love the theory behind the code, it is quite ingenious.

As far as my project, I have a glitch. The way I read it, dataelementtocompare is one thing comparing to the array of checkboxes that the for loop created in the IN ( )statement parenthese.

I think I need the equivelent of WHERE main.SensorName = the checkboxes checked

"SensorName" is a column in the "main" table that contains the Values equal to the checkbox values.

So a checkbox would have a value that I would search for in the SensorName column of the main table and I would not know how many checkboxes are going to be checked so I need the variable.

Writing this has given me an idea on how I may construct a statement using your technique. I will get back to you if I solve it, but please reply if you have more ideas.

Thanks again.

thompsonsco

I found out that I can use the IN() statement for my application and here is the final solution.

Thanks to Jack (OctoberAsh) and goa103 from PHP Builder forum for their help.

To restate, Users select a date range through drop down lists for start month, day, year and end month, day and year.

Then they select the checkboxes that contain the values for the data they would like to see from the database.

The code builds a query based on the date range and checked boxes selected.

<?php>
$StartDate = $_POST['YearStart'] . "-" . $_POST['MonthStart'] . "-" . $_POST['DayStart']; // Formats to MySQL date format
$EndDate = $_POST['YearEnd'] . "-" . $_POST['MonthEnd'] . "-" . $_POST['DayEnd']; // Formats to MySQL date format
$Sensors = $_POST['SensorList'];
$QuerySelect = "SELECT * FROM main ";
$QueryWhere = "WHERE (main.`Date` BETWEEN '$StartDate' AND '$EndDate') AND main.`SensorName` IN(";
$QueryClose = ")";
$QueryOrderBy = " ORDER BY main.`Date`, main.`Time`"; 
$PickedSensors = count($Sensors);
$SubsequentData = ""


for ($i = 0; $i < $PickedSensors; $i++) // Builds the checkboxes inbetween the parentheses in the IN() statement
{
	if ($i==0)
	  {
	   $FirstData = "'" . $Sensors[$i] . "'";
	  }

	else
	  {
	    $SubsequentData = $SubsequentData . ",'" . $Sensors[$i] . "'";
	  }

}
mysql_select_db($database_weather, $weather);
$query_DataRequest = $QuerySelect . $QueryWhere . $FirstData . $SubsequentData . $QueryClose . $QueryOrderBy . ";";
$DataRequest = mysql_query($query_DataRequest, $weather) or die(mysql_error());
$row_DataRequest = mysql_fetch_assoc($DataRequest);
$totalRows_DataRequest = mysql_num_rows($DataRequest);
?>

Here is the resulting Query statement for three checked boxes for WindSpd, WindGust, and WindDir.

SELECT * FROM main WHERE (main.Date BETWEEN '2001-03-13' AND '2001-03-14')
AND main.SensorName IN('WindSpd','WindGust','WindDir') ORDER BY main.Date, main.Time;

Thanks again.

OctoberAsh

Glad to see the solution worked out for you, Scott. I found that building SQL queries with complex inputs works well with the "divide and conquer" approach. Deconstructing the query, inserting chosen values into the parts, then re-assembling the parts has simplified many of the PHP scripts I've had to write.

Good luck with your project, and happy to have been some help!