php - Count Page By Weekly, then create new table entry -


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