Creating Reports

raouleduke

I am creating reports from a database, there is one field that I would like to group by is there a way to group by a certain field and display that field. Here is what I would like to do is group by SANO and have them displayed on multiple pages and at the end of each SANO I would like to have totals, because there are way to many records to display on one page. Hope you understand what I am trying to say, I am fairly new to php, I have downloaded a few reports generators but they seem to be to cumbersome, maybe that is just what I need to do.
Here is what I got so far, thanks in advance for any help.

<?php


mysql_connect("","",""); 
mysql_select_db(""); 
	$query = "SELECT * FROM KEYCAD WHERE KEYCAD.`PROCESSEDDATE` BETWEEN '$From' AND '$To' AND ACTIVE LIKE 'Y'";
	$result = mysql_query($query);
?>

marque

Checking to see if I have the effort to read your code... nope. Sorry please refrain and please, please, reduce your code down to a few lines rather than putting your entire script on here.

jpmoriarty

yep, i'm with you marque. all i saw before getting bored was "group by", so i'm going to (blindly) suggest the group by sql statement:

select customerid, sum(amount) from orders group by customerid;

raouleduke

Sorry,
I thought it might help.

marque

Thanks for that revise.

Let me just see if I got this right:

The concept you are looking for is to collect rows from a database between a $From value and a $To value, I'll take into assumption that $From and $To is a timestamp or at least an interger or float.

So here you get, lets say 30 values, you want to display each row and at the bottom of the table you have a total of rows?

Then there would be a previous and next button to show the next pages. And possibily a page number list?

Am I on the right track?

raouleduke

Thats right but at the end I want to total up certain fields and show percentatges.

zevacronet

check out the limit clause and the sum(), might be what your looking for or at least the right track. You could limit say 30 result to a page, sum up what you want and display them at the bottom. hope that helps

marque

Ok i think the best way to do it is to have 2 queries:

$query = "SELECT * FROM KEYCAD WHERE (PROCESSEDDATE BETWEEN '$From' AND '$To') AND ACTIVE LIKE 'Y'";
$result = mysql_query($query);
$total = "mysql_num_rows($result);

$total will become the total of the whole lot

if(!isset($PageNum) {
   $PageNum = 1;
   }

$NumPerPage = 10;

$query = "SELECT * FROM KEYCAD WHERE (PROCESSEDDATE BETWEEN '$From' AND '$To') AND ACTIVE LIKE 'Y' LIMIT ". $pageNum * $NumPerPage .",$NumPerPage ";
$result = mysql_query($query);

What I have done here is made $NumPerPage = 10 so it will only show 10 rows, $PageNum is the page number; in your next previous links just increment or de-increment (if thats a word) the page number.

Hope this helps.

raouleduke

I found a script, I got it to print out the first page, but when I click on a page link I get nothing. Tell me what you think.

<?php
include_once("header.php");

$From = $_POST['From'];
$To = $_POST['To'];
$SANO = $_POST['SANO'];
$REPORTS = $_POST['REPORTS'];
$limit = '10'; 

mysql_connect(""); 
mysql_select_db(""); 
if($REPORTS=='1'){

if (empty($offset) || $offset < 0) { 
$offset=0; 
} 
if (empty($index)) $index=0; /* This is to count the line index of the results */ 

$getrows = mysql_query("SELECT * FROM KEYCAD WHERE (PROCESSEDDATE BETWEEN '$From' AND '$To') AND ACTIVE LIKE 'Y'"); 
$numrows=mysql_num_rows($getrows); 

$query = mysql_query("select * from KEYCAD WHERE (PROCESSEDDATE BETWEEN '$From' AND '$To') AND ACTIVE LIKE 'Y' limit $offset,$limit"); 

while ($row=mysql_fetch_array($query)){ 
$index++; 
//print out results
}
if ($numrows <= $limit) { 
} 
else { 

if ($offset!=0) { 
$prevoffset=$offset-$limit; 
echo "<a onMouseOver=\"window.status='Previous $limit Results'; return true\"; href=\"$PHP_SELF?offset=$prevoffset&index=$prevoffset\"><B>[Previous]</B></a>   "; 
} 
else echo "<b><font color=666666>[Previous]</font></b>   "; 

$pages = intval($numrows/$limit); 

if ($numrows%$limit) { 

$pages++; 
} 

for ($i=1;$i<=$pages;$i++) { 

if (($offset/$limit) == ($i-1)) { 

echo " <b><font color=666666>$i</font></b> "; 
} else { 

$newoffset=$limit*($i-1); 
echo " <a onMouseOver=\"window.status='Page $i Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>$i</B></a> \n"; 
} 

} 

if (!((($offset/$limit)+1)==$pages) && $pages!=1) { 

$newoffset=$offset+$limit; 
echo "    <a onMouseOver=\"window.status='Next $limit Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>[Next]</B></a><p>\n"; 
}  

else echo "   <b><font color=666666>[Next]</font></b>"; 
}
}
?>

Thanks in advance!

marque

globals on or off?

if their off this could be the problem:

$offset should be $_POST['offset']

raouleduke

register_globals is on.