We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Multiple loops, checking to see if query is "empty"

KJB01
KJB01 asked
on
Medium Priority
584 Views
Last Modified: 2013-12-13
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!
<?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
        }
      }
    }
  }
}
?>

Open in new window

Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
You might want to post the FORM script, so we can see how you have constructed the HTML.  Its interaction with the action script is going to be essential to debugging your code.

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']) and you might want to start your offset at zero instead of one - array elements are numbered starting at zero in PHP.

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
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
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"
}

 <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>

Open in new window

result.jpg
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
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.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
@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

Author

Commented:
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($query2)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.


<?
 
$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);
 
?>

Open in new window

Commented:
Use $row=mysql_fetch_array($result); in stead.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
@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!

Author

Commented:
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!

Author

Commented:
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. (?)

<?    
 
for ($j=0; $j<count($unique_sites); $j++)
	{
	echo "<h3>" . $unique_sites[$j][0] . ": " . $unique_sites[$j][1] . "</h3>";
	}
 
?>

Open in new window

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.