comment on

I am writing my first real MySQL script and just started learning it yesterday from Programming The Perl DBI book.

It was suggested by people in the CB that I post my MYSQL code to see what you monks think about it (as quoted from the cb "your data layout must be crappy").

So below is my setup.cgi script which sets up my tables and everything.

SCRIPT INFORMATION: just to make a fun (simple) project with MySQL I want to create a search engine bot notifier. When you include it on your index page it'll check the UserAgent of everyone/everything that visits the site. It logs the UserAgent, IP, and Time (along with having an id increment which maybe serves no real purpose as of yet-- I just thought it'd be fun).

Each search engine has their own TABLE. Google, Altavista, etc. each gets their own table. And when the script tracks the UserAgent, it inserts the data in the related table (to say if Googlebot is on your site, it'll insert the id, useragent, time and ip into the Google table).

Now what I want to do is create a LAST 10 USERAGENTS table. The last 10 known UserAgents from the list we have will be placed there. Just 10, no more. I don't want to read the last 10, I only want their to be a max of 10 in this table. That's what I need help with, also.

But maybe you wise monks know of a better way to do this. I'm up for suggestions but please don't throw advanced MySQL terms or shortcuts my way since I just started doing this yesterday.

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);

use warnings;
use strict;
use CGI qw/:standard/;

use DBI;

###################
# configurations
###################
my $dbase = "spyders_test";
my $mysql_user = "spyders_admin";
my $mysql_pass = "pass";

my $table1 = "table1";
###################
# end configurations
###################
print header;

my $dbh = DBI->connect("DBI:mysql:$dbase", $mysql_user, $mysql_pass) o
+r
print DBI->errstr;

print "Connection was made<p>";


##########
# Altavista
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS altavista
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Altavista table created.<br>"; }  

##########
# Google
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS google
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Google table created.<br>"; }  

##########
# Alexa
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS alexa
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Alexa table created.<br>"; }  

##########
# AllTheWeb
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS alltheweb
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "AllTheWeb table created.<br>"; }  

##########
# Entireweb
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS entireweb
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Entireweb table created.<br>"; }  

##########
# Excite
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS excite
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Excite table created.<br>"; }  

##########
# Galaxy
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS altavista
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Altavista table created.<br>"; }  

##########
# Euroseek
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS euroseek
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Euroseek table created.<br>"; }  

##########
# Infoseek
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS infoseek
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "InfoSeek table created.<br>"; }  

##########
# Lycos
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS lycos
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Lycos table created.<br>"; }  

##########
# MSN
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS msn
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "MSN table created.<br>"; }  

##########
# NothernLight
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS northernlight
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "NorthernLight table created.<br>"; }  

##########
# Teoma
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS teoma
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Teoma table created.<br>"; }  

##########
# JUNK TEST
##########

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS junk
    (
       id int auto_increment not null,
       useragent char(32) NOT NULL,
       ip char(32) NOT NULL,
       date char(32) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

if ($sth->errstr) { print $sth->errstr; }  
else {  print "Junk table created.<br>"; }  


$dbh->disconnect
or warn "Couldn't disconnect from database: $DBI::errstr";
exit;
[download]

2005-01-29 Janitored by Arunbear - added readmore tags, as per Monastery guidelines

In reply to newbie table structure by coldfingertips

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:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.