i've created page counter, , want track how many times user click page.
so far can track how many times click page, want set when week create new entry.
my mysql_query base on user's name, seeing there more 1 user i'm tracking.
this code tracking user selected page:
<?php include"lib/settings.php"; date_default_timezone_set("america/los_angeles"); $track_users_clicks = $_session['username']; $todays_date = date("m/d/y h:i:s a"); $query = "select * page_count `username`=".sql_val($track_users_clicks); $result = mysql_query($query) or die("<b>a fatal mysql error occured</b>.<br />query: ".$query."<br />error: (".mysql_errno().") ".mysql_error()); while ($row = mysql_fetch_assoc($result)) { $username = $row['username']; $counter_snippet = $row['counter_snippet']; $date_time = $row['date_time']; }//end while if ($_server['remote_addr']){ $query_update = 'update page_count set `counter_snippet` = '.sql_val($counter_snippet + 1).', `date_time` = '.sql_val($todays_date).' `username` = '.sql_val($track_users_clicks); $result = mysql_query($query_update) or die("<b>a fatal mysql error occured</b>.<br />query: ".$query_update."<br />error: (".mysql_errno().") ".mysql_error()); } ?>
this far i've come tracking user per week:
<?php include"lib/settings.php"; date_default_timezone_set("america/los_angeles"); //$track_users_clicks = $_session['username']; $todays_date = date("m/d/y"); $begin_date = date("m/d/y"); $end_date = date( "m/d/y", strtotime($begin_date."+7 day" ) ); $user_log = "trevor.hanes" .$end_date; $track_users_clicks = "trevor.hanes"; $query_begin = "select * date_time `username`=".sql_val($track_users_clicks); $result = mysql_query($query_begin) or die("<b>a fatal mysql error occured</b>.<br />query: ".$query_begin."<br />error: (".mysql_errno().") ".mysql_error()); while ($row = mysql_fetch_assoc($result)) { $username = $row['username']; $counter_snippet = $row['counter_snippet']; $start_date = $row['start_date']; //$end_date = $row['end_date']; } if ($todays_date >= $end_date){ $query_start = 'insert date_time ( `username`, `start_date`, `end_date`, `user_log` ) values ( '.sql_val($track_users_clicks).', '.sql_val($todays_date).', '.sql_val($end_date).', '.sql_val($user_log).' )'; $result = mysql_query($query_start) or die("<b>a fatal mysql error occured</b>.<br />query: ".$query_start."<br />error: (".mysql_errno().") ".mysql_error()); } else { if ($_server['remote_addr']){ if ($end_date >= $todays_date){ $query_update = 'update date_time set `counter_snippet` = '.sql_val($counter_snippet + 1).' `end_date` = '.sql_val($end_date); $result = mysql_query($query_update) or die("<b>a fatal mysql error occured</b>.<br />query: ".$query_update."<br />error: (".mysql_errno().") ".mysql_error()); }}} echo $begin_date; echo "<br>"; echo $end_date; echo "<br>"; echo $user_log; ?>
so problem once change date later end date, keeps creating new table entries. instead of creating 1 new table entry new week updating hits.
any thoughts on i'm doing wrong.
first thing's first: need using prepared statements. prepared statements prevent malicious code profoundly impacting database. example, drop entire table if had poorly configured user privileges getting following text $_session['username']:
; drop table page_count;
this looks decent tutorial on pdo (most common way use prepared statements in php) at: http://wiki.hashphp.org/pdo_tutorial_for_mysql_developers
anyway, answer question, use query following:
select *, yearweek(date_time) week, page_count `username` = :username group week;
documentation yearweek: http://www.techonthenet.com/mysql/functions/yearweek.php
Comments
Post a Comment