SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Visitor tracking and display results query

    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 Code:
    <?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. #2
    SitePoint Zealot
    Join Date
    Sep 2001
    Location
    glasgow
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    // 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($i2'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. #3
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thank you!

    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •