How to split rows and redisplay header after each 20 rows?
Posted: Sat Oct 25, 2008 3:27 am
I made a script that exports MYSQL database data to variables and display them with the php file. The list is very long and would like to be able to specify how many rows to split the rows and redisplay the header and then continue with the next rows. How would I be able to accomplish this? For example: I want to display 20 rows and then redisplay the header then display another 20 rows and repeat.
Here is a sample (edited for security) of my script.
Here is a sample (edited for security) of my script.
Code: Select all
<?php
// --------------- Connecting to Database
// create connection
$conn = mysql_connect("localhost","PRIVATE","PRIVATE")
or die(mysql_error());
// select database
$db = mysql_select_db("PRIVATE", $conn) or die(mysql_error());
// create SQL
$sql = "SELECT * FROM user_purchases ORDER BY PURCHASE_ID";
//create count variable
$countTotal_qty = '0';
$firstgen_v_one_total_qty = '0';
$firstgen_v_two_total_qty = '0';
$secondgen_total_qty = '0';
$thirdgen_v_one_total_qty = '0';
$thirdgen_v_two_total_qty = '0';
$fourthgen_v_one_total_qty = '0';
$fourthgen_v_two_total_qty = '0';
$mixedgen_total_qty = '0';
$total_purchases_total_qty = '0';
$total_shipping_total = '0';
$total_subtotal_total = '0';
$final_total_total = '0';
$total_final_final_total = '0';
$final_subtotal_total = '0';
// Variables for check marks
$CHECK_MARK_GREEN = "<img border=0 src=http://camarosource.ca/2009_calendars/check_green.gif width=20 height=20>";
$CHECK_MARK_RED = "<img border=0 src=http://camarosource.ca/php/frides/check.gif width=20 height=20>";
$UNCHECK = "<img border=0 src=http://camarosource.ca/2009_calendars/uncheck.gif width=20 height=20>";
$CAN_STD_CHECK = " "; // Added so the EMPTY CELL Displays
$CAN_XPR_CHECK = " "; // Added so the EMPTY CELL Displays
$USA_STD_CHECK = " "; // Added so the EMPTY CELL Displays
$USA_XPR_CHECK = " "; // Added so the EMPTY CELL Displays
$INT_STD_CHECK = " "; // Added so the EMPTY CELL Displays
$INT_XPR_CHECK = " "; // Added so the EMPTY CELL Displays
//Execute SQL query
$sql_result = mysql_query($sql,$conn) or die(mysql_error());
// start rpo code list formatting
include("http://www.camarosource.ca/2009_calendars/admin_purchase_list_header.htm");
// Format list by row
while ($row = mysql_fetch_array($sql_result)) {
$ID = $row["PURCHASE_ID"];
$NAME = $row["FULL_NAME"];
$COUNTRY = $row["COUNTRY"];
$FIRST_GEN_QTY_VER_ONE = $row["FIRST_GEN_QTY_VER_1"];
$FIRST_GEN_QTY_VER_TWO = $row["FIRST_GEN_QTY_VER_2"];
$SECOND_GEN_QTY = $row["SECOND_GEN_QTY"];
$THIRD_GEN_QTY_VER_ONE = $row["THIRD_GEN_QTY_VER_ONE"];
$THIRD_GEN_QTY_VER_TWO = $row["THIRD_GEN_QTY_VER_TWO"];
$FOURTH_GEN_QTY_VER_ONE = $row["FOURTH_GEN_QTY_VER_ONE"];
$FOURTH_GEN_QTY_VER_TWO = $row["FOURTH_GEN_QTY_VER_TWO"];
$MIXED_GEN_QTY = $row["MIXED_GEN_QTY"];
$SHIPPING = $row["SHIPPING"];
$TOTAL_SHIPPING = $row["TOTAL_SHIPPING"];
$SUBTOTAL = $row["SUBTOTAL"];
$FINAL_TOTAL = $row["FINAL_TOTAL"];
$PAYMENT_TYPE = $row["PAYMENT_TYPE"];
$DATE_PAID_CALENDARS = $row["DATE_PAID_CALENDARS"];
$DATE_PAID_SHIPPING = $row["DATE_PAID_SHIPPING"];
$DATE_EMAILED_WAITING_FOR_PAYMENT = $row["DATE_EMAILED_WAITING_FOR_PAYMENT"];
$DATE_MO_CASH_MAILED_OUT = $row["DATE_MO_CASH_MAILED_OUT"];
$DATE_MO_CASH_RECEIVED = $row["DATE_MO_CASH_RECEIVED"];
$DATE_MAILED_PACKAGE = $row["DATE_MAILED_PACKAGE"];
$DATE_MO_CASH_EMAIL_SENT = $row["DATE_MO_CASH_EMAIL_SENT"];
$SAID_PURCHASE_QTY = $row["SAID_PURCHASE_QTY"];
$ADMIN_COMMENTS = $row["ADMIN_COMMENTS"];
$CALENDAR_QUANTITY = $FIRST_GEN_QTY_VER_ONE + $FIRST_GEN_QTY_VER_TWO + $SECOND_GEN_QTY + $THIRD_GEN_QTY_VER_ONE + $THIRD_GEN_QTY_VER_TWO + $FOURTH_GEN_QTY_VER_ONE + $FOURTH_GEN_QTY_VER_TWO + $MIXED_GEN_QTY;
if ($SHIPPING == "can_std") {
$SHIPPING_TYPE = "CA - STD";
} elseif ($SHIPPING == "can_xpr") {
$SHIPPING_TYPE = "CA - XP";
} elseif ($SHIPPING == "usa_std") {
$SHIPPING_TYPE = "US - STD";
} elseif ($SHIPPING == "usa_xpr") {
$SHIPPING_TYPE = "US - XP";
} elseif ($SHIPPING == "int_std") {
$SHIPPING_TYPE = "INT - STD";
} elseif ($SHIPPING == "int_xpr") {
$SHIPPING_TYPE = "INT - XP";
}
if ($DATE_PAID_CALENDARS == "") {
$PAID_CALENDARS = $UNCHECK;
} else {
$PAID_CALENDARS = $CHECK_MARK_GREEN;
}
if ($DATE_PAID_SHIPPING == "") {
$PAID_SHIPPING = $UNCHECK;
} else {
$PAID_SHIPPING = $CHECK_MARK_GREEN;
}
if ($DATE_EMAILED_WAITING_FOR_PAYMENT == "") {
$EMAILED_WAITING_FOR_PAYMENT = $UNCHECK;
} else {
$EMAILED_WAITING_FOR_PAYMENT = $CHECK_MARK_GREEN;
}
if ($DATE_MO_CASH_MAILED_OUT == "") {
$MO_CASH_SENT_OUT = $UNCHECK;
} else {
$MO_CASH_SENT_OUT = $CHECK_MARK_GREEN;
}
if ($DATE_MO_CASH_RECEIVED == "") {
$MO_CASH_RECEIVED = $UNCHECK;
} else {
$MO_CASH_RECEIVED = $CHECK_MARK_GREEN;
}
if ($DATE_MAILED_PACKAGE == "") {
$MAILED_CALENDARS = $UNCHECK;
} else {
$MAILED_CALENDARS = $CHECK_MARK_GREEN;
}
if ($DATE_MO_CASH_EMAIL_SENT == "") {
$MO_CASH_EMAIL_SENT = $UNCHECK;
} else {
$MO_CASH_EMAIL_SENT = $CHECK_MARK_GREEN;
}
if ($PAYMENT_TYPE == "paypal") {
$PAYMENT_TYPE = "<img border=0 src=https://www.paypalobjects.com/en_US/i/logo/PayPal_mark_60x38.gif>";
// $FINAL_TOTAL = ($FINAL_TOTAL + $CALENDAR_QUANTITY);
} elseif ($PAID == "moneyorder") {
$PAYMENT_TYPE = " ";
}
//
$FINAL_FINAL_TOTAL = ($FINAL_TOTAL + $TOTAL_SHIPPING);
//add the quantity to for each row total
$countTotal_qty = $countTotal_qty + $CALENDAR_QUANTITY;
$firstgen_v_one_total_qty = $firstgen_v_one_total_qty + $FIRST_GEN_QTY_VER_ONE;
$firstgen_v_two_total_qty = $firstgen_v_two_total_qty + $FIRST_GEN_QTY_VER_TWO;
$secondgen_total_qty = $secondgen_total_qty + $SECOND_GEN_QTY;
$thirdgen_v_one_total_qty = $thirdgen_v_one_total_qty + $THIRD_GEN_QTY_VER_ONE;
$thirdgen_v_two_total_qty = $thirdgen_v_two_total_qty + $THIRD_GEN_QTY_VER_TWO;
$fourthgen_v_one_total_qty = $fourthgen_v_one_total_qty + $FOURTH_GEN_QTY_VER_ONE;
$fourthgen_v_two_total_qty = $fourthgen_v_two_total_qty + $FOURTH_GEN_QTY_VER_TWO;
$mixedgen_total_qty = $mixedgen_total_qty + $MIXED_GEN_QTY;
$total_purchases_total_qty = $total_purchases_total_qty + 1;
$total_shipping_total = ($total_shipping_total + $TOTAL_SHIPPING);
$final_total_total = ($final_total_total + $FINAL_TOTAL);
$final_subtotal_total = ($final_subtotal_total + $SUBTOTAL);
$total_final_final_total = ($total_final_final_total + $FINAL_FINAL_TOTAL);
$total_paypal_fees = ($final_total_total - $final_subtotal_total);
// Getting results of Calendar Quantity
// Start Rows
echo "<tr>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$ID</font></b></td>
<b><font color=#FF0000>
<td bgcolor=#EEEEEE align=center height=21>
$MAILED_CALENDARS</td>
<td bgcolor=#EEEEEE align=center height=21>
$RECEIVED_CALENDARS</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial><a href=LINK>
$NAME</a></font></b></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$COUNTRY</font></b></td>
<b><font color=#FF0000>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
$SAID_PURCHASE_QTY</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=4 color=#FF0000>$CALENDAR_QUANTITY</font></b></font></b></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<font size=1 face=Arial>$FIRST_GEN_QTY_VER_ONE</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<font size=1 face=Arial>$FIRST_GEN_QTY_VER_TWO</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$SECOND_GEN_QTY</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$THIRD_GEN_QTY_VER_ONE</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$THIRD_GEN_QTY_VER_TWO</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$FOURTH_GEN_QTY_VER_ONE</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$FOURTH_GEN_QTY_VER_TWO</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$MIXED_GEN_QTY</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font color=#FF0000>
$SUBTOTAL</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font color=#FF0000>
$FINAL_TOTAL</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font size=1 face=Arial>$SHIPPING_TYPE</font></b></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font color=#FF0000>
<font size=1 face=Arial>$TOTAL_SHIPPING</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font color=#FF0000>
$FINAL_FINAL_TOTAL</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font color=#FF0000>
<font size=1 face=Arial>$PAYMENT_TYPE</font></td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font color=#FF0000>
$PAID_CALENDARS</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<b><font color=#FF0000>
$PAID_SHIPPING</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
$MO_CASH_EMAIL_SENT</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
$EMAILED_WAITING_FOR_PAYMENT</td>
<td bgcolor=#EEEEEE align=center height=21>
$MO_CASH_SENT_OUT</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
$MO_CASH_RECEIVED</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
</td>
<td bgcolor=#EEEEEE align=center height=35 rowspan=2>
<font size=1>$ADMIN_COMMENTS</font></td>
</tr>
<tr>
<td bgcolor=#EEEEEE align=center height=14>
<b>
<font size=1 color=#FF0000>
$DATE_MAILED_PACKAGE</td>
<td bgcolor=#EEEEEE align=center height=14>
<b>
<font size=1 color=#FF0000>
$DATE_CALENDARS_RECEIVED</td>
<td bgcolor=#EEEEEE align=center height=14>
<font size=1>$DATE_MO_CASH_MAILED_OUT</font></td>
</tr>";
unset($CAN_STD_CHECK);
unset($CAN_XPR_CHECK);
unset($USA_STD_CHECK);
unset($USA_XPR_CHECK);
unset($INT_STD_CHECK);
unset($INT_XPR_CHECK);
$CAN_STD_CHECK = " "; // Added so the EMPTY CELL Displays
$CAN_XPR_CHECK = " "; // Added so the EMPTY CELL Displays
$USA_STD_CHECK = " "; // Added so the EMPTY CELL Displays
$USA_XPR_CHECK = " "; // Added so the EMPTY CELL Displays
$INT_STD_CHECK = " "; // Added so the EMPTY CELL Displays
$INT_XPR_CHECK = " "; // Added so the EMPTY CELL Displays
}
echo "<tr>
<td bgcolor=#000080 align=center height=38 colspan=29>
<b><font color=#FFFF00 size=6>TOTALS</font></b></td>
</tr>
<tr>
<td bgcolor=#0000FF align=center height=102 rowspan=3 colspan=4>
<b><font color=#FF0000>
<font face=Arial size=1 color=#FFFF00>Total<br>
</font></font><font color=#FFFF00 face=Arial size=1>
Purchases</td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
<font face=Arial size=1 color=#FFFF00>
Country</font></td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
<font color=#00FF00><b>Said #<br>
Total</b></font></td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
<font face=Arial size=1 color=#FFFF00>
Purchase Quantity Total</font></td>
<td bgcolor=#0000FF align=center height=64 colspan=8>
<font color=#FFFF00 face=Arial size=1>Ordered<br>
(Qty.)</font></td>
<td bgcolor=#0000FF align=center height=101 rowspan=3>
<b><font color=#FF0000>
<font color=#FFFF00 size=1 face=Arial>SubTotal<br>
(before S&H)</font> </td>
<td bgcolor=#0000FF align=center height=77>
<font color=#FFFF00 size=1 face=Arial>Total Cost<br>
with PAYPAL fee</font></td>
<td bgcolor=#0000FF align=center height=101 rowspan=3>
</td>
<td bgcolor=#0000FF align=center height=80 rowspan=2>
<b>
<font color=#FFFF00 size=1 face=Arial>Shipping<br>
Cost</font></td>
<td bgcolor=#0000FF align=center height=80 rowspan=2>
</td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
<font color=#FFFF00 size=1 face=Arial>Payment<br>
Type</font></td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
<b><font color=#ffff00 size=2 face=Arial>Paid<br>
Cal<br>
Totals</font></b></td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
<b><font color=#ffff00 size=2 face=Arial>Paid<br>
S/H<br>
Totals</font></b></td>
<font color=#FF0000>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
</td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
</td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
</td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
</td>
<td bgcolor=#0000FF height=102 rowspan=3>
</td>
<td bgcolor=#0000FF align=center height=102 rowspan=3>
</td>
</tr>
<tr>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font color=#FFFF00 face=Arial size=1>1st</font></td>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font color=#FFFF00 face=Arial size=1>1st</font></td>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font color=#FFFF00 face=Arial size=1>2nd</font></td>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font color=#FFFF00 face=Arial size=1>3rd<br>
(ver1)</font></td>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font color=#FFFF00 face=Arial size=1>3rd<br>
(ver2)</font></td>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font color=#FFFF00 face=Arial size=1>4th<br>
(ver1)</font></td>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font color=#FFFF00 face=Arial size=1>4th<br>
(ver2)</font></td>
<td bgcolor=#0000FF align=center height=23 rowspan=2>
<font face=Arial size=1 color=#FFFF00>MIXED</font></td>
<td bgcolor=#0000FF align=center height=24 rowspan=2>
<font color=#FFFF00 size=1 face=Arial>TOTAL WORTH OF PAYPAL
FEE'S</font></td>
</tr>
<font color=#FF0000>
<font color=#FF0000>
<tr>
<td bgcolor=#0000FF align=center height=21>
<font color=#FFFFFF><b>Actual Cost</b></font></td>
<td bgcolor=#0000FF align=center height=21>
<font color=#FFFFFF><b>Actual Cost</b></font></td>
</tr>
<tr>
<td bgcolor=#EEEEEE align=center height=72 colspan=4 rowspan=3>
<b><font color=#FF0000>
$total_purchases_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$countTotal_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$firstgen_v_one_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$firstgen_v_two_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$secondgen_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$thirdgen_v_one_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$thirdgen_v_two_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$fourthgen_v_one_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$fourthgen_v_two_total_qty</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
$mixedgen_total_qty</td>
<td bgcolor=#EEEEEE align=center height=85 rowspan=3>
<b>
<font color=#FF0000>$$final_subtotal_total</font></td>
<td bgcolor=#EEEEEE align=center height=38>
<b><font color=#000080 size=4>$$final_total_total</font></td>
<td bgcolor=#EEEEEE align=center height=85 rowspan=3>
</td>
<td bgcolor=#EEEEEE align=center height=63 rowspan=3>
<b>
<font color=#FF0000>$$total_shipping_total</font></td>
<td bgcolor=#FF0000 align=center height=63 rowspan=3>
<b><font color=#FFFF00>
$total_final_final_total</td>
<td bgcolor=#EEEEEE align=center height=40>
<b><font color=#FF0000>
$ship_type_total_paypal</td>
<td bgcolor=#EEEEEE align=center height=40>
YES<br>
<b><font color=#FF0000>
$PAID_CALENDARS_TOTAL_YES</td>
<td bgcolor=#EEEEEE align=center height=40>
YES<br>
<b><font color=#FF0000>
$PAID_SHIPPING_TOTAL_YES</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
</td>
<td bgcolor=#EEEEEE height=72 rowspan=3>
</td>
<td bgcolor=#EEEEEE align=center height=72 rowspan=3>
</td>
</tr>
<tr>
<td bgcolor=#EEEEEE align=center height=62 rowspan=2>
$$total_paypal_fees</td>
<td bgcolor=#EEEEEE align=center height=28>
<b><font color=#FF0000>
$ship_type_total_MO</td>
<td bgcolor=#EEEEEE align=center height=64 rowspan=2>
NO<br>
<b><font color=#FF0000>
$PAID_CALENDARS_TOTAL_NO</td>
<td bgcolor=#EEEEEE align=center height=64 rowspan=2>
NO<br>
<b><font color=#FF0000>
$PAID_SHIPPING_TOTAL_NO</td>
</tr>
<tr>
<td bgcolor=#EEEEEE align=center height=36>
<b><font color=#FF0000>
$ship_type_total_cash</td>
</tr>
</TABLE>";
?>