|
|
Posted by Steve on 10/13/06 23:53
ok kentor, here's the deal...
i'm going to post the topical code in this post. i will then reply to this
post with a report engine i built a while back. i'll explain here how to get
it to work on your pc/server. sorry for the text wrapping. i'm spoon feeding
you as it is. you can clean the dribble. ;^)
first, here's the db structure (all db syntax and access to data assumes
mysql):
CREATE TABLE siteTracking
(
Id INT AUTO_INCREMENT PRIMARY KEY
,
Page VARCHAR(255) NOT NULL
,
IpAddress VARCHAR(15) NOT NULL
,
UserName VARCHAR(255) NULL
,
Stamp TIMESTAMP NULL DEFAULT NOW()
ON UPDATE NOW()
);
next, you need to create a method to query the db...in the code that
follows, just change db::execute to point to a function called
dbExecute...here it is:
function execute($sql, $returnNewId = false)
{
$array = array();
$key = 0;
$records = mysql_query($sql);
$fieldCount = @mysql_num_fields($records);
while ($row = @mysql_fetch_array($records, MYSQL_NUM))
{
for ($i = 0; $i < $fieldCount; $i++)
{
$value = $row[$i];
$array[$key][strtoupper(@mysql_field_name($records, $i))] = $value;
}
$key++;
}
if ($returnNewId)
{
$array = array();
$array[0]['ID'] = mysql_insert_id();
}
@mysql_free_result($records);
return $array;
}
you will need to use mysql_connect once, prior to calling this function.
here's the report script...it is set up only to take from and to dates right
now. what you'll want to add is the ability to multi-select how to group the
fields and in what order to break them out. you'll feed that into the report
object.
<?
$pageTitle = 'Site Access';
$getReport = isset($_REQUEST['getReport']) ? true : false;
$groupBy = isset($_REQUEST['groupBy']) ? $_REQUEST['groupBy'] :
'PAGE';
if (!is_array($groupBy)){ $groupBy = array($groupBy); }
$fromMonth = $_POST['fromMonth'];
$fromYear = $_POST['fromYear'];
$toMonth = $_POST['toMonth'];
$toYear = $_POST['toYear'];
if ($fromMonth == ''){ $fromMonth = date('n'); }
if ($fromYear == ''){ $fromYear = date('Y'); }
if ($toMonth == ''){ $toMonth = date('n'); }
if ($toYear == ''){ $toYear = date('Y'); }
$fromDate = strtotime($fromMonth . '/01/' . $fromYear );
$toDate = strtotime($toMonth . '/01/' . $toYear );
if ($fromDate > $toDate)
{
$switchDate = $fromDate;
$fromDate = $toDate;
$toDate = $switchDate;
}
$months = array(
'01' => 'January' ,
'02' => 'February' ,
'03' => 'March' ,
'04' => 'April' ,
'05' => 'May' ,
'06' => 'June' ,
'07' => 'July' ,
'08' => 'August' ,
'09' => 'September' ,
'10' => 'October' ,
'11' => 'November' ,
'12' => 'December'
);
$years = array();
$thisYear = date('Y');
for ($i = $thisYear; $i >= ($thisYear - 5); $i--)
{
$years[] = $i;
}
?>
<div class="bullet" style="background:white no-repeat url('<?=
$site->imagesDirectory ?>bullet.jpg');">
<?= $pageTitle ?>
</div>
<hr>
<?
if (!$getReport)
{
?>
<form method="post">
<span class="label">Start</span>
<span>
<select class="value" name="fromMonth" style="width:120px;">
<?
foreach ($months as $month => $name)
{
$selected = $month == $fromMonth ? 'selected' : '';
?>
<option value="<?= $month ?>" <?= $selected ?>><?= $name ?></option>
<?
}
?>
</select>
<select class="value" name="fromYear" style="width:75px;">
<?
foreach (array_values($years) as $year)
{
$selected = $year == $fromYear ? 'selected' : '';
?>
<option value="<?= $year ?>" <?= $selected ?>><?= $year ?></option>
<?
}
?>
</select>
</span>
<br>
<span class="label">End</span>
<span>
<select class="value" name="toMonth" style="width:120px;">
<?
foreach ($months as $month => $name)
{
$selected = $month == $toMonth ? 'selected' : '';
?>
<option value="<?= $month ?>" <?= $selected ?>><?= $name ?></option>
<?
}
?>
</select>
<select class="value" name="toYear" style="width:75px;">
<?
foreach (array_values($years) as $year)
{
$selected = $year == $toYear ? 'selected' : '';
?>
<option value="<?= $year ?>" <?= $selected ?>><?= $year ?></option>
<?
}
?>
</select>
</span>
<br>
<br>
<input type="submit" value="Run Report">
<input type="hidden" name="getReport" value="1">
</form>
<?
echo $sessionFooter;
exit;
}
// report output
require_once $site->classDirectory . 'report.class.php';
$groups = db::describe('siteTracking');
$groups = array_diff(array_keys($groups), $groupBy);
$groups = $groupBy + $groups;
$groups = array('STAMP', 'PAGE'); // delete this line if NOT testing
$sql = "
SELECT Id ,
Page ,
IpAddress ,
UserName ,
DATE_FORMAT(Stamp, '%c-%e-%Y') Stamp
FROM siteTracking
ORDER BY " . implode (', ', $groups) . "
";
$records = db::execute($sql);
$columns = array(
'PAGE' ,
'IPADDRESS' ,
'USERNAME' ,
'STAMP'
);
$title = '';
switch ($groupBy[0])
{
case 'PAGE' : $title = 'Page Visited';
case 'IPADDRESS' : $title = 'Ip Address';
case 'USERNAME' : $title = 'User Name';
case 'STAMP' : $title = 'Date Visited';
}
$title = $pageTitle . ' - ' . $title;
$reportColumns = array();
$reportSummaries = array();
$reportColumns[] = new reportColumn(
'IPADDRESS' ,
'IP Address'
);
$reportColumns[] = new reportColumn(
'USERNAME' ,
'User Name'
);
//$reportSummaries[] = new reportSummary(
// 'PAGE' ,
// array(
// new reportFormula(
// '[Times Visted]'
,
// 'PAGE'
,
// 'COUNT'
// )
// )
// );
$report = new report(
$title ,
$records ,
$reportColumns ,
$groups ,
$reportSummaries ,
$reportColumns
);
echo $report->toHTML();
echo $sessionFooter;
?>
Navigation:
[Reply to this message]
|