Visitor tracking and display results query

Databases
1

Hi everyone,
I have a bit of a problem here (I know I’m being stupid, but cannot for the life of me figure out how to do this)
I have a stats table for page visits (for a community website I am making for fun) and a graph to display the data, but at the moment am doing a query for each day of the current month for that particular user, then printing a comma (for the graph to work properly)

Instead of having 31 different queries running on the page what is the best way to loop through the table and printing the data in thiformat:

0,5,6,3,0,4,3,6, etc (obviously 30 times and days with no visits printing 0 and always a comma in between)

I apologize for my awful exaplanation but would greatly appreciate it if someone could give me a bit of advice (Im a real amateur at SQL and am struggling to understand what Im doing!)

Here is a snippet of the code I am using currently:

<?php 

$curmon = date('m');


$pviews01 = mysql_query("SELECT * FROM `visitors_table` WHERE visitor_page = '".$_SESSION['username']."' AND visitor_day = '01' AND visitor_month = '".$curmon."' ");
$pv01 = mysql_num_rows($pviews01);
echo $pv01;

echo ',';

$pviews02 = mysql_query("SELECT * FROM `visitors_table` WHERE visitor_page = '".$_SESSION['username']."' AND visitor_day = '02' AND visitor_month = '".$curmon."' ");
$pv02 = mysql_num_rows($pviews02);
echo $pv02;

echo ',';

$pviews03 = mysql_query("SELECT * FROM `visitors_table` WHERE visitor_page = '".$_SESSION['username']."' AND visitor_day = '03' AND visitor_month = '".$curmon."' ");
$pv03 = mysql_num_rows($pviews03);
echo $pv03;

echo ',';

// THIS REPEATS 30 TIMES

?>
2

Set-up an array for your month with 0s for each day of that month, get the data with one query, and then update your array.

// build an array from '01' to $numDaysInMonth and set number of visits to 0
$viewsArray = array();

$numDaysInMonth = date('t');

for($i=1; $i<=$numDaysInMonth; $i++)
{
  $viewsArray[str_pad($i, 2, '0', STR_PAD_LEFT)] = 0; // pad to get in format '01', '02', '03'...
}


$pviews = mysql_query("SELECT
  visitor_day
, COUNT(visitor_day) AS numvisits
FROM `visitors_table`
WHERE visitor_page = '".$_SESSION['username']."'
  AND visitor_month = '".$curmon."'
GROUP BY visitor_day
ORDER BY visitor_day ASC");

$numDaysWithVisits = mysql_num_rows($pviews);

for($i=0; $i<$numDaysWithVisits; $i++)
{
  $viewRow = mysql_fetch_array($pviews);

  $viewsArray[$viewRow['visitor_day']] = $viewRow['numvisits'];
}

echo(implode(',', $viewsArray))
3

frasernm:
Thank you soo much! Ive been trying to work my head around this for ages and your snippet works perfectly
Please let me know if I can do anything in return