Do I need a nested foreach loop to do this? Help...

[fjm]

Hello everyone and happy Sunday. :)

I have a problem that I *think* I may know the solution to but have no idea how to write the code for it. I am working on a templating system wher I have created a template html file. I am using str_replace to replace the fields that I want changed. I'm not sure that I am doing this correctly either. A code sample follows.

I have a script that pulls a single customer from the database. The data that is pulled is the name of the company and other details about that company such as phone numbers, etc. Since a customer has many different phone numbers, I am getting the customer name repeated as many times as are phone numbers. This is exactly what the db should be doing but I need to somehow use php to echo the company name once and then loop all of the phone numbers, and addresses that are distinct to that customer.

        foreach($result as $content)
        {
            $out = str_replace("{LOCATION_NAME}", "$content[location_name]", "$tpl");
            $out .= str_replace("{MANAGER}", "$content[manager]", "$tpl");
 
            $out .= str_replace("{EMPLOYEE}", "$content[employee]", "$tpl");
 
echo $out;
        }

Of course with this foreach loop, I am getting six different pages because of my query. Should I be using two seperate queries? One to get the name of the location and then another for the multiple rows of data? I am almost sure that there is a way to maybe nest a foreach loop to get this.

Also, someone please tell me if I am doing this correctly. I created this html file and I am using fopen() to read the contents. I need to make about 7 or 8 replacements throughout that document. Is my str_replace code correct to do this? Am I on the right track here?

Would someone please help me out with this.. I really appriciate it

Frank

[fjm]

I have taken a break from this for a while and think I may have thought of the answer. I am thinking:

[PHP]
foreach($a as $b){
echo $b['something'];
foreach($b as $c){
echo $c['something'];
}
}
[/PHP]

Would this work? Am I on the right track?

[dlite922]

You're going about the templating the wrong way. This takes a lot of resources due to string manipulation and is absolutely not necessary.

Use Smarty templating system or another. if not, your html file should contain <?php echo $companyName ?> in it.

This way, all you have to is make sure that $companyName is a variable that's declared and initialized before you include() your html file.

for example here's an example PHP file:

[PHP]

<?php

$companyName = "test";

include("page.php");
exit();
?>
[/PHP]

then your page.php would include something like this:

 
include("header.ext"); 
 
<div id="company"><?php echo $companyName ?></div>
 
include("footer.ext");
 
 

Hope that makes sense,



Dan

[fjm]

Hi Dan. Thanks for the help and the direction. I used Smarty a few years back and I really didn't care for it that much. I would prefer to stay away from it where possible.

I thought about your second option of simply echoing the values in the html and I will definately go that route. That still leaves me with the same problem looping over my result set.

I need to compile one report per customer. The result set may have 5 or 10 or 20 different things associated with that customer. If I loop over the set I will get 5 or 10 or 20 reports.I only need a single report and was why I thouhgt that a nested foreach would be the ticket here.

Can you please advise? Or maybe I am not following what you were trying to say.

Thanks,

Frank

[pbmods]

Heya, Frank.

Assuming you have a string that looks something like this:

<tr>
  <td>{LOCATION_NAME}</td>
  <td>{MANAGER}</td>
  <td>{EMPLOYEE}</td>
</tr>
 

We'll call that $template.

You'll end up doing something like this:

$rendered = '';
 
foreach( $result as $content )
{
  $rendered .=
    str_replace
    (
        array
        (
            '{LOCATION_NAME}'
          , '{MANAGER}'
          , '{EMPLOYEE}'
        )
      , array
        (
            $content['location_name']
          , $content['manager']
          , $content['employee']
        )
      , $template
    );
}
 

This way, you render each row in the table (in this case) once for each record and then append the rendered content onto the output string.

[fjm]

Pbmods, thanks.... I am going to try that now. I will be back soo to let you know how that worked out. Thanks soo much!

Frank

[fjm]

Heya again Pbmods. :)

Thanks again for the help. The code you gave works and on the surface, appears to do exactly what mine was doing. It IS working, however, I still have the issue of creating 20+ reports when I only need one.

Here is the code I am currently working with:
[PHP]
<?php
require_once("db.php");
$db = new Database('test');

$filename = "report.tpl";
$handle = fopen($filename, "r");
$template = fread($handle, filesize($filename));
fclose($handle);

$rendered = '';

$result = $db->Query("SELECT location_name, addr1, city, name, email......");

foreach($result as $content)
{
$rendered .= str_replace(array('{LOCATION_NAME}', '{NAME}'), array($content['location_name'], $content['name']), $template);
echo $rendered;
}
?>
[/PHP]

The foreach loops every customer with as many email addresses or phone numbers it has (whichever is more). What I need is this:

 report1
Customer1
address1
phone1
phone2
phone3
phone4 ...
 
 report2
Customer2
address1
phone1 (maybe this customer only has 1 phone.. it should stop here and go to report 3)
 

What I am getting is:
customer1
phone1
customer1
phone2
customer1
phone3
customer1
phone4 ...

Sorry Pbmods, I hope I am making sense here. The loop is generating massive amounts of reports instead of just a single report.

In other words, I get a new template generated for each and every phone number or employee name or whatever. I just need it all to be contained within 1 loop. It would seem to me that if I had a loop to get the customer name, address, city, state, zip and then had a second loop to get the items that customer had such as all phones, all employees, etc and compile that data with the first outer loop.

Should I be using a while loop outside and a foreach inside?

[Atli]

Hi.

You will simply have to format the data *before* you print them, or put them into your template.

Consider this:

$data = array();
while($row = mysql_fetch_assoc($result)) {
  $data[$row['name']] = $row['number'];
}
foreach($data as $_name) {
  $numbers = "";
  foreach($_name as $_number) {
    $numbers .= "<tr><td>$_number</td></tr>";
  }
  echo "<table><tr><th>$_name</th></tr>$numbers</table>";
}
 

Which would give you a table for each name, containing the numbers for that name alone.

[fjm]

I'm sorry Atli, I wish I could say it worked. :(

[pbmods]

Ah, ok.

The way I like to do it is to keep track of the primary key with a temporary variable:

$lastID = null;
 
foreach( $result as $content )
{
  if( $content['CustomerID'] != $lastID )
  {
    $lastID = $content['CustomerID'];
    $rendered .= "<tr><th>{$content['CustomerID']}</th></tr>";
  }
 
  $rendered .= str_replace( ... );
}
 

Every time you hit a new customer ID, it will add a header row.

The only gotcha here is that you have to sort your results by CustomerID first.

[Atli]

I'm sorry Atli, I wish I could say it worked. :(

Well you would need to adjust it to fit your data :)
It was only an example, just to show one way to sort the data before using it.

[fjm]

Well you would need to adjust it to fit your data :)
It was only an example, just to show one way to sort the data before using it.

lol.. I know but I still could not get it to work. Sorry for the short post yesterday and no explanation. I was tired and a bit upset. Today is a new day and I hope I will be able to fix this.

[PHP]while($row = mysql_fetch_assoc($result)) {
$data[$row['name']] = $row['number'];[/PHP]

The problem is that my DBAL is setup to handle the fetch_assoc() and already returns the results as an array. So..
[PHP]$result = $db->Query("..."); returns the mysql fetch_assoc()[/PHP]
I am not sure how to adjust that part of your sample to work. I tried
[PHP]while($result)[/PHP] and if I remember correctly, it went into an endless loop.

Also, I was getting a foreach loop error as well.

Thanks for the help Atli.

[fjm]

Hey Pbmods,

I am using a composite PK in this table. Here is how I thought it should be rewritten
[PHP]
$rendered = '';
$lastID = null;

foreach( $result as $content )
{
if(($content['customer_id'] && $content['location_id']) != $lastID )
{
$lastID = $content['customer_id'] . $content['location_id'];
$rendered .= "<tr><th>".$content['customer_id'] . $content['location_id']."</th></tr>";
}
echo $rendered;
// $rendered .= str_replace( ... );
}
[/PHP]

I am starting to think that somehow I have not explained my requirements well. Would you guys mind if I tried one last time to explain what I am trying to do here? Let me please start over.

I have a template that I created in html. It is called report.tpl. This report has 7 fields delimited by {} that need to be replaced by str_replace. These fields are location_name, address, city, person_name, person_email, employee_name, and report_type.

The PK of this table I am pulling my data from has a composite PK that consists of customer_id and location_id.

With these 7 fields, I had planned to populate this template. So, a single customer (which again, is always customer_id and location_id) for example can obviously list in this report:

more than 1 person_name
more than 1 person_email
more than 1 employee_name
more than 1 report_type

The first 3 fields (location_name, address, city) need to populate the top of the template. The remaining 4 fields I have listed above should loop however many results are found in the database and again be replaced by my delimited fields in the lower half of my html template.

Am I not going about this the right way? I cannot seem to get this to work.

Thanks again guys and I'm sorry for the long thread.

Frank

[fjm]

Hello again everyone. I have managed to write in a very sloppy and procedurally way, the format of what I need. The first foreach sets up the customer_id and the location_id for the other 3 nested loops. Can someone please help me to optimize this?

Here is what I have.
[PHP]<?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]

Sometimes, writing something in code is better than an explanation.

Thanks for the help and the patience guys. :)

Frank

[Atli]

Ok, so that would give you a list with every name+location that has a report whose date is grater than tomorrow, each followed by a list of reports, ordered from type1 to type3.

You could combine the last 3 queries by adding a ORDER BY clause, specifying the t3.report_type column, and removing current line in the WHERE clause.

Is this the output you wanted? It doesn't look like what you posted earlier.
I thought you wanted to print a list of phone numbers for each customer.

Also, I got to ask. Why do you join the CustomerID and LocationID as a primary key? That's something I avoid like the plaque. It only makes things more complex, and it is almost never necessary.

Maybe if you posted the structure of all them tables it would make more sense?

P.S.
I added a few line-brakes and spaces to your code, just to make it easier to read. These single-line queries are impossible to read :P
Hope you don't mind.

[fjm]

Ok, so that would give you a list with every name+location that has a report whose date is grater than tomorrow, each followed by a list of reports, ordered from type1 to type3.

Actually, it is less than. "date_sub"

You could combine the last 3 queries by adding a ORDER BY clause, specifying the t3.report_type column, and removing current line in the WHERE clause.

Ok, unless I am missing something, using the group by clause, how would I then tell php where to insert report1 or report2? The reports follow a certain order.

Is this the output you wanted? It doesn't look like what you posted earlier.
I thought you wanted to print a list of phone numbers for each customer.

No, it is not the format I wanted but I am trying to get something working here. I am two days overdue on this project because of this snag. I figured I could always go back later and fix it. Half a loaf is better than nothing. :)

Also, I got to ask. Why do you join the CustomerID and LocationID as a primary key? That's something I avoid like the plaque. It only makes things more complex, and it is almost never necessary.

Because the business requirements say that it has to be this way. I inherited a nightmare here.

If you want, I can post all 74 tables in the database but I think that might be counterproductive. Let me know.

P.S.
I added a few line-brakes and spaces to your code, just to make it easier to read. These single-line queries are impossible to read :P
Hope you don't mind.

Of course not Atli. Thanks, I agree, it was a lot of code. That was why I tried to explain in words and not post all this spaghetti. :)