Author Topic: Count of unique site visits (sessions)?  (Read 22397 times)

0 Members and 1 Guest are viewing this topic.

Offline possom

  • Pre-Newbie
  • Posts: 3
    • View Profile
Count of unique site visits (sessions)?
« on: May 21, 2003, 11:12:54 PM »
Hi all,

I know there have been alot of posts about site stats, and counts etc. but nothing to answer my question. How can I show the total number of visits to my site that are from a unique user/session (sort of a site counter, but internal to site)?

It is my understanding that each registered user or guest to a site have a unique session, can this be used in find this statistic?

Hope I was clear.... :?

TIA,
possom

Offline possom

  • Pre-Newbie
  • Posts: 3
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #1 on: May 27, 2003, 05:15:22 AM »
I finally found time to figure out a solution.

It is to simply store session information each time a user enters the site or logs in, then use that data to show the number of site hits...

Steps are as follows:

1. Create a new table for storing all historical session information (schema exactly the same as the '4images_sessions' table).

Code: [Select]

DROP TABLE IF EXISTS 4images_sessions_hist;
CREATE TABLE 4images_sessions_hist (
  session_id varchar(32) NOT NULL default '',
  session_user_id mediumint(8) NOT NULL default '0',
  session_lastaction int(11) NOT NULL default '0',
  session_location varchar(255) NOT NULL default '',
  session_ip varchar(15) NOT NULL default '',
  UNIQUE session_id_date (session_id, session_lastaction),
  KEY session_id_ip_user_id (session_id,session_ip,session_user_id)
) TYPE=MyISAM;


2. Change includes/sessions.php

2.1 Find:
Code: [Select]

    if (!$login_process) {
      $this->session_id = $this->generate_session_id();
      $sql = "INSERT INTO ".SESSIONS_TABLE."
              (session_id, session_user_id, session_lastaction, session_location, session_ip)
              VALUES
              ('$this->session_id', ".$this->user_info['user_id'].", $this->current_time, '$this->user_location', '$this->user_ip')";
      $site_db->query($sql);
      ...


Add after:

Code: [Select]

      $sql = "INSERT INTO ".SESSIONS_HIST_TABLE."
              (session_id, session_user_id, session_lastaction, session_location, session_ip)
              VALUES
              ('$this->session_id', ".$this->user_info['user_id'].", $this->current_time, '$this->user_location', '$this->user_ip')";
      $site_db->query($sql);


2.2 Find:
Code: [Select]

    if ($user_id != GUEST) {
      if ($row[$user_table_fields['user_password']] == $user_password) {
        $ip_sql = ($this->mode == "get") ? " AND session_ip = '$this->user_ip'" : "";
        $sql = "UPDATE ".SESSIONS_TABLE."
                SET session_user_id = $user_id
                WHERE session_id = '$this->session_id'
                $ip_sql";
        $site_db->query($sql);


Add after:

Code: [Select]

        $sql = "UPDATE ".SESSIONS_HIST_TABLE."
                SET session_user_id = $user_id
                WHERE session_id = '$this->session_id'
                $ip_sql";
        $site_db->query($sql);


3. Add to includes/constants.php

Code: [Select]

define('SESSIONS_HIST_TABLE', $table_prefix.'sessions_hist');


4. Now access the data, for example I will include total number of hits on your site. If you have a stats.php in the PHP root dir, add:

Code: [Select]

//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM ".SESSIONS_HIST_TABLE." sh, ".USERS_TABLE." u
        WHERE sh.session_user_id = u.user_id
        AND u.user_level <> ".ADMIN;
$row = $site_db->query_firstrow($sql);

$total_sessions = $row['sessions'];
 
$site_template->register_vars("total_sessions", $total_sessions);
unset($total_sessions);


5. Use the statistic in a page, i.e. using {total_sessions} somewhere

--- end of mod ---

There is one undesirable effect of logging sessions this way is that if a user logs off, and the screen refreshs back to the main page, as a Guest, then a second session is logged even though it is the same user. Also, when you first go to the your site, sometimes when you reload the page another session is logged, but once you follow a link a reload no longer adds a new session.
Any ideas to fixing (or better understanding) these issues? ;)

Cheers,
possom

Offline possom

  • Pre-Newbie
  • Posts: 3
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #2 on: March 31, 2005, 09:35:13 PM »
Wow, its been almost two years since I posted this... I have a related query to this site modification.

Firstly, one observation is that the 4images_sessions and 4images_sessions_hist table seem to have more than one record for each time a user browses the website, so simply counting the number of records in the 4images_sessions_hist table will not give me an accurate count of visitors.

The question, which is more of a MySQL question, is how can I change the following SQL to count the number of groupings returned, not returning each group and an associated count:

Code: [Select]
SELECT COUNT(*) as sessions
FROM `4images_sessions_hist` sh, `4images_users` u
WHERE sh.session_user_id = u.user_id
AND u.user_level <> 9
group by FROM_UNIXTIME(sh.session_lastaction, '%Y-%m-%d'), sh.session_user_id, sh.session_ip;

This returns a row for each unique set of session_lastaction, session_user_id and session_ip with a count, so for five separate visits the SQ :? :?L may return:
1
10
1
2
5
... but I want to see '5' returned.  :?

Hope this is clear.

TIA,
possom

Offline Jetstream

  • Newbie
  • *
  • Posts: 20
    • View Profile
    • Fotogalerie Chris Gubelmann
Re: Count of unique site visits (sessions)?
« Reply #3 on: August 10, 2007, 08:22:52 PM »
Hey Possom

Did you found in the meantime a solution to count the total VISITORS of your 4images site?

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #4 on: August 11, 2007, 08:21:31 PM »
Visitor or user + admin for total ?
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline Jetstream

  • Newbie
  • *
  • Posts: 20
    • View Profile
    • Fotogalerie Chris Gubelmann
Re: Count of unique site visits (sessions)?
« Reply #5 on: August 14, 2007, 03:41:41 PM »
Thx thunderstrike, only Visitors (GUESTS) and users (USER) without ADMIN for total

I was trying out SQL code to find the solutiojn, but my knowledge of sql seems to bee not sufficient :D

I came only to the same result as possom on his last post

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #6 on: August 14, 2007, 03:46:42 PM »
Chek for step 2.2 (1st post).

Quote
$sql = "UPDATE ".SESSIONS_HIST_TABLE."
                SET session_user_id = $user_id
                WHERE session_id = '$this->session_id'
                $ip_sql";
        $site_db->query($sql);

for:

Quote
if ($this->user_info['user_level'] == GUEST || $this->user_info['user_level'] == USER) {
$sql = "UPDATE ".SESSIONS_HIST_TABLE."
           SET session_user_id = $user_id
           WHERE session_id = '$this->session_id'
           $ip_sql";

           $site_db->query($sql);
}

Work for GUEST and USER. ;)
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline Jetstream

  • Newbie
  • *
  • Posts: 20
    • View Profile
    • Fotogalerie Chris Gubelmann
Re: Count of unique site visits (sessions)?
« Reply #7 on: August 14, 2007, 04:04:22 PM »
thank you thunderstrike for your VERY FAST answer, thx really a lot

I just implemented the new code that you proposed above, and it works fine

But my main question is: how to READ OUT the 4images_sessions_hist table for showing me how many visitors (GUESTS & USERS) have visited my site in a time period defined by me?

Greets from sunny switzerland
Chris
« Last Edit: August 14, 2007, 04:16:38 PM by Jetstream »

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #8 on: August 14, 2007, 04:32:47 PM »
For showing stats for guest and user count:

Quote
//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM (".SESSIONS_HIST_TABLE." sh, ".USERS_TABLE." u)
        WHERE sh.session_user_id = " . get_user_table_field("u.", "user_id") . "
        AND " . get_user_table_field("u.", "user_level") . " = '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " = '" . USER . "'";
$row = $site_db->query_firstrow($sql);
« Last Edit: August 14, 2007, 04:50:58 PM by thunderstrike »
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline Jetstream

  • Newbie
  • *
  • Posts: 20
    • View Profile
    • Fotogalerie Chris Gubelmann
Re: Count of unique site visits (sessions)?
« Reply #9 on: August 17, 2007, 01:05:13 PM »
Hi Thunderstrike, thx for your great help

now I gave in the following code at the bottom of top.php:

Code: [Select]
//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM (".SESSIONS_HIST_TABLE." sh, ".USERS_TABLE." u)
        WHERE sh.session_user_id = " . get_user_table_field("u.", "user_id") . "
        AND " . get_user_table_field("u.", "user_level") . " = '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " = '" . USER . "'";

$counter_result = $site_db->query_firstrow($sql);

//-----------------------------------------------------
//--- Print Out ---------------------------------------
//-----------------------------------------------------
$site_template->register_vars(array(
  "summe_hits" => $sum_hits,
  "counter_result" => $counter_result,
...

If I print out {counter_result} it gives me only back "Array". Anything I don't see?

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #10 on: August 17, 2007, 08:08:21 PM »
Quote
"counter_result" => $counter_result,

for:

Quote
"counter_result" => (isset($counter_result['sessions'])) ? $counter_result['sessions'] : 0,
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline Jetstream

  • Newbie
  • *
  • Posts: 20
    • View Profile
    • Fotogalerie Chris Gubelmann
Re: Count of unique site visits (sessions)?
« Reply #11 on: August 19, 2007, 05:12:54 PM »
Cool,  :lol: I think we (or have I to say: "You" :roll:) are on the right way.

But:

with this code combination I have the result 0 (ZERO), I think that may come from the
Code: [Select]
(isset($counter_result['sessions'])) ? $counter_result['sessions'] : 0,] , it seems to me that
Code: [Select]
$counter_result['sessions'] seems to be set so that this order will give zero to "counter_result".

Now, I have no idea how this variable has to be set and how to implement it, thats exactly my actual question to you.

 :idea: Fot clearness, I will paste in above my whole code at the end of top.php who should read out the count of total unique site visits:

Code: [Select]
//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM (".SESSIONS_HIST_TABLE." sh, ".USERS_TABLE." u)
        WHERE sh.session_user_id = " . get_user_table_field("u.", "user_id") . "
        AND " . get_user_table_field("u.", "user_level") . " = '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " = '" . USER . "'";

$counter_result = $site_db->query_firstrow($sql);

//-----------------------------------------------------
//--- Clickstream -------------------------------------
//-----------------------------------------------------
$clickstream = "<span class=\"clickstream\"><a href=http://www.chris-gubelmann.ch/index.html>Homepage</a> --> <a href=\"".$site_sess->url(ROOT_PATH."index.php")."\" class=\"clickstream\">Fotogalerie</a>".$config['category_separator'];
if ($cat_id && isset($cat_cache[$cat_id])) {
  $clickstream .= get_category_path($cat_id, 1).$config['category_separator'];
}
$clickstream .= $lang['top_images']."</span>";

//-----------------------------------------------------
//--- Print Out ---------------------------------------
//-----------------------------------------------------
$site_template->register_vars(array(
  "summe_hits" => $sum_hits,
  "counter_result" => (isset($counter_result['sessions'])) ? $counter_result['sessions'] : 0,
  "summe_downloads" => $sum_dls,
  "summe_votes" => $sum_votes,
  "summe_cats" => $sum_cat,
  "msg" => $msg,
  "clickstream" => $clickstream,
  "lang_top_image_hits" => $lang['top_image_hits'],
  "lang_top_image_downloads" => $lang['top_image_downloads'],
  "lang_top_image_rating" => $lang['top_image_rating'],
  "lang_top_image_votes" => $lang['top_image_votes']
));
$site_template->print_template($site_template->parse_template($main_template));

$content = ob_get_contents();
ob_end_clean();

if ($cache_page_top) {
  // Reset session mode
  $site_sess->mode = $old_session_mode;

  save_cache_file($cache_id, $content);
}

} // end if get_cache_file()

echo $content;

include(ROOT_PATH.'includes/page_footer.php');
?>

I think we are really on the right way and not far of the goal, but I need your help again. I cannot repeat it enough that I am so thankful of your work, I aprecciate my biggest honour to people like you who invest your time for that good thing. May be I can invite you vor a visit on my 4images based gallery  www.fotigalerie.ch. Hope to hear from you again.
Thanks,
Chris

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #12 on: August 19, 2007, 07:11:14 PM »
Quote
seems to be set so that this order will give zero to "counter_result".

0 mean no result found.

Quote
//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM (".SESSIONS_HIST_TABLE." sh, ".USERS_TABLE." u)
        WHERE sh.session_user_id = " . get_user_table_field("u.", "user_id") . "
        AND " . get_user_table_field("u.", "user_level") . " = '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " = '" . USER . "'";

$counter_result = $site_db->query_firstrow($sql);

for:

Quote
//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM ".SESSIONS_HIST_TABLE." sh
        LEFT JOIN "  . USERS_TABLE . " u ON (" . get_user_table_field("u.", "user_id") . " = sh.session_user_id)
        WHERE " . get_user_table_field("u.", "user_level") . " >= '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " <= '" . USER . "'";

$counter_result = $site_db->query_firstrow($sql);
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline Jetstream

  • Newbie
  • *
  • Posts: 20
    • View Profile
    • Fotogalerie Chris Gubelmann
Re: Count of unique site visits (sessions)?
« Reply #13 on: August 19, 2007, 08:56:12 PM »
 :lol: Yo man yo that was is, it works!!!!! Juhuuuu! thk you so much!!!!!!!!!!!!!!  :P Grrrrrrrrrrrrrrrreat

this ist the first working session-id based visitor counter for 4images!!!!!


thanks to you thunderstrike  :!:

  :?: 'Nother question: Can we build into the SQL sentence a "group by sh.session_ip" to get the visitor counter based upon visitors' ip adress?  :?:
« Last Edit: August 19, 2007, 09:06:39 PM by Jetstream »

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Count of unique site visits (sessions)?
« Reply #14 on: August 19, 2007, 09:17:31 PM »
Quote
//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM ".SESSIONS_HIST_TABLE." sh
        LEFT JOIN "  . USERS_TABLE . " u ON (" . get_user_table_field("u.", "user_id") . " = sh.session_user_id)
        WHERE " . get_user_table_field("u.", "user_level") . " >= '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " <= '" . USER . "'";

$counter_result = $site_db->query_firstrow($sql);

for:

Quote
//-----------------------------------------------------
//--- total user sessions  ----------------------------
//-----------------------------------------------------
$sql = "SELECT COUNT(*) as sessions
        FROM (".SESSIONS_HIST_TABLE." sh, " . SESSIONS_TABLE . " s)
        LEFT JOIN "  . USERS_TABLE . " u ON (" . get_user_table_field("u.", "user_id") . " = sh.session_user_id)
        WHERE " . get_user_table_field("u.", "user_level") . " >= '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " <= '" . USER . "'
        GROUP BY s.session_ip";

$counter_result = $site_db->query_firstrow($sql);
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?