Ignoring whether this is the best way to log spider accesses, let's look at the database portion. You really don't want to have a table creation be part of the cgi logging access--you don't want to create the tables at every request! Create a separate 'installation' script that creates the tables once. Next, notice how all your tables look the same? This is a sure sign that there's a problem! Whether you want to call it refactoring or normalization, we need to avoid 'cut-n-paste' code. Consider that which agent/robot hits the cgi is just another piece of data, like so: 

     id        int      NOT NULL auto_increment,
     useragent char(32) NOT NULL,
     ip        char(32) NOT NULL,
     date      char(32) NOT NULL,
     useragent char(32),              -- added field for ua
     primary key  (id)

[download]

Now we have 1 table. In order to hold only the last 10 hits from each different user agent, we'll need to get a little clever (or stupid depending on your viewpoint) with our updates. First, I think we should add another column to track the order of the requests for each ua. (We could achieve the result using the date column, but I think it's clearer for the SQL uninitiated to add a column and avoid the date handling.) 

     ...
     countdown  int  default(0),
     ...

[download]

Now when we add a new row, it will get a countdown value of 0. Every insert, we increment the countdown for that ua. Then, delete any entry with a countdown > 10. 

    UPDATE Table set countdown = countdown + 1 where useragent = ?;

    DELETE FROM Table WHERE countdown > 10;

[download]

That's a enough of a start for you!

--Solo

--
You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.

In reply to Re: newbie table structure by Solo
in thread newbie table structure by coldfingertips

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.