Avatar of cgustaf
cgustaf
 asked on

SPLIT MYSQL OUTPUT TO SEVERAL PAGES...

I have a query that outputs payments to more than one vendor.  It is attached.

The output looks as follows:

id: 1 BIBLE TRANSLATION IN THE TUVIN LANGUAGE. $1,333.00
id: 1 BIBLE TRANSLATION IN THE UZBEK LANGUAGE. $1,222.00
id: 2 GENERAL SUPPORT OF BIBLE TRANSLATION.$1,555.00

There are two different vendor IDs in the above example -- two items for id #1, and one item for item #2.  There could be more vendors.

I would like to paginate the output so that the payments for each different vendor is printed on a separate page -- in the above example, the first page will display the two items for vendor id1, and the second page the one item for vendor id2.  That is, the payments to the vendors are paginated on the vendor id.  (In the query, this id is called t_expend.expend_id.)

The database is in MySQL, and I wonder if this pagination can be done in SQL -- if so, how?  Or, should it be done some other way?
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "SELECT t_desig.prefix_id, t_desig.lang_id, t_desig.postscr_id , CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, t_desig.desig_id, t_recip.recip_id,t_recip.recipnt,t_expend.paid_date, t_expend.exp_ref,t_expend.exp_done, t_expend.expend_id, t_expend.exp_done2,t_expend.exp_amt FROM t_desig  LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id WHERE t_desig.desig_id = t_expend.desig_id AND t_expend.recip_id = t_recip.recip_id AND t_expend.exp_done = 'Y' AND t_expend.paid_date = CURDATE() ORDER BY  t_expend.recip_id, t_desig.desig_id ASC";
$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$row_rs1 = mysql_fetch_assoc($rs1);
$totalRows_rs1 = mysql_num_rows($rs1);

Open in new window

PHPMySQL Server

Avatar of undefined
Last Comment
NerdsOfTech

8/22/2022 - Mon
Neil Russell

This will need to be done in whever application/eprt generator you are using to output your data.
Marco Gasi

I suggest to read this article about pagination: http://www.sitepoint.com/perfect-php-pagination/ You ''can find useful info for your job.
Cheers
G_H

I agree with Neilsr,

There are two ways to do this. Either re-structure the SQL to two separate statements, each for it's own page, or build to output variables in you application, while looping the recordset, and show each on a different page...

GH
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
cgustaf

ASKER
Thanks for your responses.

Unfortunately, I'm not a coder.  I use DW MX, but am nowadays often running into difficulties for which DW seemingly offers no solutions.

I have read the article at http://www.sitepoint.com/perfect-php-pagination/  and it appears to deal with pagination of text -- such as printing only ten names on any one page.  This is not really my issue, I think.

What I need to do is to separate the data for each t_expend.expend_id onto their own pages, so that in the case of the example I gave the data for id#1 is printed on one page, and the data of for id #2 is printed on a subsequent page, etc...  Thus, the issue is not really a pagination issue, seemingly.

Perhaps you have some further thoughts on how to accomplish this.  
Walter Ritzel

One idea is to create an index of all vendor ids, and for each vendor id, show their page when clicking on the id.
Meaning as well that the query you have to show the sample you put in your question would have a new parameter, which is the vendor id...
cgustaf

ASKER
It appears that G.H.'s second option is the most promising.  But, I need help implementing it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
G_H

Hi There,

I have re-read the post, and I can see you are using PHP?? As I am not a PHP guy, I cannot answer this fully, but this should give you or the PHP guys an idea of how to implement the second option...

(In Quasi Code)

Get Record Set
  Start Record Set Loop
    if Field = SupplierA then
      OutputForPage1 = OutputForPage1 &+. Whatever from the Record Set
    elseif Field = SupplierB then
      OutputForPage2 = OutputForPage2 &+. Whatever from the Record Set
    end if
  End of Record Set Loop

Page 1 Content = OutputForPage1
Page 2 Content = OutputForPage2

Hope this helps,

GH

NerdsOfTech

Are you trying to actually PRINT the data on separate pages?
If so, you can just output all of the data on one page and separate the groups using <div> and CSS page breaks.

If you are actually trying to paginate the records into separate pages then you can do this multiple ways.

The most secure way is to paginate based on enumerated groups:
1. create a numerical id reference of the groups via SQL:
SELECT 
 @groupID := @groupID + 1 groupID, 
 t.*
FROM 
 (SELECT @rownum:=0) r,
 SELECT 
  * 
 FROM (
  SELECT 
   t_expend.expend_id as vendorID 
  FROM 
  (
   SELECT 
    t_desig.prefix_id, 
    t_desig.lang_id, 
    t_desig.postscr_id , 
    CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
    t_desig.desig_id, 
    t_recip.recip_id,
    t_recip.recipnt,
    t_expend.paid_date, 
    t_expend.exp_ref,
    t_expend.exp_done, 
    t_expend.expend_id, 
    t_expend.exp_done2,
    t_expend.exp_amt 
   FROM 
    t_desig  
   LEFT JOIN t_lang        ON t_desig.lang_id = t_lang.lang_id   
   LEFT JOIN t_prefix     ON t_desig.prefix_id = t_prefix.prefix_id    
   LEFT JOIN t_postscr    ON t_desig.postscr_id = t_postscr.postscr_id  
   LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
   LEFT JOIN t_recip      ON t_recip.recip_id = t_expend.recip_id 
   WHERE 
    t_desig.desig_id = t_expend.desig_id 
    AND t_expend.recip_id = t_recip.recip_id 
    AND t_expend.exp_done = 'Y' 
    AND t_expend.paid_date = CURDATE() 
   ORDER BY  
    t_expend.recip_id, t_desig.desig_id ASC
  ) s
  GROUP BY 
   t_expend.expend_id
 ) t

Open in new window


2. Next determine the page the user is on via GET
if no get variable exists output page 1
if get varable exists get page number

3. Output records based the selected page (which will output record(s) for the selected vendorID)
t_expend.expend_id = the referenced page number thus we can now retrieve the records pertaining to just that VendorId: (see below, code line 27)

SELECT 
 t_desig.prefix_id, 
 t_desig.lang_id, 
 t_desig.postscr_id , 
 CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
 t_desig.desig_id, 
 t_recip.recip_id,
 t_recip.recipnt,
 t_expend.paid_date, 
 t_expend.exp_ref,
 t_expend.exp_done, 
 t_expend.expend_id, 
 t_expend.exp_done2,
 t_expend.exp_amt 
FROM 
 t_desig  
LEFT JOIN t_lang        ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix     ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr    ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip      ON t_recip.recip_id = t_expend.recip_id 
WHERE 
 t_desig.desig_id = t_expend.desig_id 
 AND t_expend.recip_id = t_recip.recip_id 
 AND t_expend.exp_done = 'Y' 
 AND t_expend.paid_date = CURDATE() 
 t_expend.expend_id = $vendorID
ORDER BY  
 t_expend.recip_id, t_desig.desig_id ASC

Open in new window


4. After selected data is outputted, output the page number links based on the enumerated groupIDs (the first SQL statement) or by your own preferred method.

cgustaf

ASKER
Thanks, NerdsOfTech.  I will take a look at your sugestion, and hopefully it will work.  Stand by.
Your help has saved me hundreds of hours of internet surfing.
fblack61
cgustaf

ASKER
Two questions:

In step 1, my assumption is that nothing needs to be changed by me in lines 1-7.  Is this correct?

I do not understand step 2.  Can you explain this a bit more?

-----------------------------
With respect to your question:  "Are you trying to actually PRINT the data on separate pages?"  

The answer is YES.

For each recip_id in t_expend, there can be be several records (each with its own expend_id, of course).  The output (printed page) should display all expend_id's (along with accompanying data) for each individual recip_id.

Example:

recip_id 1
   expend_id 3
   expend_id 6
   expend_id 7
   etc...
-------------- page break -----------
recip_id 2
   expend_id 1
   expend_id 2
   expend_id 4
   etc...
------------- page break -----------
other recip_id's may follow on addtional pages.

If "...output all of the data on one page and separate the groups using <div> and CSS page breaks" can accomplish this, perhpas this is the approach we should pursue?

Your further input on this is appreciated.
G_H

Read my post 36324487 above and modify so:

Response.write OutputForPage1
Response.write "<hr style='page-break-after:always;'>"
Response.write OutputForPage2

GH


NerdsOfTech

Thank you for your response.

Here is the code (including comments) which will complete the solution


mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_done2,
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_row1 = mysql_fetch_assoc($result); 	// extract first row
$rs1_curGroupID = $rowFirst["recip_id"]; 	// set $rs1_curGroupID to first row recip_id
mysql_data_seek($rs1,0); 			// reset cursor to row 0 after previous data fetch via seek 0
$rs1_num_rows = mysql_num_rows($rs1);
$rs1_curRow = 0; 				// current row index

while ($row = mysql_fetch_assoc($rs1)) {
 // loop through rows
 // OUTPUT FORMAT
 // recip_id 1
 //   expend_id 3
 //   expend_id 6
 //   expend_id 7
 //   etc...
 // -------------- page break -----------

 if($row["recip_id"] <> $rs1_CurGroupID){ 	
  // different group - co close previous divs and print title
  echo " </DIV>\n";
  echo "</DIV>\n";
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "<BR />\n";  
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
  $rs1_CurGroupID = $row["recip_id"]  // set $rs1_CurGroupID to current recip_id
 }
 elseif($rs1_curRow == 0){ 
  // first group - so print title
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "<BR />\n";   
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
 }

 echo $row["expend_id"] . "<BR />\n";
 $rs1_curRow++; // increment current row number
}

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cgustaf

ASKER
Thanks,  I'll check it in the morning.
NerdsOfTech

a slight edit to fix the output format     test-groupprint.pdf test-groupprint.html
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_done2,
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_row1 = mysql_fetch_assoc($result); 	// extract first row
$rs1_curGroupID = $rowFirst["recip_id"]; 	// set $rs1_curGroupID to first row recip_id
mysql_data_seek($rs1,0); 			// reset cursor to row 0 after previous data fetch via seek 0
$rs1_num_rows = mysql_num_rows($rs1);
$rs1_curRow = 0; 				// current row index

while ($row = mysql_fetch_assoc($rs1)) {
 // loop through rows
 // OUTPUT FORMAT
 // recip_id 1
 //   expend_id 3
 //   expend_id 6
 //   expend_id 7
 //   etc...
 // -------------- page break -----------

 if($row["recip_id"] <> $rs1_CurGroupID){ 	
  // different group - co close previous divs and print title
  echo " </DIV>\n";
  echo "</DIV><BR />\n\n";
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";  
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
  $rs1_CurGroupID = $row["recip_id"]  // set $rs1_CurGroupID to current recip_id
 }
 elseif($rs1_curRow == 0){ 
  // first group - so print title
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";   
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
 }

 echo $row["expend_id"] . "<BR />\n";
 $rs1_curRow++; // increment current row number
}

Open in new window

test-groupprint.png
NerdsOfTech

one more edit to fix line 35 $result TO $rs1 :)
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_done2,
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_row1 = mysql_fetch_assoc($rs1); 	// extract first row
$rs1_curGroupID = $rowFirst["recip_id"]; 	// set $rs1_curGroupID to first row recip_id
mysql_data_seek($rs1,0); 			// reset cursor to row 0 after previous data fetch via seek 0
$rs1_num_rows = mysql_num_rows($rs1);
$rs1_curRow = 0; 				// current row index

while ($row = mysql_fetch_assoc($rs1)) {
 // loop through rows
 // OUTPUT FORMAT
 // recip_id 1
 //   expend_id 3
 //   expend_id 6
 //   expend_id 7
 //   etc...
 // -------------- page break -----------

 if($row["recip_id"] <> $rs1_CurGroupID){ 	
  // different group - co close previous divs and print title
  echo " </DIV>\n";
  echo "</DIV><BR />\n\n";
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";  
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
  $rs1_CurGroupID = $row["recip_id"]  // set $rs1_CurGroupID to current recip_id
 }
 elseif($rs1_curRow == 0){ 
  // first group - so print title
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";   
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
 }

 echo $row["expend_id"] . "<BR />\n";
 $rs1_curRow++; // increment current row number
}

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
cgustaf

ASKER

I encountered a squiggly bracket error as follows:

"Parse error: syntax error, unexpected '}' in /home/ibtib/public_html/dbc/don/reps/don_disb_report_test.php on line 104"

In the attached code this may not be on line 104, but I remmed the position in the code before sending it to you now.


 
<?php
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_done2,
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_row1 = mysql_fetch_assoc($rs1); 	// extract first row
$rs1_curGroupID = $rowFirst["recip_id"]; 	// set $rs1_curGroupID to first row recip_id
mysql_data_seek($rs1,0); 			// reset cursor to row 0 after previous data fetch via seek 0
$rs1_num_rows = mysql_num_rows($rs1);
$rs1_curRow = 0; 				// current row index

while ($row = mysql_fetch_assoc($rs1)) {
 // loop through rows
 // OUTPUT FORMAT
 // recip_id 1
 //   expend_id 3
 //   expend_id 6
 //   expend_id 7
 //   etc...
 // -------------- page break -----------

 if($row["recip_id"] <> $rs1_CurGroupID){ 	
  // different group - co close previous divs and print title
  echo " </DIV>\n";
  echo "</DIV><BR />\n\n";
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";  
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
  $rs1_CurGroupID = $row["recip_id"]  // set $rs1_CurGroupID to current recip_id

// Closing squiggly bracket error here:

 }

 elseif($rs1_curRow == 0){ 
  // first group - so print title
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";   
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
 }

 echo $row["expend_id"] . "<BR />\n";
 $rs1_curRow++; // increment current row number
}
?>
<?php  $lastTFM_nest = "";?>

Open in new window

cgustaf

ASKER
It's on line 63.
NerdsOfTech

line 59 semi colon missing
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NerdsOfTech

fixed line 59 and removed debug comment.

line 59 from:
$rs1_CurGroupID = $row["recip_id"]

to:
$rs1_CurGroupID = $row["recip_id"];

Let me know if that worked
<?php
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_done2,
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_row1 = mysql_fetch_assoc($rs1); 	// extract first row
$rs1_curGroupID = $rowFirst["recip_id"]; 	// set $rs1_curGroupID to first row recip_id
mysql_data_seek($rs1,0); 			// reset cursor to row 0 after previous data fetch via seek 0
$rs1_num_rows = mysql_num_rows($rs1);
$rs1_curRow = 0; 				// current row index

while ($row = mysql_fetch_assoc($rs1)) {
 // loop through rows
 // OUTPUT FORMAT
 // recip_id 1
 //   expend_id 3
 //   expend_id 6
 //   expend_id 7
 //   etc...
 // -------------- page break -----------

 if($row["recip_id"] <> $rs1_CurGroupID){ 	
  // different group - co close previous divs and print title
  echo " </DIV>\n";
  echo "</DIV><BR />\n\n";
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";  
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
  $rs1_CurGroupID = $row["recip_id"];  		// set $rs1_CurGroupID to current recip_id

// Closing squiggly bracket error here:

 }

 elseif($rs1_curRow == 0){ 
  // first group - so print title
  echo "<DIV style='page-break-after:always;'>\n";
  echo $row["recip_id"] . "\n";   
  echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
 }

 echo $row["expend_id"] . "<BR />\n";
 $rs1_curRow++; 				// increment current row number
}
?>
<?php  $lastTFM_nest = "";?>

Open in new window

cgustaf

ASKER
Line 32 above reports this error:

Warning: mysql_data_seek() [function.mysql-data-seek]: Offset 0 is invalid for MySQL result index 3 (or the query data is unbuffered) in /home/ibtib/public_html/dbc/don/reps/don_disb_report_test.php on line 82

NerdsOfTech

That is a "warning" for empty set seeking.

I fixed it so if you have an EMPTY result it shouldn't give you a warning. Plus i fixed an error on line 37 (where did that variable come from? LOL it was me)

I removed the error comment, format comments, and the last line of code too.

If you need that last line still it just pop it back in there.
<?php
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_done2,
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_num_rows = mysql_num_rows($rs1);		// row count
if ($rs1_num_rows > 0){				// only work with non-empty results
 
 $rs1_row1 = mysql_fetch_assoc($rs1); 		// extract first row
 $rs1_curGroupID = $rs1_row1["recip_id"]; 	// set $rs1_curGroupID to first row recip_id
 						
 if (!mysql_data_seek($rs1, 0)) {		// reset cursor to row 0 after previous data fetch via seek 0
   // could not seek
 }else{						// non-empty set
  $rs1_curRow = 0; 				// current row index
  
  while ($row = mysql_fetch_assoc($rs1)) {
  
   if($row["recip_id"] <> $rs1_CurGroupID){ 	
    // different group - co close previous divs and print title
    echo " </DIV>\n";
    echo "</DIV><BR />\n\n";
    echo "<DIV style='page-break-after:always;'>\n";
    echo $row["recip_id"] . "\n";  
    echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
    $rs1_CurGroupID = $row["recip_id"];  	// set $rs1_CurGroupID to current recip_id  
  
   }elseif($rs1_curRow == 0){ 
    // first group - so print title
    echo "<DIV style='page-break-after:always;'>\n";
    echo $row["recip_id"] . "\n";   
    echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
   }
  
   echo $row["expend_id"] . "<BR />\n";
   $rs1_curRow++; 				// increment current row number
  }
 }
}  

?>

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
cgustaf

ASKER
Thank's for the update.  The table is currently empty, and I actually suspected that might be the reason for the error.  I'll test your results and let you know.
cgustaf

ASKER
I have tested the code, and it produces the results higlighted in the attached screenshot of the report.
It shows the kind of separation I'd like to achieve.  In other words, the output (echoing) of lines 49-66 in the attached code produces:

recip_id 2
       expend_id  64
       expend_id  65

It also shows on the same page

recip_id 1
       expend_id  66

Two observations:

1.  Each recip_id (with its listing of expend_id(s)) should be output (echoed) to a separate page.

2.  The data output for each recip_id should be displayed within the report, and should contain these fields:
      rs1.recipnt
      rs1.exp_ref
      rs1.designtn
      rs1.paid_date
      rs1.exp_amt
             and a grand total of all exp_amt(s) for the recip_id should be displayed
             -- probably must  be generated by a separate query.

The code for the entire report is attached, and a screenshot of the report indicating the current echoing of lines 49-66 is also attached.

Can the echoing of the recip_id(s) to separate pages, and the echoing of the appropriate fields on the report be achieved?

 
<?php require_once('../../Connections/ibt_dbc.php'); ?>
<?php
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang      ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix      ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr     ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend     ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip     ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_num_rows = mysql_num_rows($rs1);		// row count
if ($rs1_num_rows > 0){				// only work with non-empty results
 
 $rs1_row1 = mysql_fetch_assoc($rs1); 		// extract first row
 $rs1_curGroupID = $rs1_row1["recip_id"]; 	// set $rs1_curGroupID to first row recip_id
 						
 if (!mysql_data_seek($rs1, 0)) {		// reset cursor to row 0 after previous data fetch via seek 0
   // could not seek
 }else{						// non-empty set
  $rs1_curRow = 0; 				// current row index
  
  while ($row = mysql_fetch_assoc($rs1)) {
  
   if($row["recip_id"] <> $rs1_CurGroupID){ 	
    // different group - co close previous divs and print title
    echo " </DIV>\n";
    echo "</DIV><BR />\n\n";
    echo "<DIV style='page-break-after:always;'>\n";
    echo $row["recip_id"] . "\n";  
    echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
    $rs1_CurGroupID = $row["recip_id"];  	// set $rs1_CurGroupID to current recip_id  
  
   }elseif($rs1_curRow == 0){ 
    // first group - so print title
    echo "<DIV style='page-break-after:always;'>\n";
    echo $row["recip_id"] . "\n";   
    echo " <DIV style='margin-left: 20px; padding-left: 10px;'>\n";
   }
  
   echo $row["expend_id"] . "<BR />\n";
   $rs1_curRow++; 				// increment current row number
  }
 }
}  

?> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Disbursement Report</title>
<link href="../../css/thin_border.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
body,td,th {
	font-family: Geneva, Arial, Helvetica, sans-serif;
	font-size: 12px;
}
.style1 {
	font-size: 14px;
	font-weight: bold;
}
body {
	background-color: #E9FCEA;
	margin-left: .5in;
	margin-top: .5in;
	margin-right: .5in;
	margin-bottom: .5in;
}
-->
</style>
</head>

<body>
<table width="700" align="left" class="thin_border">
  <tr>
    <td valign="top"><table width="700" align="center">
      <tr>
        <td colspan="3" valign="top"><div align="left"><a href="../../switch.php">Home</a> <a href="javascript:window.print()">Print</a></div></td>
      </tr>
      <tr>
        <td colspan="3" align="center" valign="top"><span class="style1">Disbursement Report</span></td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top"><strong>IBT North America </strong></td>
      </tr>
      <tr>
        <td colspan="3" valign="top"><strong>Disbursement Report</strong></td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top"><form name="form1" method="POST">
          <table width="700" align="center">
            <tr>
              <td width="136" valign="top"><div align="right">Recipient:</div></td>
              <td width="17" valign="top">&nbsp;</td>
              <td colspan="4" valign="top"><?php echo $row_rs1['recipnt']; ?></td>
            </tr>
            <tr>
              <td valign="top"><div align="right">Transaction Reference:</div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top"><?php echo $row_rs1['exp_ref']; ?></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top"><div align="right">Expenditures:</div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><strong>Designations</strong></td>
              <td valign="top"><div align="right"><strong>Date</strong></div></td>
              <td valign="top"><div align="right"><strong>Amount</strong></div></td>
            </tr>
            <tr>
              <td height="27" valign="top"><div align="right"></div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><?php echo $row_rs1['designtn']; ?></td>
              <td width="87" valign="top"><div align="right"><?php echo $row_rs1['paid_date']; ?></div></td>
              <td width="97" valign="top"><div align="right">$<?php echo number_format($row_rs1['exp_amt'],2); ?></div></td>
            </tr>
<tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><div align="right"><strong>TOTAL EXPENDITURE: </strong></div></td>
              <td colspan="2" valign="top"><div align="right"><strong>$<?php echo number_format($row_rs1['exp_amt'],2); ?></strong></div></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Authorized:</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Curt L. Gustafsson </td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="3" valign="top"><hr noshade></td>
              <td width="283" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top"><input name="recip_id" type="hidden" id="recip_id" value="<?php echo $row_rs1['recip_id']; ?>"></td>
              <td colspan="2" valign="middle"><div align="right"></div></td>
              <td colspan="2" valign="bottom"><div align="right">              </div></td>
            </tr>
          </table>
          
            
            
        </form></td>
      </tr>
      <tr>
        <td width="157" valign="top"><div align="right"></div></td>
        <td width="14" valign="top">&nbsp;</td>
        <td width="513" valign="top">&nbsp;</td>
      </tr>
    </table></td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($rs1);
?>

Open in new window

  Screenshot of report
NerdsOfTech

Of course it can. In fact, the report is currently on separate pages (check your print preview).

The other data can be easily be outputted as well.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NerdsOfTech

The additional information for each recip_id is going to change things around in the code though.
Let me know how you want that info for each recip_id to look like and where it should be positioned... on top (easy)? or on the bottom (hard) of each group?

Do you understand what was done to output on separate pages (print page breaks)?

What did what you expected? If not, let me know what you would need.
cgustaf

ASKER
Yes, I see the two pages.  But since the output is not yet part of the report layout, that layout (as seen in the screenshot) only appears on one of the two pages.
cgustaf

ASKER
OK.  Thanks for the second message just now.
I will produce another screenshot for you which shows the layout with some data on it.  This shot will not utilize the code you provided, but will show data on the report in the positions it should be.

The page separation occurs in lines 49-66 in the code I sent you, but exactly how it happens is not clear to me yet.  There is intricate code there that I need time to study.

Stand by for the screenshot.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
cgustaf

ASKER
I attach two screenshots --  #1 shows the the locations of the data fields on the report, and #2 is a sample of what the report should look like as it displays the data for one recip_id.
Shows data output locations on report  Sample of report for one recip_id
NerdsOfTech

This might require a "hire me" request as it may require confidential code viewing.

I think this should be straightfoward if I see the disbursement report code.

=NerdsOfTech
cgustaf

ASKER
Could you explain "hire me?"  Have not encountered that term yet on EE.

The lay-out code is basically as in 36421537, with the exception that lines 49-66 should be rearranged to generate the intended output.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NerdsOfTech

The "hire me" service on EE allows the asker to hire an expert via the experts profile. This is a way to hire an expert to complete a solution where it is more difficult than what points would warrant as a reward or has asker specific customizations that are beyond the scope of the question, as in this case.

In 36421537, lines 100-218 is the HTML table code that needs to be repeated; thus, you would need to integrate that particular HTML block into the PHP Loop and output the "variable" information using data from the database.

The primary question was answered regarding "how" the page break(s) in the data can be completed. However, I would like very much so to customize the output for you. Let me know if I can assist in the formatting of the custom page output by hiring me to complete this asker specific customization.

Here is my profile if you are interested in hiring me for this task:
https://www.experts-exchange.com/M_4141693.html

This should take no longer than 30 minutes of my time to complete.

=NerdsOfTech



cgustaf

ASKER
Sounds good.  I wonder, though, if you can explain "This might require a "hire me" request as it may require confidential code viewing" before I move forward.  In other words, I'd like to understand your point about "confidential code viewing."
NerdsOfTech

In summary, I was concerned that the customization steps required me to review confidential code, such as proprietary code, and I wanted to caution you from posting such code on this public forum. Instead it is recommended to setup a "hire me" to provide protection of privacy for such code, if any such code exists.

Let me know when you would like to start.








All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
NerdsOfTech

This customization solution will take me approximately 30 minutes of code time. I charge $100 USD per hour, thus, I will only charge you a flat rate of $50 USD.

Please click on my profile to "hire me" through experts-exchange.

This will be very easy for me to complete for you.
NerdsOfTech

Thank you.
This is the full solution. Please test. Let me know if you get any errors or anomalies.
<?php require_once('../../Connections/ibt_dbc.php'); ?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Disbursement Report</title>
<link href="../../css/thin_border.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
body,td,th {
	font-family: Geneva, Arial, Helvetica, sans-serif;
	font-size: 12px;
}
.style1 {
	font-size: 14px;
	font-weight: bold;
}
body {
	background-color: #E9FCEA;
	margin-left: .5in;
	margin-top: .5in;
	margin-right: .5in;
	margin-bottom: .5in;
}
-->
</style>
</head>
<body>

<?php
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang	ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix	ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr	ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend	ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip	ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_num_rows = mysql_num_rows($rs1);		// row count
if ($rs1_num_rows > 0){				// only output report on non-empty results
 
 $rs1_curGroupID = ""; 				// set $rs1_curGroupID to empty string
 $rs1_curRow = 0; 				// current row index
 $g_curRow = 0;					// current group row index
 $g_total = 0;					// running total for group
  
 while ($row = mysql_fetch_assoc($rs1)) {
 
  if($row["recip_id"] != $rs1_CurGroupID){ 	// new group = new report
   $rs1_CurGroupID = $row["recip_id"];  	// set $rs1_CurGroupID to current recip_id
   $g_curRow = 0;				// reset group row number index to 0
   if($rs1_curRow != 0){			// print footer of previous group, if not first record
?>
	    <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><div align="right"><strong>TOTAL EXPENDITURE: </strong></div></td>
              <td colspan="2" valign="top"><div align="right"><strong>$<?php echo number_format($g_total,2); ?></strong></div></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Authorized:</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Curt L. Gustafsson </td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="3" valign="top"><hr noshade></td>
              <td width="283" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top"></td>
              <td colspan="2" valign="middle"><div align="right"></div></td>
              <td colspan="2" valign="bottom"><div align="right"></div></td>
            </tr>
          </table>
        </td>
      </tr>
      <tr>
        <td width="157" valign="top"><div align="right"></div></td>
        <td width="14" valign="top">&nbsp;</td>
        <td width="513" valign="top">&nbsp;</td>
      </tr>
    </table></td>
  </tr>
</table>
</DIV>


<?php
   }						// end if first row check
   $g_total = 0;				// reset running total for new group to zero
						// print new report page header
?>

<DIV style='page-break-after:always;'>
<table width="700" align="left" class="thin_border">
  <tr>
    <td valign="top"><table width="700" align="center">
      <tr>
        <td colspan="3" valign="top"><div align="left"><a href="../../switch.php">Home</a> <a href="javascript:window.print()">Print</a></div></td>
      </tr>
      <tr>
        <td colspan="3" align="center" valign="top"><span class="style1">Disbursement Report</span></td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top"><strong>IBT North America </strong></td>
      </tr>
      <tr>
        <td colspan="3" valign="top"><strong>Disbursement Report</strong></td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">
          <table width="700" align="center">
            <tr>
              <td width="136" valign="top"><div align="right">Recipient:</div></td>
              <td width="17" valign="top">&nbsp;</td>
              <td colspan="4" valign="top"><?php echo $row['recipnt']; ?></td>
            </tr>
            <tr>
              <td valign="top"><div align="right">Transaction Reference:</div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top"><?php echo $row['exp_ref']; ?></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top"><div align="right">Expenditures:</div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><strong>Designations</strong></td>
              <td valign="top"><div align="right"><strong>Date</strong></div></td>
              <td valign="top"><div align="right"><strong>Amount</strong></div></td>
            </tr>
<?php 
  }						// end if new row check
						// print expenditures row(s)
?>

            <tr>
              <td height="27" valign="top"><div align="right"></div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><?php echo $row['designtn']; ?></td>
              <td width="87" valign="top"><div align="right"><?php echo $row['paid_date']; ?></div></td>
              <td width="97" valign="top"><div align="right">$<?php echo number_format($row['exp_amt'],2); ?></div></td>
            </tr>

<?php   
  $rs1_curRow++; 				// increment current row number
  $g_curRow++; 					// increment current group row number
  $g_total = $g_total + $row['exp_amt'];	// add this exp amount to running group total
 }						// end while loop
						// output last footer
?>

	    <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><div align="right"><strong>TOTAL EXPENDITURE: </strong></div></td>
              <td colspan="2" valign="top"><div align="right"><strong>$<?php echo number_format($g_total,2); ?></strong></div></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Authorized:</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Curt L. Gustafsson </td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="3" valign="top"><hr noshade></td>
              <td width="283" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top"></td>
              <td colspan="2" valign="middle"><div align="right"></div></td>
              <td colspan="2" valign="bottom"><div align="right"></div></td>
            </tr>
          </table>
        </td>
      </tr>
      <tr>
        <td width="157" valign="top"><div align="right"></div></td>
        <td width="14" valign="top">&nbsp;</td>
        <td width="513" valign="top">&nbsp;</td>
      </tr>
    </table></td>
  </tr>
</table>
</DIV>
<?php
}  						// end if of data exists check
?>

</body>
</html>
<?php
mysql_free_result($rs1);
?>

Open in new window

cgustaf

ASKER
Thank you.  It works, but there is one issue that I'd ask you to look at.

I attach a screen shot of the current output.  It shows 3 pages, thus 3 receipients.  The issue is a matter of formating.  You see that the top margin on the first page is lower than the top margin on pages 2 and 3.  Report output -- 3 pages
The wider top margin on page 1 is better, and if you can make the the top margins on pages 2 and 3, that is top margins on all pages subsequent to page 1, the same as page 1 this would be better.

Other than this, the report seems to work as intended.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
NerdsOfTech

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cgustaf

ASKER
Can you put this

<img src="../../pics/CGSIGN.jpg" width="148" height="36">

So it shows up on all pages as the signature in the attached pdf?  don-disb-rcrd-new.pdf
Replacement-File--Q-27242017.JPG
cgustaf

ASKER
Thanks for fixing the top margin so it replicates on all pages.  Looks good.

Regarding the sig.jpg, I think we will forget about this idea.  The reports will usually be printed as PDFs, and there are better and more secure ways to sign such pages.
NerdsOfTech

Thank you.

Would you like me to ask a mod to remove that attachment on ID # 36477267 for you since it has a signature?

=NerdsOfTech
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
cgustaf

ASKER
Yes, plesae ask the moderator to rermove ID # 36477267  
NerdsOfTech

Completed the send request for mod to remove that message --- awaiting mod to fix.
cgustaf

ASKER
OK.  I'll watch for it.  The we are ready to finalize.  Do you need to send an additional payment request, or can I use the previous one?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
NerdsOfTech

Suggested Solution ID # 36477258
NerdsOfTech

Lets try this again :)
Let me see if a remember the short hand for this

http:#36477258
cgustaf

ASKER
Thank you.  I have made the payment, and am now awarding the 500 points. (5596-4279-5157-9253)

I appreciate your accuracy and speed!

Curt L. Gustafsson
Your help has saved me hundreds of hours of internet surfing.
fblack61
cgustaf

ASKER
Nicely done, and also quickly and responsively.
NerdsOfTech

Implemented the requested message if the recordset is blank.
<?php require_once('../../Connections/ibt_dbc.php'); ?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Disbursement Report</title>
<link href="../../css/thin_border.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
body,td,th {
	font-family: Geneva, Arial, Helvetica, sans-serif;
	font-size: 12px;
}
.style1 {
	font-size: 14px;
	font-weight: bold;
}
body {
	background-color: #E9FCEA;
	margin-left: .5in;
	margin-top: 0;
	margin-right: .5in;
	margin-bottom: 0;
	padding: 0;
}
.titlediv {
	margin-top: .5in;
	page-break-after:always;
}
-->
</style>
</head>
<body>

<?php
mysql_select_db($database_ibt_dbc, $ibt_dbc);
$query_rs1 = "
SELECT 
t_desig.prefix_id, 
t_desig.lang_id, 
t_desig.postscr_id , 
CONCAT_WS(' ',t_prefix.prefix,t_lang.lang,t_postscr.postscr) designtn, 
t_desig.desig_id, 
t_recip.recip_id,
t_recip.recipnt,
t_expend.paid_date, 
t_expend.exp_ref,
t_expend.exp_done, 
t_expend.expend_id, 
t_expend.exp_amt 
FROM 
t_desig  
LEFT JOIN t_lang	ON t_desig.lang_id = t_lang.lang_id   
LEFT JOIN t_prefix	ON t_desig.prefix_id = t_prefix.prefix_id    
LEFT JOIN t_postscr	ON t_desig.postscr_id = t_postscr.postscr_id  
LEFT JOIN t_expend	ON t_desig.desig_id = t_expend.desig_id  
LEFT JOIN t_recip	ON t_recip.recip_id = t_expend.recip_id 
WHERE 
t_desig.desig_id = t_expend.desig_id 
AND t_expend.recip_id = t_recip.recip_id 
AND t_expend.exp_done = 'Y' 
AND t_expend.paid_date = CURDATE() 
ORDER BY  
t_expend.recip_id, 
t_desig.desig_id ASC;
";

$rs1 = mysql_query($query_rs1, $ibt_dbc) or die(mysql_error());
$rs1_num_rows = mysql_num_rows($rs1);		// row count
if ($rs1_num_rows > 0){				// only output report on non-empty results
 
 $rs1_curGroupID = ""; 				// set $rs1_curGroupID to empty string
 $rs1_curRow = 0; 				// current row index
 $g_curRow = 0;					// current group row index
 $g_total = 0;					// running total for group
  
 while ($row = mysql_fetch_assoc($rs1)) {
 
  if($row["recip_id"] != $rs1_CurGroupID){ 	// new group = new report
   $rs1_CurGroupID = $row["recip_id"];  	// set $rs1_CurGroupID to current recip_id
   $g_curRow = 0;				// reset group row number index to 0
   if($rs1_curRow != 0){			// print footer of previous group, if not first record
?>
	    <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><div align="right"><strong>TOTAL EXPENDITURE: </strong></div></td>
              <td colspan="2" valign="top"><div align="right"><strong>$<?php echo number_format($g_total,2); ?></strong></div></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Authorized:</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Curt L. Gustafsson </td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="3" valign="top"><hr noshade></td>
              <td width="283" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top"></td>
              <td colspan="2" valign="middle"><div align="right"></div></td>
              <td colspan="2" valign="bottom"><div align="right"></div></td>
            </tr>
          </table>
        </td>
      </tr>
      <tr>
        <td width="157" valign="top"><div align="right"></div></td>
        <td width="14" valign="top">&nbsp;</td>
        <td width="513" valign="top">&nbsp;</td>
      </tr>
    </table></td>
  </tr>
</table>
</DIV>


<?php
   }						// end if first row check
   $g_total = 0;				// reset running total for new group to zero
						// print new report page header
?>

<DIV class="titlediv">
<table width="700" align="left" class="thin_border">
  <tr>
    <td valign="top"><table width="700" align="center">
      <tr>
        <td colspan="3" valign="top"><div align="left"><a href="../../switch.php">Home</a> <a href="javascript:window.print()">Print</a></div></td>
      </tr>
      <tr>
        <td colspan="3" align="center" valign="top"><span class="style1">Disbursement Report</span></td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top"><strong>IBT North America </strong></td>
      </tr>
      <tr>
        <td colspan="3" valign="top"><strong>Disbursement Report</strong></td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td colspan="3" valign="top">
          <table width="700" align="center">
            <tr>
              <td width="136" valign="top"><div align="right">Recipient:</div></td>
              <td width="17" valign="top">&nbsp;</td>
              <td colspan="4" valign="top"><?php echo $row['recipnt']; ?></td>
            </tr>
            <tr>
              <td valign="top"><div align="right">Transaction Reference:</div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top"><?php echo $row['exp_ref']; ?></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top"><div align="right">Expenditures:</div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><strong>Designations</strong></td>
              <td valign="top"><div align="right"><strong>Date</strong></div></td>
              <td valign="top"><div align="right"><strong>Amount</strong></div></td>
            </tr>
<?php 
  }						// end if new row check
						// print expenditures row(s)
?>

            <tr>
              <td height="27" valign="top"><div align="right"></div></td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><?php echo $row['designtn']; ?></td>
              <td width="87" valign="top"><div align="right"><?php echo $row['paid_date']; ?></div></td>
              <td width="97" valign="top"><div align="right">$<?php echo number_format($row['exp_amt'],2); ?></div></td>
            </tr>

<?php   
  $rs1_curRow++; 				// increment current row number
  $g_curRow++; 					// increment current group row number
  $g_total = $g_total + $row['exp_amt'];	// add this exp amount to running group total
 }						// end while loop
						// output last footer
?>

	    <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="2" valign="top"><div align="right"><strong>TOTAL EXPENDITURE: </strong></div></td>
              <td colspan="2" valign="top"><div align="right"><strong>$<?php echo number_format($g_total,2); ?></strong></div></td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Authorized:</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">Curt L. Gustafsson </td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td valign="top">&nbsp;</td>
              <td valign="top">&nbsp;</td>
              <td colspan="4" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="3" valign="top"><hr noshade></td>
              <td width="283" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
              <td colspan="2" valign="top">&nbsp;</td>
            </tr>
            <tr>
              <td colspan="2" valign="top"></td>
              <td colspan="2" valign="middle"><div align="right"></div></td>
              <td colspan="2" valign="bottom"><div align="right"></div></td>
            </tr>
          </table>
        </td>
      </tr>
      <tr>
        <td width="157" valign="top"><div align="right"></div></td>
        <td width="14" valign="top">&nbsp;</td>
        <td width="513" valign="top">&nbsp;</td>
      </tr>
    </table></td>
  </tr>
</table>
</DIV>
<?php
}  						// end if of data exists check
else{						// blank recordset output message
?>

<BR />
<DIV ALIGN="center">No expenditure has been finalized yet</DIV><BR />
<DIV ALIGN="center"><A HREF="../../switch.php">Home</A></DIV>

<?php
}						// end else (blank recordset) area
?>

</body>
</html>
<?php
mysql_free_result($rs1);
?>

Open in new window

cgustaf

ASKER
Thanks for fixing this.  The message and Home button are visible when the recordset is empty.

However, when the recordset it not empty, the "empty message" and Home button still show up.  In other words, we have lost the GoTo when the record is not empty.

Is the problem possibly here (line 70 in the code above):

if ($rs1_num_rows > 0){      // only output report on non-empty results
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cgustaf

ASKER
The moderator has not yet removed 36477267
cgustaf

ASKER
JustAMod --  Yes, this is acceptable.  Thanks.
cgustaf

ASKER
Jay -- I apologize!  The error which I reported to you, was an error on my side.  It related to the the wrong URL designated as the GoTo on the page that calls the distribution report.  I fixed this, and everything works as anticipated now -- so it seems!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
NerdsOfTech

Excellent. I am glad to be of service to you. Best regards