Displaying Reports from Arrays

ryclem

I am storing some site reporting statistics in the database, basically just sessions and two target events (sales and the user adding themselves to the mailing list). In this report table, I want to display the current day (which works fine) and Month-to-Date and Year-to-Date statistics as well.

I've included all the query and the table output below, but as mentioned the issue is with the MTD and YTD query and output. I think one of the issues is that once those queries come back, I need to total them.

If anyone can provide any insight on this, please help.

Thanks,
Ryan

<?
// Execute query
$today = date("y-m-d");
$top_query = mysql_query("SELECT * FROM tracking where date = '$today'");

// store in $num2
$numrow = mysql_num_rows($top_query);

// If empty, say Empty table
if ($numrow == 0)
{
die ("Empty Table for $numrow $today");
}

// Loop the contents of what's brought back
while($Row = mysql_fetch_array($top_query))

{

$array[$Row['type']] = $Row;
}

🙁
// Execute MTD query
$mtd = date("y-m");
$mtd_query = mysql_query("SELECT * FROM tracking where date like '$mtd-%%'");

// store in $num
$mtdnumrow = mysql_num_rows($mtd_query);

// If empty, say Empty table
if ($mtdnumrow == 0) {
die ("Empty Table for $mtd");
}

// Loop the contents of what's brought back
while($mtdrow = mysql_fetch_array($mtd_query))

{

$mtdarray[$mtdrow['type']] = $mtdrow;
}

// Execute YTD query
$ytd = date("y");

$ytd_query = mysql_query("SELECT * FROM tracking where date like '$ytd-%%-%%'");

// store in $num
$ytdnumrow = mysql_num_rows($ytd_query);

// If empty, say Empty table
if ($ytdnumrow == 0) {
die ("Empty Table for $mtd");
}

// Loop the contents of what's brought back
while($ytdrow = mysql_fetch_array($ytd_query))

{

$ytdarray[$ytdrow['type']] = $ytdrow;
}
🙂
?>
<table>
<tr>
<th bgcolor=yellow> </th>

<th bgcolor=yellow>  Y-T-D  </th>
</tr>
<tr>
<td width=100>Sessions</td>
<td align=center><? echo $array['session']['count']; ?></td>
<td align=center><? echo $mtdarray['session']['count']; ?></td>
<td align=center><? echo $ytdarray['session']['count']; ?></td>

</tr>
<tr>

<td>Name Capture</td>
<td align=center><? echo $array['name_capture']['count']; ?></td>
<td align=center><? echo $mtdarray['name_capture']['count']; ?></td>
<td align=center><? echo $ytdarray['name_capture']['count']; ?></td>
</tr>
<tr>
<td height="21">Sales</td>
<td align=center><? echo $array['sale']['count']; ?></td>
<td align=center><? echo $mtdarray['sale']['count']; ?></td>
<td align=center><? echo $ytdarray['sale']['count']; ?></td>
</tr>
<tr>
<td>Conversion</td>
<td align=center><? echo round(($array['sale']['count']) / ($array['session']['count'])100, 4); ?></td>
<td align=center><? echo round(($mtdarray['sale']['count']) / ($mtdarray['session']['count'])100, 4); ?></td>
<td align=center><? echo round(($ytdarray['sale']['count']) / ($ytdarray['session']['count'])*100, 4); ?></td>
</tr>
</table>

AstroTeg

I skipped directly over the code so maybe I missed something. But are you trying to do a cross-tab report? If so, there's easier ways... 🙂

ryclem

Not sure exactly what a cross-tab report is -- but an example of the output that I am trying to get is as follows:

                           05-03-15      M-T-D         Y-T-D

Sessions 576 13485 52215
Name Capture 12 121 522
Sales 9 117 511
Conversion 1.5625 0.8676 0.9786

Again, the daily column works fine... its the data for the MTD and YTD that I'm having problems with...

Additionally, once I get this fixed, I want to add a calendar option so I can select any day in time for historical info.

Hope this clarifies...

Thanks,
Ryan

AstroTeg

That looks like a cross-tab to me.

I can't find the tutorial I used back in the day (it was pretty easy to read and understand). I found this: http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html

A search on google will produce a good number of tutorials on this.

I think this is what you'll want. It looks crazy to build, but it really isn't bad at all IF you take a moment to understand each step of the process...