DaniWeb Forum Index > Web Development > PHP

Optimizing a lot of foreach loops. Please help.

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Last »

•

Join Date: Sep 2007

Posts: 18

Reputation: tomjohnson is an unknown quantity at this point

Solved Threads: 0

tomjohnson tomjohnson is offline

Offline

Newbie Poster

Optimizing a lot of foreach loops. Please help.

Aug 18th, 2008

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:

Help with Code Tags

(Toggle Plain Text)

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

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

Last edited by tomjohnson; Aug 18th, 2008 at 11:12 pm.

•

Join Date: Aug 2008

Posts: 381

Reputation: langsor is an unknown quantity at this point

Solved Threads: 33

langsor

Offline

Posting Whiz

Re: Optimizing a lot of foreach loops. Please help.

Aug 18th, 2008

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

•

Join Date: Sep 2007

Posts: 18

Reputation: tomjohnson is an unknown quantity at this point

Solved Threads: 0

tomjohnson tomjohnson is offline

Offline

Newbie Poster

Re: Optimizing a lot of foreach loops. Please help.

Aug 18th, 2008

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.

•

Join Date: Jun 2008

Posts: 849

Reputation: R0bb0b is on a distinguished road

Solved Threads: 67

R0bb0b

Offline

Practically a Posting Shark

Re: Optimizing a lot of foreach loops. Please help.

Aug 18th, 2008

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

instead of doing a separate query for each.

“Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.” - Dr. Seuss

-- The documentation is inevitable, you may get away with it for a little while but eventually you too will have to do the deed.

•

Join Date: Aug 2008

Posts: 381

Reputation: langsor is an unknown quantity at this point

Solved Threads: 33

langsor

Offline

Posting Whiz

Re: Optimizing a lot of foreach loops. Please help.

Aug 19th, 2008

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

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<html>
<head>
<script type="text/javascript">
</script>
</head>
<body>
  <h1 id="my_id"></h1>
  <p>Some content</p>
</body>
</html>
<html>
<head>
<script type="text/javascript">
</script>
</head>
<body>
  <h1 id="my_id"></h1>
  <p>Some content</p>
</body>
</html>

examples.php

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<?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
 
?>
<?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

Last edited by langsor; Aug 19th, 2008 at 1:05 am.

•

Join Date: Jun 2008

Posts: 849

Reputation: R0bb0b is on a distinguished road

Solved Threads: 67

R0bb0b

Offline

Practically a Posting Shark

Re: Optimizing a lot of foreach loops. Please help.

Aug 19th, 2008

•

Originally Posted by 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')

•

Join Date: Jun 2008

Posts: 849

Reputation: R0bb0b is on a distinguished road

Solved Threads: 67

R0bb0b

Offline

Practically a Posting Shark

Re: Optimizing a lot of foreach loops. Please help.

Aug 19th, 2008

so basically you can condense all this:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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>";
	}
$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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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>";
	}
$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>";
	}

Last edited by R0bb0b; Aug 19th, 2008 at 1:20 am.

•

Join Date: Aug 2008

Posts: 381

Reputation: langsor is an unknown quantity at this point

Solved Threads: 33

langsor

Offline

Posting Whiz

Re: Optimizing a lot of foreach loops. Please help.

Aug 19th, 2008

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

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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>";
    }
  }
}
?>
<?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 ...

Last edited by langsor; Aug 19th, 2008 at 1:33 am.

•

Join Date: Aug 2008

Posts: 25

Reputation: Cindy2009 is an unknown quantity at this point

Solved Threads: 0

Cindy2009

Offline

Light Poster

Re: Optimizing a lot of foreach loops. Please help.

Aug 19th, 2008

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.

Last edited by Cindy2009; Aug 19th, 2008 at 1:38 am.

•

Join Date: Aug 2008

Posts: 25

Reputation: Cindy2009 is an unknown quantity at this point

Solved Threads: 0

Cindy2009

Offline

Light Poster

Re: Optimizing a lot of foreach loops. Please help.

#10

Aug 19th, 2008

•

Originally Posted by R0bb0b

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?