SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Reporting and de-duping

    I have the following report to assess site interactions:

    PHP Code:
    <html>
    <head>
    <title>Briefing Downloads</title>
    </head>
    <body>
    <p>UserID, BriefingID, Date Downloaded</p>
    <?
    //dbcon.php
    //DB connection stuff - pulled out.

    $grab=@mysql_query("SELECT * FROM tracking");
    while (
    $result=mysql_fetch_array($grab)) {
    echo(
    $result['user_id']."#".$result['briefing_id']."#".$result['date_loaded']."<br />");
    }

    ?>

    </body>
    </html>
    However, the only "hits" of value to us are unique "briefings" pulled within a 24hr period. I basically need to only have one instance of a record where the user_id and briefing_id are identical within 24hrs.

    Don't have a clue how to get this done - advice appreciated as always!
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  2. #2
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, could you maybe post some rows from the database ? How is date_loaded stored (unix timestamp/timestamp/datetime ?)

    More info would make things easier
    - website

  3. #3
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yep, sure

    It is unix timestamp, example row:

    Code:
    id 	user_id 	briefing_id 	date_loaded
     1  	 607  	            53  	 1043943276
    I'm actually using a left join query now to pull in the section to which a "briefing" belongs, but hope that won't affect anything...
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  4. #4
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so for each day you want only one istance of the same briefing_id and user_id right?
    then this code might help, it might not be the best way to do it but at least it is the best I know of at the moment, it is not tested (rather hard to do so) but it might at least get you on the track.
    PHP Code:
    $grab=@mysql_query("SELECT * FROM tracking");
    $finished = array();
    while (
    $result=mysql_fetch_array($grab)) {
      
      
    //get the current date as YYYYMMDD
      
    $date = (string) date('Ymd'$result['date_loaded']);
      
      
    //(re)set $found to false, indicating I haven't found any match the current day
      
    $found false;

      
    //browse through the $finished array
      
    foreach ($finished as $val) {
        if (
    //see if row with the same userID, same briefingID and same day has comed before
          
    strcmp($val['date'], $date) === && 
          
    $val['user'] == $result['user_id'] &&
          
    $val['briefing'] == $result['briefing_id'])
          {
          
    //set $found to true indicating I have found a match and stop the loop
          
    $found true;
          break;
          }
      }
      
    //if $found is true go to next execution of the loop
      
    if ($found == true)
        continue;
      else 
    //else register the date, user and briefing for later search
        
    $finished[] = array(
        
    'date' => $date
        
    'user' => $result['user_id']. 
        
    'briefing' => $result['briefing_id']);
      
    //then if all is well, print everything out
      
    echo($result['user_id']."#".$result['briefing_id']."#".$result['date_loaded']."<br />" );

    Once again, I hope this helps
    - website

  5. #5
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks for that Website, I'll give it a go today `
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS


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
  •