#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Netherlands, the
    Posts
    48
    Rep Power
    15

    Question Visits 'script', some feedback please? (learning still)


    No errors here. However, seeing how I am not very experienced yet, I am wondering if I could perhaps get some feedback on my code. Did I use excess code? Bad use of variables? Inefficient code? And so on. Any feedback at all would be greatly appreciated, as I'm very willing to learn and improve my skills more :)


    The visits 'script':
    PHP Code:
    ##########################################
    #        Visitor logging system.         #
    ##########################################
    #
    # MySQL table used:
    #
    # CREATE TABLE `visits` (
    #   `ip` VARCHAR( 15 ) NOT NULL ,
    #   `page` VARCHAR( 25 ) NOT NULL ,
    #   `timestamp` TIMESTAMP( 14 ) NOT NULL ,
    # );
    #
    #
    # Sets several variables:
    #
    #              Unique visitors so far:  $visits->unique
    #              Total amount of visits:  $visits->total
    #   Current amount of people browsing:  $visits->active
    #  Since when this has been monitored:  $visits->since
    #
    #
    # Actual code below.
    #

    # Log visit/pageview (if last was at least 30 seconds ago).
    #
    $select mysql_query("SELECT * FROM visits WHERE ip = '".$REMOTE_ADDR."' ORDER BY timestamp DESC");
    if (
    mysql_num_rows($select) > 0)
    {
      
    $result mysql_fetch_object($select);
      if (
    timediff($result->timestampdate("YmdHis")) > 30)
      {
        
    $insert mysql_query("INSERT INTO visits VALUES ('".$REMOTE_ADDR."','".$PHP_SELF."',now())");
      }
    } else { 
    $insert mysql_query("INSERT INTO visits VALUES ('".$REMOTE_ADDR."','".$PHP_SELF."',now())"); }


    # Count unique and total visits.
    #
    $select mysql_query("SELECT DISTINCT ip FROM visits");
    $visits->unique mysql_num_rows($select);
    $select mysql_query("SELECT * FROM visits");
    $visits->total mysql_num_rows($select);

    # Currently visiting (last 5 minutes).
    #
    $time date("YmdHis"mktime(date(H), date(m), date(s)-300date(m), date(d), date(y)));
    $select mysql_query("SELECT DISTINCT ip FROM visits WHERE timestamp >= '".$time."'");
    $visits->active mysql_num_rows($select);


    # Since when.
    #
    $select mysql_query("SELECT * FROM visits ORDER BY timestamp ASC limit 0,1"); $row mysql_fetch_object($select);
    $visits->since $row->timestamp
    And the function timediff();

    PHP Code:
    # Get the difference between 2 dates in seconds.
    # Input for $old & $new = 'yyyymmddhhmmss' 
    # (standard MySQL 'timestamp(14)' field format).
    #
    #          Example: datediff(20030606141000, 20030606141500);
    #    Should return: '600' (seconds)
    #
    function timediff($old,$new)
    {
      
    $y1 substr($new,0,4);  $y2 substr($old,0,4);
      
    $m1 substr($new,4,2);  $m2 substr($old,4,2);
      
    $d1 substr($new,6,2);  $d2 substr($old,6,2);
      
    $h1 substr($new,8,2);  $h2 substr($old,8,2);
      
    $i1 substr($new,10,2); $i2 substr($old,10,2);
      
    $s1 substr($new,12,2); $s2 substr($old,12,2);
      
    $new mktime($h1,$i1,$s1,$m1,$d1,$y1);
      
    $old mktime($h2,$i2,$s2,$m2,$d2,$y2);
      return 
    $new $old;

    Last edited by Alcohol; January 28th, 2004 at 03:25 AM.
  2. #2
  3. coding with style
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Location
    Washington
    Posts
    1,254
    Rep Power
    62

    QL


    Okay...

    1) each person has their own code syntax, but yours wasn't mine and thats why I changed it.

    2) use $_SERVER['REMOTE_ADDR'] instead of $REMOTE_ADDR. It is more universal. Same goes for PHP_SELF.

    3) don't be afraid to make MySQL do date calculations! Thats the perk of using timestamp fields.

    4) If you only need 1 row, then be sure to limit the query.

    5) "id" in your table should be a primary key and not just unique. It will speed up your queries. Also set it to auto-increment.

    6) Make MySQL count the number of results, not PHP. MySQL is optimized for it. It will also reduce the amount of memory your script uses.

    7) Why are you using an object to save your data? Why not use an array? They are more easily manipulated.

    8) Generally try to do as few queries as possible on a page. If you were to keep some of this information in a session, you could do these calculations based on that data rather than doing all those queries. You should save it in the database too, though, so that you know who is doing what.

    PHP Code:
    $select mysql_query("SELECT id, ip, page, timestamp,
                                  UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(timestampe) AS diff
                             FROM visits
                            WHERE ip = '"
    $_SERVER['REMOTE_ADDR'] ."'
                         ORDER BY timestamp DESC
                            LIMIT 1"
    );

    if (
    mysql_num_rows($select) > 0) {
        
        
    $result mysql_fetch_object($select);
        
        if (
    timediff($result->timestampdate("YmdHis")) > 30)
            
    $insert mysql_query("INSERT INTO visits
                                        VALUES ('"
    .  $_SERVER['REMOTE_ADDR']  ."','"$_SERVER['PHP_SELF'] ."', now())");
            
    } else {
        
    $insert mysql_query("INSERT INTO visits
                                    VALUES ('"
    $_SERVER['REMOTE_ADDR'] ."','"$_SERVER['PHP_SELF'] ."', now())"); }




    # Count unique and total visits.
    #
    $select mysql_query("SELECT COUNT(DISTINCT(IP))
                             FROM visits"
    );
    $results mysql_fetch_array($select);
    $visits->unique $select[0];

    $select mysql_query("SELECT COUNT(*)
                             FROM visits"
    );
    $results mysql_fetch_array($select);
    $visits->total $select[0];


    # Currently visiting (last 5 minutes).
    #
    $time date("YmdHis"mktime(date(H), date(m), date(s)-300date(m), date(d), date(y)));
    $select mysql_query("SELECT COUNT(DISTINCT(ip))
                             FROM visits
                            WHERE timestamp >= '"
    .$time."'");
    $results mysql_fetch_array($select);
    $visits->active $select[0];


    # Find out when their first visit was.
    #
    $select mysql_query("SELECT timestamp
                             FROM visits
                         ORDER BY timestamp ASC
                            LIMIT 1"
    );
    $row mysql_fetch_object($select);
    $visits->since $row->timestamp
    - Nycto
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Netherlands, the
    Posts
    48
    Rep Power
    15
    Everyone has their own syntax I suppose yes, I'm satisfied with mine though :)

    I wasn't aware thought that you could use count() over distinct if using () with distinct. I will take a further look into that, and also those $_SERVER variables and such (globals right?), thanks for pointing it out :)

    On a side note, in your first query, I noticed you use 'UNIX_TIMESTAMP':

    PHP Code:
    $select mysql_query("SELECT id, ip, page, timestamp,
                                  UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(timestampe) AS diff
                             FROM visits
                            WHERE ip = '"
    $_SERVER['REMOTE_ADDR'] ."'
                         ORDER BY timestamp DESC
                            LIMIT 1"
    ); 
    Could you perhaps elaborate as to what it does exactly, and what the result of this select will be? Thanks in advance :)

    Also, why are objects less easily manipulated compared to arrays? (I used both, and in the end found the $var->name thing to look neater, never really thought about the pros or cons though :P).


    PS. The ID field I mentioned there was in the first post, I took out since it was excessive. So please ignore that :)
    Last edited by Alcohol; January 28th, 2004 at 03:28 AM.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Netherlands, the
    Posts
    48
    Rep Power
    15
    Could perhaps anyone else explain what his query does exactly?
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Netherlands, the
    Posts
    48
    Rep Power
    15
    Erm, did this thread die? :/
  10. #6
  11. coding with style
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Location
    Washington
    Posts
    1,254
    Rep Power
    62
    Calm down. Patience is a virtue, lad.

    You can find info on the MySQL unix_timestamp function here:
    http://www.mysql.com/doc/en/Date_calculations.html

    Arrays are more easily manipulated because PHP supports a colossal list of array manipulating functions:
    http://www.php.net/manual/en/ref.array.php

    Objects may "look" cool, but that is not a good enough reason to use them

    You know what the funny part about me changing your query is? I forgot to change the rest of your code to incorporate the new query. Here... this should do it:

    PHP Code:
    $select mysql_query("SELECT id, ip, page, timestamp,
                                  UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(timestampe) AS diff
                             FROM visits
                            WHERE ip = '"
    $_SERVER['REMOTE_ADDR'] ."'
                         ORDER BY timestamp DESC
                            LIMIT 1"
    );

    if (
    mysql_num_rows($select) > 0) {
        
        
    $result mysql_fetch_array($select);
        
        if (
    $result['diff'] > 30)
            
    $insert mysql_query("INSERT INTO visits
                                        VALUES ('"
    .  $_SERVER['REMOTE_ADDR']  ."','"$_SERVER['PHP_SELF'] ."', now())");
            
    } else {
        
    $insert mysql_query("INSERT INTO visits
                                    VALUES ('"
    $_SERVER['REMOTE_ADDR'] ."','"$_SERVER['PHP_SELF'] ."', now())");

    The actual addition I made to the query simply calculates the time difference (in seconds) between that row and right now.
    - Nycto
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Netherlands, the
    Posts
    48
    Rep Power
    15
    I already read the MySQL manual, but I wasn't really sure how to utilize this function, so thanks bunches for your example. I think I get it now

IMN logo majestic logo threadwatch logo seochat tools logo