Solved

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

Posted on 2009-07-04
13
560 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

0
Comment
Question by:KJB01
  • 5
  • 4
  • 4
13 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24778066
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
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24778068
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
0
 

Author Comment

by:KJB01
ID: 24778841
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
0
 
LVL 14

Accepted Solution

by:
profya earned 400 total points
ID: 24779145
You can use checkbox arrays for both variables and sites, for example <input type="checkbox" name="variable[]" value=$var_id>
<input type="checkbox" name="site[]" value=$site_id>
When you post the form, php provides you with two array variables, $_POST['valiable'] and $_POST['site'] containing only checked items, for example you can loop on checked variables something like:
$variables=$_POST['valiable'];
$sites=$_POST['site'];
for ($i=0; $i<COUNT($variables); $i++)
{
    ........
    ........
    for ($j=0; $j<COUNT($sites); $j++)
    {
          .........
          .........
    }
}
0
 
LVL 14

Expert Comment

by:profya
ID: 24779150
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.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24779931
@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
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:KJB01
ID: 24780986
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

0
 
LVL 14

Expert Comment

by:profya
ID: 24781022
Use $row=mysql_fetch_array($result); in stead.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24781034
@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!
0
 

Author Comment

by:KJB01
ID: 24781140
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!
0
 

Author Comment

by:KJB01
ID: 24781192
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

0
 
LVL 14

Assisted Solution

by:profya
profya earned 400 total points
ID: 24783181
Thanks Ray, may be I lost the focus a little bit.
KJB01:
Why you do not check if the query returns results or nor?
It seems to me this is the condition you are looking for.
Try this:

if ($result && mysql_num_rows($result)>0)

      $row[]=mysql_fetch_array($result);

Open in new window

0
 

Author Closing Comment

by:KJB01
ID: 31600050
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!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now