Three tables I think would work:
affiliates
ID int(11) NOT NULL auto_increment,
AffiliateName varchar(75) NOT NULL,
AffiliateAddress varchar(255) NOT NULL,
AffiliateUser varchar(30) NOT NULL,
AffiliatePass varchar(255) NOT NULL
stats
ID int(11) NOT NULL auto_increment,
AffiliateID int(11) NOT NULL,
Hits int(11) default '0'
Hits
ID int(11) NOT NULL auto_increment,
AffiliateID int(11) NOT NULL,
TimeDate int(11) NOT NULL default '0'
Then you can see when/where the hits were, and you can have a grand total of hits....
or just do it with two tables (not the stats table) and use a good query to get only the ones you want 😉
2-Table Setup:
--
-- Create the table affiliates
-- Will hold affiliate info
--
CREATE TABLE `affiliates` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(75) NOT NULL,
`address` varchar(255) NOT NULL,
`user` varchar(30) NOT NULL,
`pass` varchar(255) NOT NULL,
`details` mediumtext NOT NULL,
PRIMARY KEY (id)
) Type=MyISAM;
--
-- Hits table creation
-- Holds all the "hits" received...
--
CREATE TABLE `hits` (
`id` int(11) unsigned NOT NULL auto_increment,
`affiliateid` int(11) unsigned NOT NULL,
`timedate` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) Type=MyISAM;
INSERT INTO `affiliates` (name, address, user, pass, details)
VALUES ('$name', '$address', '$user', '$pass', '$details');
--
-- Get some information from the tables
--
-- Total hits this year:
--
SELECT COUNT(*) AS hits
FROM hits;
-- Total hits for a specific Affiliate:
--
SELECT COUNT(*) AS hits
FROM hits
WHERE affiliateid = '$affiliateID';
-- Total hits for a specific month:
--
-- $month = 'July'
SELECT COUNT(*) AS hits
FROM hits
WHERE FROM_UNIXTIME(timedate, '%M') = '$month';
-- Total hits for a specific month from a specific affiliate:
--
-- $month = 'July'
SELECT COUNT(*) AS hits
FROM hits
WHERE affiliateid = '$affiliateid'
AND FROM_UNIXTIME(timedate, '%M') = '$month';
-- Affiliate information, AND how many hits overall:
--
SELECT a.name, a.address, a.details,
COUNT(h.*) AS hits
FROM affiliates AS a
INNER JOIN hits AS h
ON a.id = h.affiliateid
WHERE a.id = '$affiliateid'
-- Affiliate Info, AND how many hits for specific month:
--
-- $month = 'July'
SELECT a.name, a.address, a.details,
COUNT(h.*) AS hits
FROM affiliates AS a
INNER JOIN hits AS h
ON a.id = h.affiliateid
WHERE a.id = '$affiliateid'
AND FROM_UNIXTIME(timedate, '%M') = '$month';
There are plenty more queries that could be run, and all, but these are the basics of what you'd probably want.
