Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jun 2012
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile tracking visitors

    I am looking to keep track of my visitors through a table.
    The simple script below works fine as is.
    What I need to do though is, use the "hits" column for what it is, per each unique IP.
    Not create a new row per visit.

    Can anyone show me a way of how to do this?
    I'm thinking of using a while loop and having it check the "names" column for the existing IP, then create a new row only if ithe IP is new.



    PHP Code:


    $result 
    mysql_query("SELECT * FROM counter1"$link);
    $num_rows mysql_num_rows($result);

    $ip=$_SERVER['REMOTE_ADDR'];

    mysql_query("INSERT INTO counter1 
    (name, hits) VALUES('$ip', '1' ) "

    or die(
    mysql_error());  

    echo 
    "$num_rows Rows\n"

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Why bother? Just make the name a primary key and use an ON DUPLICATE KEY UPDATE syntax to increment the hits when a match is found. No need to select or anything.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Regular Coder
    Join Date
    Jun 2012
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks for the info.

    PHP Code:
    $result mysql_query("SELECT * FROM counter1"$link);
    $num_rows mysql_num_rows($result);

    $ip=$_SERVER['REMOTE_ADDR'];

    $rows='1';
    $rows=(int)$rows;


    mysql_query("INSERT INTO counter1 (name,hits) VALUES ($ip,1)
    ON DUPLICATE KEY UPDATE hits=hits+1"

    or die(
    mysql_error()); 
    This returns an error message.
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.223.194,1) ON DUPLICATE KEY UPDATE hits=hits+1' at line 1.

    what am I missing?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    IP is a string, not a number. You need to wrap it in quotes.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    Regular Coder
    Join Date
    Jun 2012
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    mysql_query("INSERT INTO counter1 (name) VALUES ("$ip")
    ON DUPLICATE KEY UPDATE hits=hits+1"

    or die(
    mysql_error()); 
    This now returns parse error of "T_Variable".

  • #6
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    You must use backslashes (\) to "escape" the double quotes, or concatenate the variable with the beginning and end of the query.

    "INSERT INTO counter1 (name) VALUES (\"$ip\") ON DUPLICATE KEY UPDATE hits=hits+1"

    OR

    "INSERT INTO counter1 (name) VALUES ('" . $ip . "') ON DUPLICATE KEY UPDATE hits=hits+1"

    This is because the query is contained within the double quotes - PHP knows that the query string ends when it reaches the second, closing double quote. The backslash is the escape character which tells PHP to ignore the following character for purposes of syntax.

    In the second method, you hook together a string, a variable, and another string and combine them into one string.

    HTH
    Dan
    Last edited by whizard; 07-04-2013 at 09:34 PM.
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #7
    Regular Coder
    Join Date
    Jun 2012
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks whizard, that now works.
    for an existing IP.
    I now need to know how to add a new row for a new IP.

    PHP Code:
    $result mysql_query("SELECT * FROM counter1"$link);
    $num_rows mysql_num_rows($result);

    $ip=$_SERVER['REMOTE_ADDR'];

    $rows='1';
    $rows=(int)$rows;


    mysql_query("INSERT INTO counter1 (name) VALUES (' ".$ip." ')
    ON DUPLICATE KEY UPDATE hits=hits+1"

    or die(
    mysql_error()); 

  • #8
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    My *understanding* is that if there isn't a duplicate entry, the row will be inserted as normal, so that one query above should handle both situations.

    Is your `name` field UNIQUE? I think it may need to be. That being said, SQL is something I learn when I need to use it, not something I have mastered. Fou-Lu would be able to give a definitive answer.

    HTH
    Dan
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #9
    New Coder
    Join Date
    Jan 2011
    Posts
    85
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Well I've had the same issue, but I've solved it. It's best to change $ip to something like $ipadd because my issue was IP Addresses wouldn't be submitted into the database. The script itself works, but the IP Address wasn't stored. So I had to change my strings and it worked. I can try to replicate your issue and figure it out when I have time.

  • #10
    Regular Coder
    Join Date
    Jun 2012
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Whizard...it does in fact insert a new row automagically.

    Xiong...I understand. BASIC was picky about certain things being used for variable names even if it was an undocumented reserved word. So if I had that problem, I always like to place a character before the reserved word so that conflicts don't happen. Like "name" would become "aname" and "bname". Even in CSS, similar names that could be tags should be avoided. like id="frame". Make it "xframe" instead.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •