nwalker78
asked on
looping through arrays issue
hi i am trying to format data retrieved from a MySQL table, however i cant seem to get it looping properly. the desired layout needed is something like fig-1
but ive tried sev4ral diffrent looping layouts fig-2 gives leas5t probs. 
the code for fig 2 is
but ive tried sev4ral diffrent looping layouts fig-2 gives leas5t probs. the code for fig 2 is
<?php
$bttot1= 0;
$lttot1= 0;
$tttot1= 0;
$nttot1= 0;
$ettot1= 0;
$mode = array();
$rota = 0;
$rotadetails = rdetail($rota);
function dbConnection()
{
$connection = mysql_connect('localhost', '########', '########') or die ($errorlist['1'] . mysql_error());
mysql_select_db('######', $connection) or die ($errorlist['2'] . mysql_error());
return $connection;
}
/**
* Turns MYSQL resource into array
* @param resource $result
* @return array: $res_array
*/
function db_result_to_array($result)
{
$res_array = array();
for ($count=0; $row = mysql_fetch_array($result); $count++)
{
$res_array[$count] = $row;
}
return $res_array;
}
function rdetail($id)
{
$rsdate = 1369612800;
$sectoday = 86400;
$days = $sectoday*13;
dbConnection();
$query = sprintf("SELECT
*
FROM
######
WHERE
######.FLD_EMPID = '%s'
AND
######.FLD_ROTRD > '%s'
AND
######.FLD_ROTRD < '%s'",
mysql_real_escape_string($id),
($rsdate-1),
($rsdate+$days+1));
$result = mysql_query($query);
$result = db_result_to_array($result);
return $result;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="includes/css/test.css" rel="stylesheet" type="text/css" /></head>
<table width=633 border=0 align="center" cellpadding=0 cellspacing=0 class="derder">
<tr>
<td colspan=7 style="height:4px; "></td>
</tr>
<tr align="center">
<td width="70" colspan=1 rowspan="2" class="brdr-bs">Week 1</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts brdr-ls">Breakfast</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts">Lunch</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts">Dinner</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts">Bed</td>
<td width="120" colspan=2 bgcolor="#DBE5F1" class="brdr-ts brdr-rs">Extra Time</td>
</tr>
<tr align="center">
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs brdr-ls">08:00-09:00</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">13:30-14:30</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">17:00-18:00</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">22:00-23:00</td>
<td width="60" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">Duration</td>
<td width="60" colspan=1 bgcolor="#DBE5F1" class="brdr-bs brdr-rs ">Start</td>
</tr>
<?php
foreach ($rotadetails as $rotadetail)
{
$bttot1 = $bttot1 + $rotadetail['FLD_ROTH1']; // Running total for week 1 Breakfast Calls
$lttot1 = $lttot1 + $rotadetail['FLD_ROTH2']; // Running total for week 1 Lunch Calls
$tttot1 = $tttot1 + $rotadetail['FLD_ROTH3']; // Running total for week 1 Dinner Calls
$nttot1 = $nttot1 + $rotadetail['FLD_ROTH4']; // Running total for week 1 Night Calls
$ettot1 = $ettot1 + $rotadetail['FLD_ROTH5T']; // Running total for week 1 Extra Calls
?>
<tr>
<td colspan=1 class="brdr-lrs"><?php echo date("l",$rotadetail['FLD_ROTRD']);?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH1']; ?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH2']; ?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH3']; ?></td>
<td colspan=1 bgcolor="#DBE5F1" class="fmt-text brdr-lrs"><?php echo $rotadetail['FLD_ROTH4']; ?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH5T']; ?></td>
<td class="brdr-rs"> </td>
</tr>
<?php
}
?>
<tr>
<td height="4" colspan="7" class="brdr-ts" style="height:2px; font-size: 2px;"> </td>
</tr>
<tr class="fmt-text">
<td colspan=1 class="fmt-texthdr">TOTAL</td>
<td colspan=1 class="brdr-bs brdr-ts brdr-ls"><?php echo $bttot1; ?></td>
<td colspan=1 class="brdr-bs brdr-ts"><?php echo $lttot1; ?></td>
<td colspan=1 class="brdr-bs brdr-ts"><?php echo $tttot1; ?></td>
<td colspan=1 bgcolor="#DBE5F1" class="brdr-bs brdr-ts brdr-lrs"><?php echo $nttot1; ?></td>
<td colspan=1 class="fmt-text brdr-rs brdr-bs brdr-ts"><?php echo $ettot1; ?></td>
<td colspan=1 class="fmt-text brdr-rs brdr-bs brdr-ts"></td>
</tr>
</table>
</body>
</html>
I don't see anywhere that there is a break between weeks. everything is being output in a single link. You either need to do a query for each wek and loop back up with footer generation in between, or us a value in the data to trigger generation of the break code in the loop.
Cd&
Cd&
Or maybe a GROUP clause, etc. Once we see the CREATE TABLE statement we may be able to help a little more. It does appear that there are some similarities to a calendar application and for that, this article may offer some good suggestions.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
ASKER
Ray_Paseur: code for table added, thanks for heads up with the depreciation of the MySQL functions. i will switch to this as soon as had good read up. at present its only accessible by me and dev system dont have access to internet.
COBOLdinosaur: ooops forgot to add the thhe code for that..... will add when back at pc.
DROP TABLE IF EXISTS `tbl_rota_det`;
CREATE TABLE `tbl_rota_det` (
`FLD_EMPID` int(8) NOT NULL,
`FLD_ROTAID` int(11) NOT NULL,
`FLD_ROTRD` int(11) NOT NULL,
`FLD_ROTH1` enum('0','1') DEFAULT '0',
`FLD_ROTH2` enum('0','1') DEFAULT '0',
`FLD_ROTH3` enum('0','1') DEFAULT '0',
`FLD_ROTH4` enum('0','1') DEFAULT '0',
`FLD_ROTH5` varchar(5) DEFAULT '00:00',
`FLD_ROTH5T` int(2) DEFAULT '0',
`FLD_ROTH5R` enum('1','2','3') DEFAULT '1',
`FLD_ROTH5D` varchar(200) DEFAULT 'Nothing'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;COBOLdinosaur: ooops forgot to add the thhe code for that..... will add when back at pc.
ASKER
the code i have listed fot my loops gave the result seen in fig-3
to keep things tidier in future posts the css formatting will be removed.
im pretty sure the problem lies in the foreach loop and how i iterate through but thus far nothing has come close to working.
any hlp much appreciated.
<?php
for ($i= 1; $i <= 2; $i++) {
?>
<table width=633 border=0 align="center" cellpadding=0 cellspacing=0 class="derder">
<tr align="center">
<td width="70" colspan=1 rowspan="2" class="brdr-bs">Week <?php echo $i; ?></td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts brdr-ls">Breakfast</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts">Lunch</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts">Dinner</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-ts">Bed</td>
<td width="120" colspan=2 bgcolor="#DBE5F1" class="brdr-ts brdr-rs">Extra Time</td>
</tr>
<tr align="center">
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs brdr-ls">08:00-09:00</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">13:30-14:30</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">17:00-18:00</td>
<td width="80" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">22:00-23:00</td>
<td width="60" colspan=1 bgcolor="#DBE5F1" class="brdr-bs">Duration</td>
<td width="60" colspan=1 bgcolor="#DBE5F1" class="brdr-bs brdr-rs ">Start</td>
</tr>
<?php
foreach ($rotadetails as $rotadetail)
{
$bttot1 = $bttot1 + $rotadetail['FLD_ROTH1']; // Running total for week 1 Breakfast Calls
$lttot1 = $lttot1 + $rotadetail['FLD_ROTH2']; // Running total for week 1 Lunch Calls
$tttot1 = $tttot1 + $rotadetail['FLD_ROTH3']; // Running total for week 1 Dinner Calls
$nttot1 = $nttot1 + $rotadetail['FLD_ROTH4']; // Running total for week 1 Night Calls
$ettot1 = $ettot1 + $rotadetail['FLD_ROTH5T']; // Running total for week 1 Extra Calls
?>
<tr>
<td colspan=1 class="brdr-lrs"><?php echo date("l",$rotadetail['FLD_ROTRD']);?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH1']; ?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH2']; ?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH3']; ?></td>
<td colspan=1 bgcolor="#DBE5F1" class="fmt-text brdr-lrs"><?php echo $rotadetail['FLD_ROTH4']; ?></td>
<td colspan=1 class="fmt-text"><?php echo $rotadetail['FLD_ROTH5T']; ?></td>
<td class="brdr-rs"></td>
</tr>
<?php
}
?>
<tr>
<td height="4" colspan="7" class="brdr-ts" style="height:2px; font-size: 2px;"> </td>
</tr>
<tr class="fmt-text">
<td colspan=1 class="fmt-texthdr">TOTAL</td>
<td colspan=1 class="brdr-bs brdr-ts brdr-ls"><?php echo $bttot1; ?></td>
<td colspan=1 class="brdr-bs brdr-ts"><?php echo $lttot1; ?></td>
<td colspan=1 class="brdr-bs brdr-ts"><?php echo $tttot1; ?></td>
<td colspan=1 bgcolor="#DBE5F1" class="brdr-bs brdr-ts brdr-lrs"><?php echo $nttot1; ?></td>
<td colspan=1 class="fmt-text brdr-rs brdr-bs brdr-ts"><?php echo $ettot1; ?></td>
<td colspan=1 class="fmt-text brdr-rs brdr-bs brdr-ts"></td>
</tr>
</table>
<hr align="center" width="533">
<?php
}
?>to keep things tidier in future posts the css formatting will be removed.
im pretty sure the problem lies in the foreach loop and how i iterate through but thus far nothing has come close to working.
any hlp much appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Slick812: many thanks works like a dream.
the date was set via a variable passed into the rdetail($id) function, i remover 2 vars from the function and fixed them for testing purposes, the date was shown in another table above these stating the commencement date of the rota.
when i initially tried using a forloop to output tyhe data i was getting errors but i now see where i was going wrong.
big thanks
the date was set via a variable passed into the rdetail($id) function, i remover 2 vars from the function and fixed them for testing purposes, the date was shown in another table above these stating the commencement date of the rota.
when i initially tried using a forloop to output tyhe data i was getting errors but i now see where i was going wrong.
big thanks
ASKER
just what the dr ordered
It's probably not immediately relevant to this issue, but I see you're using MySQL. PHP is doing away with MySQL, so you have a bit of work ahead of you. This article explains why and what you must do to keep your scripts running.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html