Hello everyone. I have never really ever had a handle on nested loops and would like to optimize this piece of code. What I have works but I am not happy with it. The first query sets up the customer_id and the location_id for the rest of the loops. (Not a good way to handle this)

Here is my code:

<?php
require_once("db.php");
$db = new Database();
$rpthdr = $db->Query("SELECT t1.customer_id, t1.location_id, t1.location_name, t2.name FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) GROUP BY t2.customer_id, t2.location_id");

foreach($rpthdr as $hdr)
{
	$customer_id = $hdr['customer_id'];
	$location_id = $hdr['location_id'];
	echo $hdr['location_name']."<br>";
	echo $hdr['name']."<br>";

	$report1 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report1'");
	foreach($report1 as $rpt1)
	{
		echo "$rpt1[report]"."<br>";
	}

	$report2 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report2'");
	foreach($report2 as $rpt2)
	{
		echo "$rpt2[report]"."<br>";
	}

	$report3 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report3'");
	foreach($report3 as $rpt3)
	{
		echo "$rpt3[report]"."<br>";
	}
}
?>

I have a plain old html page that I wanted to use str_replace on to populate these database fields. I couldn't seem to get that working though. I then decided to try and procedurally write code that would at least give me my data in the format I expect. That is the code above.

Ideally, I would like to have a small foreach and or while loop to handle this and then use str_replace. Failing that, I will have to incorporate the html into this code.

Also, please notice that the sql statements for report1, 2 and 3 are identical with exception of the "report_type in the WHERE clause". It seems to me that there has to be a much better way to do this.

Can someone please help???

Thanks. Tom

Member Avatar for langsor

I can show you how to replace the code in your html file without using string replace ... if you'd like to go with that option.

Otherwise I'm having a difficult time visualizing the data we're actually working with here -- on the other hand, I want you to write my sql if I ever build a real web-app.

If you'd rather use loops over this other (PHP-DOM) method I have in mind, please help me see the data we're trying to process and into what format you would like the end result.

Thanks

Hi langsor. Thank you for the response, help and comment about my sql. :) I'd be happy to help. I have always been more of a database guy than a programmer.

Let me see if I can try and give you some sample data here. And, by the way, I am interested in anything that will allow me to import my data fields as long as it is not Smarty (template system)

This would be a *SINGLE* report for say KMart.

location_name name
KMart Tom

report1
this would be the actual db field having the report type of "1" and all the report data for report1.
report2
this would be the actual db field having the report type of "2" and all the report data for report2.
report3
this would be the actual db field having the report type of "3" and all the report data for report3.

Then we would need to start a brand new template with a new customer from this loop.

Thanks again for the help.

well first off you can do
t3.report_type in ('report1', 'report1', 'report1')

instead of doing a separate query for each.

Member Avatar for langsor

I keep learning SQL as I go ... like everything ... R0bb0b is great with SQL and PHP from what I've seen so far :-)

First off let me give an example of some methods you might be able to use to update values in your html template page ... if it is just plain html.

test.html

<html>
<head>
<script type="text/javascript">
</script>
</head>
<body>
  <h1 id="my_id"></h1>
  <p>Some content</p>
</body>
</html>

examples.php

<?php

$doc = new DOMDocument('1.0');
$doc->loadHTMLFile( 'test.html' );

// or load html as a string ...
//$doc->loadHTML( file_get_contents( 'test.html' ) );

$tags = $doc->getElementsByTagName('*'); // get all tags
//print $tags->length; // count tags

// some basic handling methods ...
foreach ( $tags as $i => $tag ) {

  print $tag->nodeName."<br>";
	
  if ( $tag->getAttribute('id') == 'my_id' ) {
    $tag->nodeValue = 'My Heading';
  }
	
  if ( $tag->nodeName == 'p' ) {
    $tag->setAttribute('class','my_class');
    $tags->item($i)->setAttribute('align','center');
  }
}

// just get first script tag
$script = $doc->getElementsByTagName('script')->item(0);
// a little javascript creation
$script->nodeValue = <<<ENDSCRIPT
alert( 'I was created by PHP Dom methods' );
ENDSCRIPT;

$head = $doc->getElementsByTagName('head')->item(0);
// create a new element node
$style = $doc->createElement('style');
$style->setAttribute('type','text/css');
// attach it to head tag on page
$head->appendChild($style);
// give it some content
$style->nodeValue = <<<ENDSTYLE
body {
  color: indigo;
}
h1 {
  font-size: 1.29em;
}
p {
  font-family: Arial;
  font-style: italic;
}
.my_class {
  color: red;
}
ENDSTYLE;

// print out the results
print $doc->saveHTML();

// or just save the container node you want
//print $doc->saveXML($head);

// Complete list of DOM methods here
//http://www.php.net/manual/en/class.domdocument.php

?>

Now I will take a look and see if I can give you any feedback on the looping question.

Cheers

commented: :$ +2

well first off you can do
t3.report_type in ('report1', 'report1', 'report1')

instead of doing a separate query for each.

that was suppose to be:
t3.report_type in ('report1', 'report2', 'report3')

so basically you can condense all this:

$report1 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report1'");
	foreach($report1 as $rpt1)
	{
		echo "$rpt1[report]"."<br>";
	}

	$report2 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report2'");
	foreach($report2 as $rpt2)
	{
		echo "$rpt2[report]"."<br>";
	}

	$report3 = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = 'report3'");
	foreach($report3 as $rpt3)
	{
		echo "$rpt3[report]"."<br>";
	}

down to this:

$report1 = $db->Query("SELECT t3.report, t3.report_type FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type in('report1', 'report2', 'report3') order by t3.report_type");
	foreach($report1 as $rpt1)
	{
		echo "$rpt1[report]"."<br>";
	}
Member Avatar for langsor

This is what I came up with, but I'm not positive without being able to test it -- to see where it fails ;-)

<?php
require_once("db.php");
$db = new Database();

$report_nums = array( 'report1', 'report2', 'report3' ); // report labels

$rpthdr = $db->Query("SELECT t1.customer_id, t1.location_id, t1.location_name, t2.name FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) GROUP BY t2.customer_id, t2.location_id");

foreach( $rpthdr as $hdr ) {
  $customer_id = $hdr['customer_id'];
  $location_id = $hdr['location_id'];
  echo $hdr['location_name']."<br>";
  echo $hdr['name']."<br>";
	
  foreach ( $report_num as $report ) {

    $results = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = '$report'");

     foreach ( $results as $rpt ) {
       print $rpt['report']."<br>";
    }
  }
}
?>

Let us know how it goes ...

Hey everyone,

Sorry it has taken me so long to reply but aparently, daniweb has some sort of function that locks you out if your emails bounce back. I have not posted to this community for some time and my email address was outdated. I updated my email address but that wasn't enough to let me back in. I registered another email address so I could come back and say thanks for the help. Now let me respond to the posts.

BTW: Cindy is my wife's name. :)

well first off you can do
t3.report_type in ('report1', 'report1', 'report1')

instead of doing a separate query for each.

Rob, thanks for the help but I don't see how that will work. if i need to place all of the report1 notes under a particular heading in my template, how am I going to control that? With the seperated statements I know that I can refer to the one I need and place it exactly where I need it. Remember, there will be more than one note under report1, 2 or 3. An I wrong?

Langsor,

This is one of the frickin coolest things I have seen in a long time. Say goodbye to str_replace and hello DOMDocument :) :)

I actually got a few questions for you about this. If I want to add another 6 fields, would I place them into the foreach loop?

Rob, I am going to try that and see what happens. brb.

Rob, thanks for the help but I don't see how that will work. if i need to place all of the report1 notes under a particular heading in my template, how am I going to control that? With the seperated statements I know that I can refer to the one I need and place it exactly where I need it. Remember, there will be more than one note under report1, 2 or 3. An I wrong?

Right, I caught that and corrected it further down, sorry. My update is not as fast as my cut and paste.

Member Avatar for langsor

Langsor,

This is one of the frickin coolest things I have seen in a long time. Say goodbye to str_replace and hello DOMDocument :) :)

I actually got a few questions for you about this. If I want to add another 6 fields, would I place them into the foreach loop?

You will have to be more specific about what you mean by fields (element nodes not db fields, right?).

Depends on if you want to create the element-nodes from the database results, then you would instantiate the DOMDocument object-instance outside the loop and use the
$new_el = $doc->createElement('tag'); and probably
$element->appendChild( $new_el ); inside the loop after it.

Otherwise, let me know better what you're trying to do.

...

Right, I caught that and corrected it further down, sorry.

Actually, I think you are referring here to your "report1" 3x. I knew that was a typo. :) I am actually talking about being able to "control" the output of the query.

I tried your sql just now and I does work. It in fact pulls the data from the report field. My issue is that the report template is is this form (sub-headers are hard coded into the html)

Company Name

Morning Report
{report1}
Afternoon Report
{report2}
Evening Report
{report3}

The database will populate everything between {}

The problem I see with the sql you last posted is that it will *not* populate in the correct order. That is the problem I face now.

You guys are terriffic. Thank you for the help! :)

You will have to be more specific about what you mean by fields (element nodes not db fields, right?).

Depends on if you want to create the element-nodes from the database results, then you would instantiate the DOMDocument object-instance outside the loop and use the
$new_el = $doc->createElement('tag'); and probably
$element->appendChild( $new_el ); inside the loop after it.

Otherwise, let me know better what you're trying to do.

...

Hey Langsor, thanks again for the help. I have to tell you. I have never seen this DOMDocument before. Is this javascript? I see that in the php script you are instantiating the DOM object. Where is the require_once() statement? Where is the object at?

By fields, I mean database fields. The only things I would want to loop are these reports that rob is helping with. the customer name, address and city would need to be outside the foreach loop

Hey Langsor, thanks again for the help. I have to tell you. I have never seen this DOMDocument before. Is this javascript? I see that in the php script you are instantiating the DOM object. Where is the require_once() statement? Where is the object at?

By fields, I mean database fields. The only things I would want to loop are these reports that rob is helping with. the customer name, address and city would need to be outside the foreach loop

No, its actually a php class. He showed me that the other day, pretty wild huh? I don't know if it is prebuilt into php though.

Rob, I almost fell out of my chair when it worked. I don't know what it is, but I love it.

Both of you guys are great for helping me like this. Thank you both!

Member Avatar for langsor

the DOMDocument PHP class was introduced in PHP 5 as one of the standard features ... so no importing neccessary (require, include).

There is very similar JavaScript DOM support built into the later JS versions and supported cross-browser too.

So I'm still confused about the fields then -- sorry, I can be obtuse at times :-)

...

So I'm still confused about the fields then -- sorry, I can be obtuse at times :-)

...

No Langsor, it's me. I am not good at explaining things. Let me try again.

You are placing id=my_"tag" inside of the html. I see that the DOMDocument is replacing anything inside that tag with whatever is inside the foreach loop. This is cool for the first tag but I have 6 total that need to be populated inside this template.

So, we would have:

location_name,
city,
fax,
-------everything above is a database field and should not loop.
then we have:
report1,
report2, and finally
report3
--------these reports are also database fields and should be inported into the template. These also need to loop because there can be more than 1 report.

How would I go about adding these reports into the DOMDocument php script?

Member Avatar for langsor

Ahhh, I think I'm on the same page as you now.

The answer partly depends on the structure of the html you want to place your report data into and how many html elements for each of the reports.

Pretending each report is one or more<p> tags and knowing the report headers are hard-coded into the templates. If you can also hard-code the (say) <div> tags under the headers, then you're golden.

<h2>Morning Report</h2>
<div id="r1">{report1 => one or more <p> tags}</div>

<h2>Afternoon Report</h2>
<div id="r2">{report2 => one or more <p> tags}</div>

<h2>Evening Report</h2>
<div id="r3">{report3 => one or more <p> tags}</div>
// outside the loop get the <div> ids
$divs= $doc->getElementsByTagName('div');
foreach ( $divs as  $div ) {
  switch ( $div->getAttribute('id') ) {
    case 'r1': $r1 = $div;
    case 'r2': $r2 = $div;
    caes 'r3': $r3 = $div;
}

// Inside the loop, something like this -- from R0bb0b's last example
foreach($report1 as $i => $rpt1)
{
  $p = $doc->createElement('p');
  $p->nodeValue = $rpt1[report];
  // here I don't actually understand what R0bb0b did so don't know what to test for ???
  // but determine what report we're working on and then append to that <div>
  switch ( TRUE ) {
    case $test1 == $match: $r1->appendChild($p); // pseudo-code
    case $test2 == $match: $r2->appendChild($p); // pseudo-code
   // ... etc ...
  }
}

As you can see I'm a little lost what you and R0bb0b are doing with the queries here, so don't know how to tell which report you're working on.

Hope this helps give a general idea though

...

Ok. let me just clarify. Are you saying that this will automatically loop in my html template without me having to code a foreach loop into it (html)?
<div id="r1">{report1 => one or more <p> tags}</div>
BTW: You are correct that the headers are hard coded into the html. It would be senseless to have the database pull afternoon, morning and evening in that order. That is why I had 3 different sql statements.

I have an idea to use Rob's sql solution.

Why couldn't I just simply code a if conditional to order the reports like so:

if($result['report_type'] == "report1")
{
    echo ....
}
elseif($result['report_type'] == "report2")
{
    echo ....
}
elseif($result['report_type'] == "report3")
{
    echo ....
}

Isn't this a viable solution so that I wouldn't have to code 3 different report sql statements?

Member Avatar for langsor

Ok. let me just clarify. Are you saying that this will automatically loop in my html template without me having to code a foreach loop into it (html)?
<div id="r1">{report1 => one or more <p> tags}</div>
BTW: You are correct that the headers are hard coded into the html. It would be senseless to have the database pull afternoon, morning and evening in that order. That is why I had 3 different sql statements.

No, these are just placeholders where the <p>report</p> data will go -- {report1 => one or more <p> tags} -- the do nothing code-wise.

For each element you want to fill with report data you need to do a
$p = $doc->createElement('p');
$p->nodeValue = $form_data;

And then append that to the html document before iterating the next cycle of your loop
$r1->appendChild($p);

I just don't know in the loop that you have how to test what <div> id to append to or else I would be more specific ... maybe you can tell from some value derived from your DB query ... or count the foreach ( $nested as $idx => $value ) $idx value and compare against that ... that's where I'm not clear since I can't see the actual output of the query results.

But willing to keep trying :-)

This would be the structure that I would use for that inner loop.

<?
$reporttypes = array("report1", "report2", "report3");
$reportswhere = "'" . implode("', '", $reporttypes) . "'";
$reportsarray = array();
$report1 = $db->Query("SELECT t3.report, t3.report_type 
						FROM location AS t1 
						Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id 
						Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id 
						WHERE 
						t3.stamp >= date_sub(now(), interval 1 day) 
						AND t1.customer_id = '$customer_id' 
						AND t1.location_id = '$location_id' 
						AND t3.report_type in(" . $reportswhere . ") 
						order by t3.report_type");
foreach($report1 as $rpt1)
{
	$reportsarray['report_type'][] = $rpt1['report'];
}

foreach($reporttypes as $reporttype)
{
?>
	<h2><? echo $reporttype; ?></h2>
	<?
	foreach($reportsarray[$reporttype] as $loopdata)
	{
		echo $loopdata . "<br />";
	}	
}


?>

No, these are just placeholders where the <p>report</p> data will go -- {report1 => one or more <p> tags} -- the do nothing code-wise.

For each element you want to fill with report data you need to do a
$p = $doc->createElement('p');
$p->nodeValue = $form_data;

And then append that to the html document before iterating the next cycle of your loop
$r1->appendChild($p);

Ok, I have looked back at the DOMDocument. Am I correct in that these elements you are referring to above need to be added in the DOMDocument? I was under the impression that these went into the html. Sorry, I am still trying to figure out how all of this ties together.

But willing to keep trying :-)

Thanks Langsor. :)

Member Avatar for langsor

I have an idea to use Rob's sql solution.

Why couldn't I just simply code a if conditional to order the reports like so:

...

Isn't this a viable solution so that I wouldn't have to code 3 different report sql statements?

Rob will have to answer that one.

If my example sql code worked then I think I know how to do the DOM part ...
Html part

<h2>Morning Report</h2>
<div id="r1"></div>
<h2>Afternoon Report</h2>
<div id="r2"></div>
<h2>Evening Report</h2>
<div id="r3"></div>

Get div id's part -- outside the loop

$divs= $doc->getElementsByTagName('div');
foreach ( $divs as  $div ) {
  switch ( $div->getAttribute('id') ) {
    case 'r1': $r1 = $div;
    case 'r2': $r2 = $div;
    caes 'r3': $r3 = $div;
  }
}

From my earlier sql block code

// preceding code ...

$report_nums = array( 'report1', 'report2', 'report3' );

foreach( $rpthdr as $hdr ) {
  $customer_id = $hdr['customer_id'];
  $location_id = $hdr['location_id'];
  echo $hdr['location_name']."<br>";
  echo $hdr['name']."<br>";
	
  foreach ( $report_num as $report ) {

    $results = $db->Query("SELECT t3.report FROM location AS t1 Inner Join email AS t2 ON t1.customer_id = t2.customer_id AND t1.location_id = t2.location_id Inner Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '$customer_id' AND t1.location_id = '$location_id' AND t3.report_type = '$report'");

     foreach ( $results as $rpt ) {
       $p = $doc->createElement('p');
       $p->nodeValue = $rpt['report'];
       switch ( TRUE ) {
         case $report == 'report1': $r1->appendChild($p); break;
         case $report == 'report2': $r2->appendChild($p); break;
         case $report == 'report2': $r3->appendChild($p); break;
      }
    }
  }
}

// following code ...

So if you can translate what I tried to do with what you are doing, you should be able to make this work -- famous last words.

I have an idea to use Rob's sql solution.

Why couldn't I just simply code a if conditional to order the reports like so:

if($result['report_type'] == "report1")
{
    echo ....
}
elseif($result['report_type'] == "report2")
{
    echo ....
}
elseif($result['report_type'] == "report3")
{
    echo ....
}

Isn't this a viable solution so that I wouldn't have to code 3 different report sql statements?

You could do the "ifs" if you want(the one query will work) or you could try my multi dimensional array solution posted above(also, one query will work).

Member Avatar for langsor

Ok, I have looked back at the DOMDocument. Am I correct in that these elements you are referring to above need to be added in the DOMDocument? I was under the impression that these went into the html. Sorry, I am still trying to figure out how all of this ties together.

No problem.

How the DOM work in PHP is you load the html or xml string or file into a $dom-object (instantiated by the class)

You make any transformations or changes to that html through the DOM methods

Then you save that back out as a string or a file

So here we're loading your entire template-html file into the main php page's dom-object, and after all is said and done, printing that out to the browser window or saving it as an (probably in your case) temp-html file ... haven't really gone into those options much yet though.

Let me know if this does not make sense and I will try to paraphrase it some ... ?

This would be the structure that I would use for that inner loop.

Thanks Rob!

This is what I get from that:

Undefined index:  report1 
Undefined index:  report2 
Undefined index:  report3 
Invalid argument supplied for foreach()

Let me have a look at it and see what I can come up with. I do have a question about this line. I have never done this and because I am not a wazoo coder, I am probably not correct but I figured it wouldn't hurt to ask.

foreach($reportsarray[$reporttype] as $loopdata)

Is $reporttype supposed to be without a variable sign in front of it?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.