Second, USE PLACEHOLDERS! (I yell because it's been said so many times on perlmonks it's hard to believe you haven't seen it).

update and bind_columns should come before an execute, not after (my bad) (thought it's hard to tell what you are wanting to do with it). Also look up MySQL's replace command if you are going to be updating a row (no need to check first).

-sam

Second, it seesm you're doing this in real-time (i.e., while the request comes in), I'd say that's not necessary in general, you could just run a perl script periodically against your access log, which will make your web site faster.

First and most importantly I think about the database schema (or layout of the tables in my database) because if I don't do this right then the whole point of the project is lost.

Draw a diagram with rectangles.. each rectangle is a TABLE containing the FIELDS within that constitute one row in the table. In this case you have:

  |-USER------|
  | id        | (a unique identifier for the row)
  | views     | (count of how many pages user has viewed)
  | last_ip   | (the last IP address user used)
  | last_time | (the last time we saw the user)
  | url       | (the user's URL)
  |-----------|
[download]

I suggest the all_ip function is performed in a different table:

  |-ALLIP-----|
  | id        | (a unique identifier for the row)
  | user_id   | (the id of the user that used this IP)
  | ip        | (the IP address)
  | time      | (the time it was seen)
  |-----------|
[download]

Now, whenever you want to query the ALLIP table you can use a WHERE clause to bind the two tables together so you can reference the customer involved.

e.g. what IP addresses has a customer used?

my $sql = <<SQLCODE;
  SELECT
    a.ip
  FROM
    ALLIP a,
    USER u
  WHERE
    a.user_id = u.id AND
    u.id = ?
  GROUP BY
    a.ip
SQLCODE

my $sth = $dbh->prepare( $sql );
if ( $sth && $sth->execute( $user_id ) )
{
    while ( my $row = $sth->fetchrow_arrayref() )
    {
        print( "IP address " . $row->[0] . "\n" );
    }
    $sth->finish();
}
else
{
    die( "Database error " . $dbh->errstr . "\n" );
}
[download]

Now onto your question. How do you check for something and update it if it already exists or add it if it doesn't.. good question, it will be more verbose than you were hoping..

my $found_id = undef;
my $sql = <<SQLCODE
  SELECT id
  FROM track 
  WHERE url = ?
SQLCODE

my $sth = $dbh->prepare( $sql );
if ( $sth && $sth->execute( $url ) )
{
    if ( my $row = $sth->fetchrow_arrayref() )
    {
        $found_id = $row->[0];
    }
}
else
{
    die( "Database error " . $dbh->errstr . "\n" );
}

if ( $found_id )
{
    # perform update
    $sql = <<SQLCODE;
  UPDATE user SET
    view = view + 1,
    last_ip = ?,
    last_time = ?
  WHERE
    id = ?
SQLCODE

    my $sth = $dbh->prepare( $sql );
    if ( $sth && $sth->execute( $last_ip, time(), $found_id ) )
    {
        if ( my $row = $sth->fetchrow_arrayref() )
        {
            $found_id = $row->[0];
        }
    }
    else
    {
        die( "Database error " . $dbh->errstr . "\n" );
    }
}
else
{
    # perform insert
    # code left to read as an exercise
}
[download]

my $dbh = DBI->connect("DBI:mysql:$dbase", $mysql_user,
    $mysql_pass) or print DBI=>"errstr";
[download]

my $dbh = DBI->connect("DBI:mysql:$dbase", $mysql_user,
    $mysql_pass) or print DBI->errstr;
[download]