A couple of general comments on my approach to databases. Note it should all be taken with a grain of salt because I'm no DBA.

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]

In reply to Re: MySQL logic by monarch
in thread MySQL logic by sulfericacid

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.