KJB01
asked on
Multiple loops, checking to see if query is "empty"
I have two sets of checkboxes on a main form. The user selects a number of each set (variables, sites). I want to print a heading for each site that has more than 1 variable. I constrain these with a start and end date.
I have a main loop that goes through each variable passed by $_POST. Within this loop, I have another loop that queries on each site also passed by $_POST, checking to see if there are any values (datavalues) in the table for that particular variable.
I have an IF statement that checks to see if there is a returning row on this query. This works and returns "0" or "1" as the case may be.
What I need to do is now store a list of the SiteName, SiteCode (two attributes of the sites) of any site that has a datavalue for any variable in an array (????), and then when all this looping is completed, echo something like this:
echo "<h3>".$row['SiteName'].": ".$row['SiteCode']."</h3>" ;
I would like to store $query="SELECT DISTINCT `SiteID`, `SiteName`, `SiteCode` FROM `sites`"; in the array.
After that heading I have another couple of loops that actually have the results (a table with variable headings, datavalues in the table, sorted by date.). This works more or less fine, I think.
I'm not sure how clear that explanation is. In short, I need a list (array?) of all sites that have a datavalue for the variables selected on the form, and the site needs to have also been selected on the form, and the datavalue needs to fall in the time constraints.
Thanks!
I have a main loop that goes through each variable passed by $_POST. Within this loop, I have another loop that queries on each site also passed by $_POST, checking to see if there are any values (datavalues) in the table for that particular variable.
I have an IF statement that checks to see if there is a returning row on this query. This works and returns "0" or "1" as the case may be.
What I need to do is now store a list of the SiteName, SiteCode (two attributes of the sites) of any site that has a datavalue for any variable in an array (????), and then when all this looping is completed, echo something like this:
echo "<h3>".$row['SiteName'].":
I would like to store $query="SELECT DISTINCT `SiteID`, `SiteName`, `SiteCode` FROM `sites`"; in the array.
After that heading I have another couple of loops that actually have the results (a table with variable headings, datavalues in the table, sorted by date.). This works more or less fine, I think.
I'm not sure how clear that explanation is. In short, I need a list (array?) of all sites that have a datavalue for the variables selected on the form, and the site needs to have also been selected on the form, and the datavalue needs to fall in the time constraints.
Thanks!
<?php
# time constraints
$smallDate=isset($_REQUEST["date0"])?$_REQUEST["date0"]:"";
$bigDate=isset($_REQUEST["date1"])?$_REQUEST["date1"]:"";
# a certain number of variables are selected using checkboxes on a form. I want to loop through all variables to see if there are any that have a data value. The checkbox values are sent through $_POST.
for($i=1;$i<=$_POST['variables'];$i++) {
if(isset($_POST["VariableID$i"])) {
$variablep=$_POST["VariableID$i"];
#Here I need to loop through all the sites selected on the previous form. This loop wil take place for each variable.
for($s=1;$s<=$_POST['sites'];$s++) {
if(isset($_POST["site$s"])) {
$sitep=$_POST["site$s"];
#This query is supposed to retrieve the name of each site with data within the date range.
$query2="SELECT DISTINCT `sites`.`SiteName`, `sites`.`SiteCode` FROM `sites` INNER JOIN `datavalues` ON `sites`.`SiteID` = `datavalues`.`SiteID` WHERE `datavalues`.`VariableID` = '$variablep' AND `datavalues`.`SiteID` = '$sitep' AND `LocalDateTime` > '$smallDate' AND `LocalDateTime` < '$bigDate'";
$result2=mysql_query($query2) or die(mysql_error());
$num=mysql_numrows($result2);
if($num>0) {
echo $num; #This is checking to see what is returned.
$query="SELECT DISTINCT `SiteID`, `SiteName`, `SiteCode` FROM `sites` WHERE `SiteID`='$sitep'";
$result=mysql_query($query) or die(mysql_error());
while($row=mysql_fetch_array($result)) {
echo "<h3>".$row['SiteName'].": ".$row['SiteCode']."</h3>";
}
}
else { #do nothing
}
}
}
}
}
?>
One other thought... use data visualization techniques.
Add this to the action script:
var_dump($_POST);
You can see exactly what is coming to your script from the HTML form. If you put echo "<pre>"; right in front of var_dump() you will find the output is easier to read.
Best regards, ~Ray
Add this to the action script:
var_dump($_POST);
You can see exactly what is coming to your script from the HTML form. If you put echo "<pre>"; right in front of var_dump() you will find the output is easier to read.
Best regards, ~Ray
ASKER
Thanks Ray!
put echo "<pre>"; before my var_dump($_POST) in as you recommended (great tip) and changed all my counters to start at 0. Nothing noticeably changed, but a good best practise for sure.
I put error_reporting(E_ALL); in my dbconnect.php which I include in each script, so that is taken care of.
I'm not sure where the count($_POST['variables']) would come in... but of course am open to suggestions.
Please note that my checkall javascript function doesn't work as I need it to in this code, but I will be working on that later!
I've also attached a screenshot of the result.
I've pasted in my <form>, which points to result2.php (screenshot and var_dump).
Below is my var_dump($_POST);
array(38) {
["date0"]=>
string(10) "2006-01-01"
["date0_dp"]=>
string(1) "1"
["date0_year_start"]=>
string(4) "1979"
["date0_year_end"]=>
string(4) "2039"
["date0_mon"]=>
string(0) ""
["date0_da1"]=>
string(10) "2006-01-01"
["date0_da2"]=>
string(10) "2009-12-31"
["date0_sna"]=>
string(0) ""
["date0_aut"]=>
string(1) "1"
["date0_frm"]=>
string(10) "searchform"
["date0_tar"]=>
string(0) ""
["date0_day"]=>
string(2) "01"
["date0_month"]=>
string(2) "01"
["date0_year"]=>
string(4) "2006"
["date1"]=>
string(10) "2009-01-01"
["date1_dp"]=>
string(1) "1"
["date1_year_start"]=>
string(4) "1979"
["date1_year_end"]=>
string(4) "2039"
["date1_mon"]=>
string(0) ""
["date1_da1"]=>
string(10) "2006-01-01"
["date1_da2"]=>
string(10) "2009-12-31"
["date1_sna"]=>
string(0) ""
["date1_aut"]=>
string(1) "1"
["date1_frm"]=>
string(10) "searchform"
["date1_tar"]=>
string(0) ""
["date1_day"]=>
string(2) "01"
["date1_month"]=>
string(2) "01"
["date1_year"]=>
string(4) "2009"
["VariableID1"]=>
string(1) "0"
["VariableID19"]=>
string(1) "2"
["VariableID22"]=>
string(1) "3"
["VariableID24"]=>
string(1) "1"
["variables"]=>
string(2) "24"
["site1"]=>
string(1) "1"
["site2"]=>
string(1) "2"
["site3"]=>
string(1) "0"
["sites"]=>
string(1) "4"
["submit"]=>
string(2) "Go"
}
put echo "<pre>"; before my var_dump($_POST) in as you recommended (great tip) and changed all my counters to start at 0. Nothing noticeably changed, but a good best practise for sure.
I put error_reporting(E_ALL); in my dbconnect.php which I include in each script, so that is taken care of.
I'm not sure where the count($_POST['variables'])
Please note that my checkall javascript function doesn't work as I need it to in this code, but I will be working on that later!
I've also attached a screenshot of the result.
I've pasted in my <form>, which points to result2.php (screenshot and var_dump).
Below is my var_dump($_POST);
array(38) {
["date0"]=>
string(10) "2006-01-01"
["date0_dp"]=>
string(1) "1"
["date0_year_start"]=>
string(4) "1979"
["date0_year_end"]=>
string(4) "2039"
["date0_mon"]=>
string(0) ""
["date0_da1"]=>
string(10) "2006-01-01"
["date0_da2"]=>
string(10) "2009-12-31"
["date0_sna"]=>
string(0) ""
["date0_aut"]=>
string(1) "1"
["date0_frm"]=>
string(10) "searchform"
["date0_tar"]=>
string(0) ""
["date0_day"]=>
string(2) "01"
["date0_month"]=>
string(2) "01"
["date0_year"]=>
string(4) "2006"
["date1"]=>
string(10) "2009-01-01"
["date1_dp"]=>
string(1) "1"
["date1_year_start"]=>
string(4) "1979"
["date1_year_end"]=>
string(4) "2039"
["date1_mon"]=>
string(0) ""
["date1_da1"]=>
string(10) "2006-01-01"
["date1_da2"]=>
string(10) "2009-12-31"
["date1_sna"]=>
string(0) ""
["date1_aut"]=>
string(1) "1"
["date1_frm"]=>
string(10) "searchform"
["date1_tar"]=>
string(0) ""
["date1_day"]=>
string(2) "01"
["date1_month"]=>
string(2) "01"
["date1_year"]=>
string(4) "2009"
["VariableID1"]=>
string(1) "0"
["VariableID19"]=>
string(1) "2"
["VariableID22"]=>
string(1) "3"
["VariableID24"]=>
string(1) "1"
["variables"]=>
string(2) "24"
["site1"]=>
string(1) "1"
["site2"]=>
string(1) "2"
["site3"]=>
string(1) "0"
["sites"]=>
string(1) "4"
["submit"]=>
string(2) "Go"
}
<form method="post" action="result2.php" name="searchform">
<h3>Sampling Dates</h3>
<div class="descr">Select a start date and end date for the search.</div>
<?
//get class into the page
require_once('classes/tc_calendar.php');
//instantiate class and set properties
$myCalendar0=new tc_calendar("date0",true);
$myCalendar0->setIcon("images/iconCalendar.gif");
$myCalendar0->setDate(1,1,2006);
$myCalendar0->getDate();
$myCalendar0->dateAllow('2006-01-01','2009-12-31',false);
$myCalendar0->autoSubmit(true,"searchform");
$myCalendar1=new tc_calendar("date1",true);
$myCalendar1->setIcon("images/iconCalendar.gif");
$myCalendar1->setDate(1,1,2009);
$myCalendar1->getDate();
$myCalendar1->dateAllow('2006-01-01','2009-12-31',false);
$myCalendar1->autoSubmit(true,"searchform");
//output the calendar
$myCalendar0->writeScript();
echo " to ";
$myCalendar1->writeScript();
?>
<h3>Sampled Parameters</h3>
<div class="descr">Select paramters to be retrieved.</div>
<?php
$result=mysql_query("select `VariableName`, `VariableID` from `variables` order by `VariableName`, `VariableName` desc");
#$count=mysql_num_rows($result); #defunct... for formatting in three columns
#$split=ceil($count/3); #defunct... for formatting in three columns
if($result) {
print "</tr>\n";
echo "<table cellspacing=\"5\">";
//create table
$i=0;
while($row=mysql_fetch_array($result)) {
$i++;
print "<tr>\n";
//while ($line = mysql_fetch_array($result)) $results[] = $line; #defunct... for formatting in three columns
print "<td><input type=\"checkbox\" name=\"VariableID$i\" value=\"$row[VariableID]\"></td>\n";
echo "<td>{$row['VariableName']}</td>\n";
echo "</tr>\n";
}
//end while
echo "<tr><td><input type=\"checkbox\" name=\"checkall\" onclick=\"checkUncheckAll(this);\"/></td><td><b> Select/unselect all</b></td></tr>";
print "</table>\n";
}
else {
echo("<P>Error performing query: ".mysql_error()."</P>");
}
print "<input type=\"hidden\" name=\"variables\" value=\"$i\"/>\n";
print "<input type=\"submit\" name=\"submit\" value=\"Go\"/>\n";
?>
<h3>Sites</h3>
<div class="descr">Select a single site.</div>
<?php
$result=mysql_query("select `SiteCode`, `SiteID` from `sites` order by `SiteCode`, `SiteCode` desc");
#$count=mysql_num_rows($result); #defunct... for formatting in three columns
#$split=ceil($count/3); #defunct... for formatting in three columns
if($result) {
#print "</tr>\n"; #defunct... for formatting in three columns
$i=0;
while($row=mysql_fetch_array($result)) {
$i++;
#print "<tr>\n"; #defunct... for formatting in three columns
//while ($line = mysql_fetch_array($result)) $results[] = $line; #defunct... for formatting in three columns
echo "<table cellspacing=\"5\">";
print "<tr><td><input type=\"checkbox\" name=\"site$i\" value=\"$row[SiteID]\"></td>\n";
echo "<td>".$row['SiteCode']."</td>";
echo "</tr>";
}
//end while
echo "<tr><td><input type=\"checkbox\" name=\"checkall\" onclick=\"checkUncheckAll(this);\"/></td><td><b> Select/unselect all</b></td></tr>";
print "</table>\n";
}
else {
echo("<P>Error performing query: ".mysql_error()."</P>");
}
print "<input type=\"hidden\" name=\"sites\" value=\"$i\"/>\n";
print "<input type=\"submit\" name=\"submit\" value=\"Go\"/>\n";
echo "</table>";
?>
</form>
result.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This makes your code more simpler and you can handle checked items directly using loop variable, e.g.
$variables[$i] and $sites[$j]
Remember, only checked items are passed via $_POST, no need to validate if it is checked or not. I hope this helps.
$variables[$i] and $sites[$j]
Remember, only checked items are passed via $_POST, no need to validate if it is checked or not. I hope this helps.
@profya: I agree - excellent suggestion about the use of arrays to simplify the iterative process of working through the submitted data.
@KJB01: If you do not already have it, consider getting this book. Excellent as a tutorial and reference, it has been a permanent part of my professional library since Version One.
http://www.sitepoint.com/books/phpmysql4/
Best regards, ~Ray
@KJB01: If you do not already have it, consider getting this book. Excellent as a tutorial and reference, it has been a permanent part of my professional library since Version One.
http://www.sitepoint.com/books/phpmysql4/
Best regards, ~Ray
ASKER
Ahhh, just lost my response!
Anyway, thanks both of you. I have made some good progress. Please see latest code snippet. Now I have good results stored in an array, but It seems these lines:
$result=mysql_query($query 2)or die(mysql_error());
$row[]=mysql_fetch_array($ result);
also store empty values; some sort of null row. Do I need an if-statement to check this?
So the result of
echo "<pre>";
print_r($unique_sites);
is:
Array
(
[0] => Array
(
[0] => Anvik R
[SiteName] => Anvik R
[1] => ananv1a
[SiteCode] => ananv1a
[2] => 1
[SiteID] => 1
)
[1] => #THIS IS THE EMPTY ROW IN THE ARRAY.
[2] => Array
(
[0] => Koyukuk R @ Huslia
[SiteName] => Koyukuk R @ Huslia
[1] => kohsl1a
[SiteCode] => kohsl1a
[2] => 0
[SiteID] => 0
)
)
Thanks so much! Upping the points.
Anyway, thanks both of you. I have made some good progress. Please see latest code snippet. Now I have good results stored in an array, but It seems these lines:
$result=mysql_query($query
$row[]=mysql_fetch_array($
also store empty values; some sort of null row. Do I need an if-statement to check this?
So the result of
echo "<pre>";
print_r($unique_sites);
is:
Array
(
[0] => Array
(
[0] => Anvik R
[SiteName] => Anvik R
[1] => ananv1a
[SiteCode] => ananv1a
[2] => 1
[SiteID] => 1
)
[1] => #THIS IS THE EMPTY ROW IN THE ARRAY.
[2] => Array
(
[0] => Koyukuk R @ Huslia
[SiteName] => Koyukuk R @ Huslia
[1] => kohsl1a
[SiteCode] => kohsl1a
[2] => 0
[SiteID] => 0
)
)
Thanks so much! Upping the points.
<?
$variables=$_POST['VariableID'];
$sites=$_POST['siteID'];
echo "<pre>VariableID";
var_dump($variables);
echo "<pre>SiteID";
var_dump($sites);
for ($i=0; $i<COUNT($variables); $i++)
{
$variablei=$variables[$i];
for ($j=0; $j<COUNT($sites); $j++)
{
$sitej=$sites[$j];
$query2="SELECT DISTINCT `sites`.`SiteName`, `sites`.`SiteCode`, `sites`.`SiteID` FROM `sites` INNER JOIN `datavalues` ON `sites`.`SiteID` = `datavalues`.`SiteID` WHERE `datavalues`.`VariableID` = '$variablei' AND `datavalues`.`SiteID` = '$sitej' AND `LocalDateTime` > '$smallDate' AND `LocalDateTime` < '$bigDate'";
$result=mysql_query($query2)or die(mysql_error());
$row[]=mysql_fetch_array($result);
}
}
echo "<pre>";
#var_dump($row);
$unique_sites = array_unique($row);
echo "<pre>";
print_r($unique_sites);
echo count($unique_sites);
?>
Use $row=mysql_fetch_array($re sult); in stead.
@profya: I think the Asker wants $row to be an array of sub-arrays - with each of the sub-arrays carrying the contents of one row from the SELECT statement. But that is just a guess - I am having trouble following the logic. It seems like maybe the data base is not organized well for this kind of query, but I am at a loss to explain what would work better!
ASKER
profya:
When I do that,
print_r($unique_sites);
echo count($unique_sites);
results in:
Array
(
[0] => Koyukuk R @ Huslia
[1] => kohsl1a
[2] => 0
)
This is only showing the last site to go through the iteration. Well, maybe not. I am not sure why this particular site shows up.
Ray, you are right about having an array of subarrays.
So this database has a table of 'datavalues'. In this table each datavalue has attributes identifying the variable ID, the site ID, and the date of the sample. My user will choose which variables s/he is interested in, and the sites of interest and the date range.
What I am doing here is creating a list of all sites that have data for the chosen variables within the date range. All of the identified sites must also have been checked on the selection form.
What I will do after this is iterate through the array ($unique_sites) and then I will be creating a table (html) to display all the datavalues for the chosen variables at each site. If I have n sites, I will have n tables, each only showing the variables where there is data (this bit works and I haven't shown it to you).
The reason what we are now working on is important, was that if I don't check to make sure there is data at each site for the chosen variables and date range, I will end up with empty tables and table headings, but no data for sites that don't qualify.
I feel like this is really close (but what do I know!).
I should note that I am now not sure what the empty row of the array in my post 05/07/09 12:13 PM, ID: 24780986 is. In theory there should be 4 elements, not three, as in this query I had 4 sites checked off (ideally only 2 would have returned if everything worked).
Many thanks!
When I do that,
print_r($unique_sites);
echo count($unique_sites);
results in:
Array
(
[0] => Koyukuk R @ Huslia
[1] => kohsl1a
[2] => 0
)
This is only showing the last site to go through the iteration. Well, maybe not. I am not sure why this particular site shows up.
Ray, you are right about having an array of subarrays.
So this database has a table of 'datavalues'. In this table each datavalue has attributes identifying the variable ID, the site ID, and the date of the sample. My user will choose which variables s/he is interested in, and the sites of interest and the date range.
What I am doing here is creating a list of all sites that have data for the chosen variables within the date range. All of the identified sites must also have been checked on the selection form.
What I will do after this is iterate through the array ($unique_sites) and then I will be creating a table (html) to display all the datavalues for the chosen variables at each site. If I have n sites, I will have n tables, each only showing the variables where there is data (this bit works and I haven't shown it to you).
The reason what we are now working on is important, was that if I don't check to make sure there is data at each site for the chosen variables and date range, I will end up with empty tables and table headings, but no data for sites that don't qualify.
I feel like this is really close (but what do I know!).
I should note that I am now not sure what the empty row of the array in my post 05/07/09 12:13 PM, ID: 24780986 is. In theory there should be 4 elements, not three, as in this query I had 4 sites checked off (ideally only 2 would have returned if everything worked).
Many thanks!
ASKER
So I have done this (code snippet) to output the results. The result is
Anvik R: ananv1a
:
Koyukuk R @ Huslia: kohsl1a
Note the extra colon where the empty row from the array is. In theory I could have an if-check to remove this. (?)
Anvik R: ananv1a
:
Koyukuk R @ Huslia: kohsl1a
Note the extra colon where the empty row from the array is. In theory I could have an if-check to remove this. (?)
<?
for ($j=0; $j<count($unique_sites); $j++)
{
echo "<h3>" . $unique_sites[$j][0] . ": " . $unique_sites[$j][1] . "</h3>";
}
?>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much to both of you! Ray, sorry I didn't think any of your answers directly lead to the solution, though they were helpful!
profya's last comment was right on. I sneaked a similar if statement into the output portion, but this is much cleaner!
Thanks again!
profya's last comment was right on. I sneaked a similar if statement into the output portion, but this is much cleaner!
Thanks again!
Add this line to the top of your script so you can see whether you are relying on undefined variables:
error_reporting(E_ALL);
This thing: $_POST['variables'] will look in the posted information and pull out the value of that field. From the look of your code, you might want to be using count($_POST['variables'])
Also you need to be aware that checkboxes and radio buttons that are not checked are NOT PRESENT in the $_POST array.
Hope that helps, ~Ray