4images Forum & Community
4images Modifications / Modifikationen => Mods & Plugins (Requests & Discussions) => Topic started by: possom 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
-
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).
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:
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:
$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:
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:
$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
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:
//-----------------------------------------------------
//--- 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
-
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:
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
-
Hey Possom
Did you found in the meantime a solution to count the total VISITORS of your 4images site?
-
Visitor or user + admin for total ?
-
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
-
Chek for step 2.2 (1st post).
$sql = "UPDATE ".SESSIONS_HIST_TABLE."
SET session_user_id = $user_id
WHERE session_id = '$this->session_id'
$ip_sql";
$site_db->query($sql);
for:
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. ;)
-
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
-
For showing stats for guest and user count:
//-----------------------------------------------------
//--- 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);
-
Hi Thunderstrike, thx for your great help
now I gave in the following code at the bottom of top.php:
//-----------------------------------------------------
//--- 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?
-
"counter_result" => $counter_result,
for:
"counter_result" => (isset($counter_result['sessions'])) ? $counter_result['sessions'] : 0,
-
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 (isset($counter_result['sessions'])) ? $counter_result['sessions'] : 0,] , it seems to me that $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:
//-----------------------------------------------------
//--- 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 (http://www.fotigalerie.ch). Hope to hear from you again.
Thanks,
Chris
-
seems to be set so that this order will give zero to "counter_result".
0 mean no result found.
//-----------------------------------------------------
//--- 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:
//-----------------------------------------------------
//--- 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);
-
: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? :?:
-
//-----------------------------------------------------
//--- 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:
//-----------------------------------------------------
//--- 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);
-
:D Thk you very much, I nearly had the solution, made just a syntax fault. Now, if you change sh.session_ip instad of s.session_ip, it gives you out exactly the result I ever wanted: 1404 ip-uniqe visitors since loggin into session_hist_table, which corresponds exactly with the statistics results.
Thunderstrike, thank you for your faboulous work and the invested time!
:!: We have here a mod for a working IP-based visitor counter for 4images :!:
If I have the time to, I will post the whole mod instructions in one here.
-
Thank for post. ;)
-
thunderstrike, you are the expert of sql AND php, so i have a further question: I have a little problem understanding/converting the 4images date format INT(11). So, my hopefully last question to you is how to supplement the WHERE clause to show the result in an gap of any given date (y, m, d; h, m, s)
greets from rainy switzerland
chris
-
Two examp:
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-format (mySQL v4.1)
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format (mySQL v5.1)
-
Okay, I think I understand now the syntax of the DATE_FORMAT SQL order.
But I have to ask the question the other way: Can you please make this php code with the SQL clause "WHERE sh.session_lastaction =" to run, I dont know how to get the variables 'today' and 'month', so my idea of the php code with sql query is:
//-----------------------------------------------------
//--- total user sessions today------------------------
//-----------------------------------------------------
$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 sh.session_lastaction = today and " . get_user_table_field("u.", "user_level") . " >= '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " <= '" . USER . "'
GROUP BY s.session_ip";
$counter_result_day = $site_db->query_firstrow($sql);
and
//-----------------------------------------------------
//--- total user sessions this month ------------------
//-----------------------------------------------------
$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 sh.session_lastaction = month and " . get_user_table_field("u.", "user_level") . " >= '" . GUEST . "' AND " . get_user_table_field("u.", "user_level") . " <= '" . USER . "'
GROUP BY s.session_ip";
$counter_result_month = $site_db->query_firstrow($sql);
thkyou
chris
-
And how question is after post mine to benefit my time ? :?
You have all need (two links) for DATE_FORMAT from mySQL page ...
-
Yes I know I am using your time... I mentioned it above. So thank you for your work and time again. But: I don't know how to handel the DATE_FORMAT in the SQL Query! is it perhaps
WHERE sh.session_lastaction = DATE_FORMAT(date,'%d,%m,%y')?
Put how to put the actual date into 'date' in php????
You see once more I am a nerth in php!
thx
Chris
-
Yes I know I am using your time... I mentioned it above.
Then, a good luck to you !
Want use time ? Use in request for paid in forum.
-
soiifz... :cry: I dont wanted to waste your time I ment. I dont understand why you helped me so far and now stop it that immediately? Have I used the wrong words perhaps? You must know english isn't my mother tongue!
By the way: the two links to mysqlpage aren't helpful for my question how to convert the 4images date format (int(11)) into a format understood by sql!!!
Sorry for that and thanks again for your grait work and time
Chris
-
I dont wanted to waste your time I ment. I dont understand why you helped me so far and now stop it that immediately?
Immediately ?? 8O
You post on this topic - August 10 start subject and keep add on ... :?
By the way: the two links to mysqlpage aren't helpful for my question how to convert the 4images date format (int(11)) into a format understood by sql!!!
Match perfect your need. DATE_FORMAT is way to go for request. Is where source manual for mySQL. No better help. Good luck !
By the way - my english poor too.