help organizing mysql array data with foreach

samoht · October 22, 2008

hello,

I have a simple form that retrieves data from a table by an interval set by the user:

<form name="form1" action="invoiceForm.php" method="POST">  
<input type="text" name="interval" id="interval" size="30">
<input type="submit" value="Submit" id="submit">
</form>
<?php
$days = $_POST['interval'];
if($_POST['interval']){
$q = mysql_query('SELECT * FROM jos_timesheet WHERE DATE_SUB(CURDATE(), INTERVAL '.$days. ' DAY)') or die (mysql_error()); 

while($row = mysql_fetch_array( $q )){
// I need a foreach here that will separate the clients 
echo "Name: ".$row['ts_client'];
echo " Date: ".$row['ts_created'];
}
}
?>

I need to separate the clients and display my data in a separte table for each client within the time range.

How do I write the foreach ??

PFMaBiSmAd · October 22, 2008

If you would post an example of your data and what you want the results to be it would help.

But, you need to start by ordering the data the way you want. Add an ORDER BY clause to your query -

ORDER BY ts_client, ts_created

This will cause the rows in the result set to be ordered (grouped) by the ts_client and then by the ts_created within each ts_client. Then you simply output the results.

samoht · October 22, 2008

here is what I have so far:

<?php
$days = $_POST['interval'];
if($_POST['interval']){
$q = mysql_query('SELECT * FROM jos_chronoforms_timesheet2 WHERE DATE_SUB(CURDATE(), INTERVAL '.$days. ' DAY) ORDER BY ts_client, ts_created') or die (mysql_error()); 

while($row = mysql_fetch_array( $q )){
$clients = array($row['ts_client']);
foreach($clients as $client) { 
echo "<h3>SIGN/OFF CLIENT: ".$row['ts_client']."</h3>\n";
echo '
<table border=0 cellpadding=0 cellspacing=0 width=97% style="border-collapse: collapse;table-layout:fixed;width:53em">
	<tr height=20 bgcolor="#555555;"><th>Date:</th><th>Time:</th><th>Description:</th></tr>';
	for($i=0;$i<count($client);$i++){
		echo '
			<tr>
				<td>'.$client['ts_created'].'</td><td>'.$client['ts_timeout'] - $client['ts_timein'].'</td><td>'.$client['ts_dscr'].'</td>
			</tr>
		</table>';
	}
}
}
}
?>

but this spits out:

<h3>SIGN/OFF CLIENT: Marclazar.com</h3>

<tr height=20 bgcolor="#555555;"><th>Date:</th><th>Time:</th><th>Description:</th></tr>0</td><td>M</td>

</tr>

</table><h3>SIGN/OFF CLIENT: Surgimap.com</h3>

<tr height=20 bgcolor="#555555;"><th>Date:</th><th>Time:</th><th>Description:</th></tr>0</td><td>S</td>

</tr>

</table>

My table data is simple

CREATE TABLE IF NOT EXISTS `jos_chronoforms_timesheet2` (
  `cf_id` int(11) NOT NULL auto_increment,
  `uid` varchar(255) NOT NULL,
  `recordtime` text NOT NULL,
  `ipaddress` text NOT NULL,
  `cf_user_id` text NOT NULL,
  `ts_timein` time NOT NULL,
  `ts_timeout` time NOT NULL,
  `ts_client` varchar(255) NOT NULL,
  `ts_dscr` varchar(255) NOT NULL,
  `ts_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`cf_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

does this make sense what I am trying to achieve??

samoht · October 22, 2008

Sorry I missed that you wanted an example of the output I am looking for.

I want something like this:

<?php 
<h3>SIGN/OFF CLIENT: Marclazar.com</h3>

<table border=0 cellpadding=0 cellspacing=0 width=97% style="border-collapse: collapse;table-layout:fixed;width:53em">
	<tr height=20 bgcolor="#555555;">
		<th>Date:</th><th>Time:</th><th>Description:</th>

	</tr>
	<tr>
		<td>10/20/2008</td><td>1:12:00</td><td>Work for Marc Lazar</td>
	</tr>
</table>

<h3>SIGN/OFF CLIENT: mywebpage.com</h3>

<table border=0 cellpadding=0 cellspacing=0 width=97% style="border-collapse: collapse;table-layout:fixed;width:53em">
	<tr height=20 bgcolor="#555555;">
		<th>Date:</th><th>Time:</th><th>Description:</th>

	</tr>
	<tr>
		<td>10/20/2008</td><td>2:42:00</td><td>Work for mywebpage</td>
	</tr>
	<tr>
		<td>10/21/2008</td><td>0:38:00</td><td>More work for mywebpage</td>
	</tr>
	<tr>
		<td>10/21/2008</td><td>1:02:00</td><td>Even more work for mywebpage</td>
	</tr>
</table>

ect..

This seems straight forward but I am having problems organizing my data by client and understanding how to loop through word done for each client.

Thanks for any help!

Barand · October 22, 2008

here's the logic in pseudocode

prevClient = '';                 // store the previous client, blank to start with

while (fetch next row)

    if (client != prevClient)              // have we a new client?
    {
          if (prevClient != '')
              close previous table

          print client name
          start new table

          prevClient = client.                // reset last client
    }
     
     output detail row.
}
close last table

samoht · October 22, 2008

Thanks Barand,

One question: how do I perform math on the repeating results?

when I have:

<?php
$prevclient = '';
while($row = mysql_fetch_array( $q )){
	if($row['ts_client'] != $prevclient){
		if($prevclient != '') {
			echo '</table>'."\n";
		}
		echo "\n<h3>SIGN/OFF CLIENT: ".$row['ts_client']."</h3>\n";
		echo '
		<table border=0 cellpadding=0 cellspacing=0 width=97% style="border-collapse: collapse;table-layout:fixed;width:53em">
			<tr height=20 bgcolor="#555555;">
				<th>Date:</th><th>Time:</th><th>Description:</th>
			</tr>';
		$prevclient = $row['ts_client'];
	}
	echo '
		<tr>
			<td>'.$row['ts_created'].'</td><td>'.$row['ts_timeout'] - $row['ts_timein'].'</td><td>'.$row['ts_dscr'].'</td>
		</tr>';
}
echo '</table>'."\n";

my output gets all messed up because of the $row['ts_timeout'] - $row['ts_timein']

but I will need to subtract these to get the total time per entry??

Any Thoughts?

samoht · October 22, 2008

OPPS -

I realize I need a time format so I changed it to

		echo '
		<tr>
			<td>'.$row['ts_created'].'</td><td>'.date('h:i:s',$row['ts_timeout'] - $row[ts_timein]) .'</td><td>'.$row['ts_dscr'].'</td>
		</tr>';

the problem is I get the same result for every row?? (always the first time in/ time out )

Barand · October 22, 2008

in your query you could put

SELECT ... TIMEDIFF(ts_timeout, ts_timein) as totaltime, ...

samoht · October 22, 2008

Ha yes,

very nice. Thank you!!

Any chance I could do math on all the entries for each client?

how would I add up all the $row['totaltime'] to get a grand total??

Barand · October 22, 2008

You could also select the timediff in seconds then total those. Divide it by 3600 at the end to get the total hours

SELECT ... TIMEDIFF(ts_timeout, ts_timein) as totaltime,

TIME_TO_SEC(TIMEDIFF(ts_timeout, ts_timein)) as totalsecs, ...

The pseudocode then becomes

prevClient = '';                 // store the previous client, blank to start with

while (fetch next row)

    if (client != prevClient)              // have we a new client?
    {
          if (prevClient != '')
              close previous table

          print client name
          start new table

          prevClient = client.                // reset last client
    }
     
     output detail row.
     grandtotal += totalsecs
}
close last table
print grandtotal/3600

philipolson · October 22, 2008

On a only slightly related note, please don't use mysql_fetch_array() as it wastes energy. Either pass in the second parameter to it or more likely just use mysql_fetch_assoc().

Barand · October 22, 2008

I agree. I always thought fetch_array rather superfluous. I can't see why I'd need both sets of array keys, and if I'm going to use the second parameter I may as well use fetch_row or fetch_assoc

samoht · October 23, 2008

I see,

However, I need the total seconds for each client?

also I need the format to be HH:MM:SS - but when I try:

<?php
$q = mysql_query('SELECT TIMEDIFF(ts_timeout, ts_timein) as totaltime, TIME_TO_SEC(TIMEDIFF(ts_timeout, ts_timein)) as totalsecs, ts_client, ts_dscr, ts_created FROM jos_chronoforms_timesheet2 WHERE DATE_SUB(CURDATE(), INTERVAL '.$days. ' DAY) ORDER BY ts_client, ts_created') or die (mysql_error()); 

$prevclient = '';
while($row = mysql_fetch_assoc( $q )){
	if($row['ts_client'] != $prevclient){
		if($prevclient != '') {
			echo '
		</table>'."\n";
		}
		echo "\n<h3>SIGN/OFF CLIENT: ".$row['ts_client']."</h3>\n";
		echo '
		<table border=0 cellpadding=0 cellspacing=0 width=97% style="border-collapse: collapse;table-layout:fixed;width:53em">
			<tr height=20 bgcolor="#555555;">
				<th>Date:</th><th>Time:</th><th>Description:</th>
			</tr>';
		$prevclient = $row['ts_client'];
	}
	echo '
			<tr>
				<td>'.$row['ts_created'].'</td><td>'.$row['totaltime'].'</td><td>'.$row['ts_dscr'].'</td>
			</tr>';
	$gt += $row['totalsecs'];
}
echo '
</table>'. date('H:i:s',$gt/3600) ."\n";

I get 19:00:00 as a grand total?? (which is not the total)

samoht · October 23, 2008

OK - I figured out that gmdate("H:i:s", $gt) gives me the format that I need

But I still am not sure how to get a total for each client.

If I place the code just after the first close of table then I will get the totals for each client except the last one??

also - using gmdate() I don't have to divide by 3600.

Barand · October 23, 2008

put total for last client just before the end close of table

samoht · October 24, 2008

Thanks very much for the help.

I think I have the form set up mostly the way I want it to be. However, I realize that I don't know how I would mail the results once creating this nice invoice page?

Is there a way to put the results in a form of its own -> add hidden inputs an a "mail Results" submit button?

The difficulty I am having is that my HTML will have several clients info So I would need to loop through the $_POST in a similar way as I looped through the $row - but how can I do That??

Any ideas?

samoht · October 24, 2008

Also, I heard that php mail() is not very fast?

Should I use something else?

Sign In

help organizing mysql array data with foreach

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information