Optimizing a lot of foreach loops. Please help.

[tomjohnson]

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']."";
echo $hdr['name']."";

$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]"."";
}

$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]"."";
}

$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]"."";
}
}
?>

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

[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

[tomjohnson]

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.

[R0bb0b]

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

instead of doing a separate query for each.

[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."";
	
  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

[R0bb0b]

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

[R0bb0b]

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]"."";
	}

	$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]"."";
	}

	$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]"."";
	}

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]"."";
	}

[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']."";
  echo $hdr['name']."";
	
  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']."";
    }
  }
}
?>

Let us know how it goes ...

[Cindy2009]

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. :)

[Cindy2009]

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?

[Cindy2009]

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?

[Cindy2009]

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

[R0bb0b]

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.

[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 byfields (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.

...

[Cindy2009]

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 NameMorning 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! :)

[Cindy2009]

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

[R0bb0b]

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.

[Cindy2009]

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!

[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 :-)

...

[Cindy2009]

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?