PHP/MySQL foreach Unique output

There is a much easier way to look through results.

I don't understand why you are running the query twice either, once would be enough.  Maybe a copy paste error here?

<?php 
do {
 
   $field1 = $row['Customer Field1'];
   $field2 = $row['Customer Field2'];
 
} while ($row = mysql_fetch_assoc($sql));
?>

                                              

1:
2:
3:
4:
5:
6:
7:
8:

Actually with one select query, you have JUST ONE record per customer and the loop you have here should work.

Your sql should only have one query.

Here is the data outputted in a HTML table :)

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';";
$num = mysql_num_rows($sql);
if ($num > 0){
echo '<table>';
$x=0;
 while ($row = mysql_fetch_assoc($sql)){
  if ($x == 0){ // output field names ONCE
   $x=1;
   echo ' <tr>';
   foreach ($row as $key => $value){
     echo '  <td>' . $key . '</td>'; 
   }  
   echo ' </tr>';
  }
 
  echo ' <tr>';
  foreach ($row as $value){
     echo '  <td>' . $value . '</td>'; 
  }
  echo ' </tr>'; 
 }
echo '</table>';
}
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

Typo use this instead:

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
if ($num > 0){
 echo '<table>';
 $x=0;
 while ($row = mysql_fetch_assoc($sql)){
  if ($x == 0){ // output field names ONCE
   $x=1;
   echo ' <tr>';
   foreach ($row as $key => $value){
     echo '  <td>' . $key . '</td>'; 
   }  
   echo ' </tr>';
  }
 
  echo ' <tr>';
  foreach ($row as $value){
     echo '  <td>' . $value . '</td>'; 
  }
  echo ' </tr>'; 
 }
 echo '</table>';
}
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

Guys,

Ya, that was a type.  I'll test and try implementing the code and give results as I get them :)

Thanks!

** TYPO - I even had a typo on the word typo - sorry (long day).

NerdsOfTech:

Ok partially works.  Your code does output, however, the problem I am having is this.  How could I take each "chunk" of a particular customer (for instance all the results for CustomerID 22) and place those results into a list, and do it for each customer.  So, in theory, if I were to do an itemized list from it, I'm looking to do something like the following:

Customer 22:
   -- output
   -- output
   -- output
   -- output
   -- output
   -- output

Customer 23:
   -- output
   -- output
   -- output
   -- output
   -- output
   -- output

Customer 24:
   -- output
   -- output
   -- output
   -- output
   -- output
   -- output

So, what PHP would need to be written to iterate through the mySQL results to get this type of result?

Large data friendly (limited echo calls)...

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
if ($num > 0){
 echo '<table border="0">';
 $x=0;
 while ($row = mysql_fetch_assoc($sql)){
   $x++;
   $output  = ' <tr>';
   $output .= '  <td colspan="2">Customer ' . $x . '</td>';
   $output .= ' </tr>';
   $output .= ' <tr>';
   echo $output;
   
   foreach ($row as $key => $value){
    $output  = ' <tr>';
    $output .= '  <td>' . $key . '</td>'; // comment out this line if you dont want fieldnames for each block and add colspan="2" for next lines <td>
    $output .= '  <td>' . $value . '</td>'; 
    $output .= ' </tr>';
    echo $output;
   }  
 
   $output  = ' <tr>';
   $output .= '  <td colspan="2"><br /></td>';
   $output .= ' </tr>';
   echo $output;
 }
 echo '</table>';
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

line fixed use this version:

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
if ($num > 0){
 echo '<table border="0">';
 $x=0;
 while ($row = mysql_fetch_assoc($sql)){
   $x++;
   $output  = ' <tr>';
   $output .= '  <td colspan="2">Customer ' . $x . '</td>';
   $output .= ' </tr>';
   echo $output;
   
   foreach ($row as $key => $value){
    $output  = ' <tr>';
    $output .= '  <td>' . $key . '</td>'; // comment out this line if you dont want fieldnames for each block and add colspan="2" for next lines <td>
    $output .= '  <td>' . $value . '</td>'; 
    $output .= ' </tr>';
    echo $output;
   }  
 
   $output  = ' <tr>';
   $output .= '  <td colspan="2"><br /></td>';
   $output .= ' </tr>';
   echo $output;
 }
 echo '</table>';
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Let me try to implement this into my code and I'll let you know. I appriciate the help ;)

No problem let me know if it works so I can help debug if it doesn't

@NerdsOfTech,

This code does output "Customer 1" ... "Customer 2" ... "Customer 3", however, I only used that as an example.  So you did exactly what I said, but not as I wanted - I do thank you for that.  I'll explain a little better :)

I would like the output to be as follows:

Customer ID FROM DB (ex. ID 9):
 --- output of all results from database (if there are 10 results with ID 9, then list them all here)

Another Customer ID from DB (ex. ID 23):
 --- out of all the results from the database (if there are 10 results with ID 23, then list them all here).

... etc etc...

Is there a way to do this?

Thanks for your help so far! :)

Sure!

Since I don't know what your id field is called I will just reference this field as id

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
if ($num > 0){
 echo '<table border="0">';
 $x=0;
 while ($row = mysql_fetch_assoc($sql)){
   $x++;
   $output  = ' <tr>';
   $output .= '  <td colspan="2">Customer ' . $row['id'] . '</td>'; // change this fieldname accordingly
   $output .= ' </tr>';
   echo $output;
   
   foreach ($row as $key => $value){
    $output  = ' <tr>';
    $output .= '  <td>' . $key . '</td>'; // comment out this line if you dont want fieldnames for each block and add colspan="2" for next lines <td>
    $output .= '  <td>' . $value . '</td>'; 
    $output .= ' </tr>';
    echo $output;
   }  
 
   $output  = ' <tr>';
   $output .= '  <td colspan="2"><br /></td>';
   $output .= ' </tr>';
   echo $output;
 }
 echo '</table>';
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

I'll make the header say Customer ID and have a gray bgcolor for readability too :)

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
if ($num > 0){
 echo '<table border="0">';
 $x=0;
 while ($row = mysql_fetch_assoc($sql)){
   $x++;
   $output  = ' <tr>';
   $output .= '  <td colspan="2" bgcolor="#cccccc">Customer ID: ' . $row['id'] . '</td>'; // change this fieldname accordingly
   $output .= ' </tr>';
   echo $output;
   
   foreach ($row as $key => $value){
    $output  = ' <tr>';
    $output .= '  <td>' . $key . '</td>'; // comment out this line if you dont want fieldnames for each block and add colspan="2" for next lines <td>
    $output .= '  <td>' . $value . '</td>'; 
    $output .= ' </tr>';
    echo $output;
   }  
 
   $output  = ' <tr>';
   $output .= '  <td colspan="2"><br /></td>';
   $output .= ' </tr>';
   echo $output;
 }
 echo '</table>';
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

@NerdsOfTech:

OK, we are SOO close on this one.  This is what happens with your code:

Let's say that there is 10 results for Customer ID 9 ...

Customer ID from DB (customer id 9)
-- output results

Customer ID from DB (customer id 9):
-- output results

...... 8 more just like this for customer id 9...

Then it does the same for the next record.

What I am really wanting to do is output ALL of one customer, in a group (the customer ID) and then all the results under that.. like this:

Customer ID from DB (customer ID 9):
-- all output from all the results ID 9

Customer ID from DB (customer ID 23):
-- all output from all the results for ID 23

.. etc etc...

Is this possible?

Ya - I still can't get it to work - any ideas, perhaps?

Yes. BRB

try this:

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
if ($num > 0){
 echo '<table border="0">';
 $x='';
 while ($row = mysql_fetch_assoc($sql)){
   if ($x != $row['id']){
    $x = $row['id'];
    $output  = ' <tr>';
    $output .= '  <td colspan="2" bgcolor="#cccccc">Customer ID: ' . $row['id'] . '</td>'; // change this fieldname accordingly
    $output .= ' </tr>';
    echo $output;
   } 
   foreach ($row as $key => $value){
    $output  = ' <tr>';
    $output .= '  <td>' . $key . '</td>'; // comment out this line if you dont want fieldnames for each block and add colspan="2" for next lines <td>
    $output .= '  <td>' . $value . '</td>'; 
    $output .= ' </tr>';
    echo $output;
   }  
 
   $output  = ' <tr>';
   $output .= '  <td colspan="2"><br /></td>';
   $output .= ' </tr>';
   echo $output;
 }
 echo '</table>';
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

Ya, this still does the same thing:

Customer ID 9:
-- output

Customer ID 9:
--output

...all the way down to the next record...

...then...

Customer ID 23:
--output

Customer ID 23:
-- output

...all the way down to the next record...

Hmmm.  Thanks for the help, do you need me to provide any other information to help?

@NerdsOfTech:

Actually, is that "new" code, the same code from above?  It kinda looks like it from a quick glance.

first, what the field name for the id...is it actually id. thanks

CustomerID is the actual table name. Thanks!

Thank you.

$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.CustomerID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
if ($num > 0){
 echo '<table border="0">';
 $x='';
 while ($row = mysql_fetch_assoc($sql)){
   if ($x != $row['CustomerID']){ // new customer ID found print title ONCE
    $x = $row['CustomerID'];
    $output  = ' <tr>';
    $output .= '  <td colspan="2" bgcolor="#cccccc">Customer ID: ' . $row['CustomerID'] . '</td>'; // change this fieldname accordingly
    $output .= ' </tr>';
    echo $output;
   } 
   foreach ($row as $key => $value){
    if ($key != 'CustomerID'){
     $output  = ' <tr>';
     $output .= '  <td>' . $key . '</td>'; // comment out this line if you dont want fieldnames for each block and add colspan="2" for next lines <td>
     $output .= '  <td>' . $value . '</td>'; 
     $output .= ' </tr>';
     echo $output;
    }
   }  
 
   $output  = ' <tr>';
   $output .= '  <td colspan="2"><br /></td>';
   $output .= ' </tr>';
   echo $output;
 }
 echo '</table>';
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

Nice! Perfect!  I'm going to try to implement this into my code and let you know how it goes.  I'll update either tonight or tomorrow :)

@NerdsOfTech:

One more question about this output, then I'm assigning out points.  

This section:

foreach ($row as $key => $value){
    if ($key != 'CustomerID'){
     $output  = ' <tr>';
     $output .= '  <td>' . $key . '</td>'; // comment out this line if you dont want fieldnames for each block and add colspan="2" for next lines <td>
     $output .= '  <td>' . $value . '</td>';
     $output .= ' </tr>';
     echo $output;
    }
   }  

How would I be able to get the same funtionality, however, choose what information I want to output.  so instead of outputing everything, say I just want to output a certain table entry or 5 table entries, for ex - service date/type/fee/customer name/customer phone.

As of right now, it basically goes through and outputs everything.  I don't want everything outputed and I fumbled around with the code somewhat but was never able to figure it out.

You could do this multiple ways:

1. (RECCOMMENDED METHOD) SQL:
change the select statement to SELECT the desired fields
instead of:
SELECT * FROM ...
you would do
SELECT CustomerID, CustomerName, ...etc FROM

2. PHP: inside of the foreach you could add additional exemptions from the loop by including the fields in the if statement

 if ($key != 'CustomerID' ||  $key != 'SomeOtherField1' || $key != 'SomeOtherField2'){

-OR-

3.PHP: instead of a foreach () you could just reference the fieldnames via $row[] array
without foreach...
{not recommend becuase of the formatting needs of this case}

$row['CustomerID]';
$row['CustomerName'];

Thanks!

NerdsOfTech:

Let me explain my situation real quick and see if you can point me into the right direction.  Based of your information above, it seems like the "recommended" way is more "tabular".  I could be totally wrong here.  However, it doesn't make sense to me for what I'm trying to do.  I actually need the following fields from the database (for output):

CustomerID, ServiceAddress, Billing ADdress, Date of service, ServiceFee, fuel surcharge - also i'm going to be grabbing each service fee and adding it together for a total.

This SQL statement is actually for a report I'm creating for an on-the-fly PDF creation.  So, basically, I'm going to be formatting this "output" a certain way.  The way you recommended above seems too "tabular" as in you will basically have a "row" and a "value" side by side.  I'm not sure if I just don't get it or if there's a better way to go about this, or if I just need to be told "this is how you do it".  

I appreciate your help and await your response.

Thanks for the update.

The recommendation was how to "filter out" fields you didn't want outputted. It would be the same output but less fields shown.

Nonetheless, you can have the data "outputted" any way you want...we have the data. Just let me know how you want it to look:

one row per data row

| Customer ID | Service Address | Billing Address | Date of Service | Service Fee | Fuel Surcharge|
| 12345 | 111 some st | 111 some str | 8-21-2009 | 65.00 | 5.00 |
| 12346 | 111 some st | 111 some str | 8-21-2009 | 1215.00 | 15.00 |
| 12347 | 111 some st | 111 some str | 8-21-2009 | 135.00 | 5.00 |

two rows per data row

| 12345 | 111 some st | 111 some str
| 8-21-2009 | 65.00 | 5.00 |

| 12346 | 111 some st | 111 some str
| 8-21-2009 | 1215.00 | 15.00 |

| 12347 | 111 some st | 111 some str
| 8-21-2009 | 135.00 | 5.00 |

one row per field (current output)

| 12345
| 111 some st
| 111 some str
| 8-21-2009
| 65.00
| 5.00 |

| 12346
| 111 some st
| 111 some str
| 8-21-2009
| 1215.00 | 15.00 |

| 12347
| 111 some st
| 111 some str
| 8-21-2009
| 135.00
| 5.00 |

etc, etc.

Again, just let me know.
Thank you,

One more thing... You can add up totals in SQL so let me know what needs to be added up.

Also, you can get group totals

=NOT

NOT:

I'm not sure how I want it to "look".  I will tell you this.  I will want the customer ID at the top of the PDF page, then to the right, I'll want the billing information for that particular customer.  Then the the left of that I Want the service address.  Below all of this, I'll want the service dates - I'll want "Date of service" - then the service fee at the end of that line, and then each date of service not paid for, I want it to be a line item.

Then after all this, I want a total.  Which would be all the service fee's added together from SQL.

So the layout of where I want the individual items is going to be all laid out on the PDF doc, so I'll need to be able to store output into a variable and "stick" it wherever I want it.

As for the adding up totals, I  will need to add all the ServiceAmount values for that certain CustomerID (for ex CustomerID 22), plus the FuelSurcharge value (right now we don't have any fuel surcharges, but when used, they will need to be factored in).

Thats what I'm needing. I just basically need a way to take the results, place them in a variable, and place them into a PDF, and create line items of the service details on a PER CUSTOMER ID basis.  

Let me know :)

Okay... wow that is quite a goal.

Now I think that may constitute as multiple questions here.

I can get you to the output format you desire for this question.

The rest we can attach as related questions

Would that be okay with you?

Of course.  I can actually handle the PDF stuff - just getting the output into variables.  You tell me the type of questions you want me to open with somewhat the information you are wanting.  I'll make them 500 points a pop - and I'll work with you to get you the points you've deserved.  Just tell me how many questions and what they need to entitle, and I'm more than willing.

First, What are you using to make the pdf file? is it the built in PDFlib ?

Nope - it's fPDF.

I've already created a report that works great - but has a far less sophisticated SQL statement and doesn't need to be itemized.  So it works perfectly.  But this one i'm doing right now is a little more in depth, as it will be used to create invoices.

Ok cool I'll get back to you tomorrow and I'll go over what you need.

Thanks!

Sounds great! :) Thanks again.  I should be around and online from 7am CST until 7pm CST - I'll be at work - so I'll be on the computer.

Again- thanks.

Looks like a busy weekend. I'll try to get back to you on Monday.

Best regards,
=NOT

Will do - thanks for the update.

NOT:

It's Thursday, and I haven't received any response.  Is this something we can work out?

-Drew

NOT

Is this project going to be too much to handle and should I just close out this and give you your 500 points?

Please do let me know.

-- Drew

Angellll,

Not sure how, as I was told that this would be followed up on - and it never was.

See above remarks.

I apologize. I meant to follow up on this but got extremely busy with a full-allocation-lock programming schedule (that is why I haven't answered questions for many weeks) and was unable to answer further.

@drewrockshard, Please add a "related question" for the pdf formatting to get help from other experts; so they can use the information from here.

Angeliii, I suggest close with points refunded to asker for his patience: @drewrockshard.

=NerdsOfTech

Agreed.