0

I'm creating an ad banner tracker for the ad banners on my website by using PHP. The tracker would track unique impressions, total impressions, unique clicks and total clicks of each banner.

The method I would use for detecting unique impressions/clicks is using IP. I'm thinking that the following four tables would be needed:

  • Banner - For storing information of each banner.
  • Unique Impression Information - Mainly for storing IPs of visitors who view the banners.
  • Unique Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of unique visitors who clicked the banners.
  • Total Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of every visitors who clicked the banners.

For storing the counts of impressions and clicks, I would use these four columns of the Banner table: "Unique Impression", "Total Impression", "Unique Click" and "Total Click".

The logic of counting impressions (or clicks) would be like:

  • If a visitor's IP is not found in the database, the IP would be stored in the database and the visitor would increase the values of both the Unique and Total Impression (or Click) columns of the Banner table by 1.
  • If a visitor's IP is found in the database, the visitor would only increase the value of the Total Impression (or Click) column of the Banner table again by 1.

I for now have no desire to know detailed information of visitors who only view but don't click the banners. That's why the visitor information stored in the Unique Impression Informaion table is only IP.

Advice on the efficiency of such a database design would be much appreciated.

== Update ==

The CREATE TABLE statements would be:

CREATE TABLE `Banner`
(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Unique Impression` int(15) NOT NULL,
    `Total Impression` int(15) NOT NULL,
    `Unique Click` int(15) NOT NULL,
    `Total Click` int(15) NOT NULL,
    /* Columns like `Name`, `Url`, `Src`, etc are omitted at here */
    PRIMARY KEY (ID)
);
/*
CREATE TABLE `Unique Impression IP` */ -- before changing table name
CREATE TABLE `Unique Impression Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    PRIMARY KEY (ID)
);
CREATE TABLE `Unique Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);
CREATE TABLE `Total Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);

As an example, the MySQL quies that will be used for detecting unique impressions would be:

$check_ip = mysqli_query($conn, "SELECT `IP`, `Banner ID` from `Unique Impression Information` WHERE `IP` = '$visitor_ip' AND `Banner ID` = '$banner_id'");

if (mysqli_num_rows($check_ip) === 0)
{
    mysqli_query($conn, "INSERT INTO `Unique Impression IP` (`IP`, `Banner ID`) VALUES ('$visitor_ip', '$banner_id')");
    mysqli_query($conn, "UPDATE `Banner` SET `Unique Impression` = `Unique Impression` + 1, `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}
else
{
    mysqli_query($conn, "UPDATE `Banner` SET `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}
  • 1
    insert on duplicate key update maybe the general form of a counting query. IPs aren't people either. Commonly attacked problem that probably has solution existing already. Also look up preventing SQL injection. Once other browser header fields are added you will be vulnerable. – danblack Dec 3 '19 at 23:43
  • 1
    The INSERT looks wrong. You check by banner and IP, but then insert only IP. It might be possible to fold those 4 SQLs into a single INSERT ... ON DUPLICATE KEY UPDATE ... That would be faster. In the 'SELECT', why test both ID and IP, when ID is known to be UNIQUE (because it is the PRIMARY KEY)? – Rick James Dec 4 '19 at 5:04
  • @RickJames Thanks for pointing out the wrong code. I just updated the CREATE TABLE `Unique Impression IP` and SQL queries. And thank you and @danblack for suggesting using INSERT ... ON DUPLICATE KEY UPDATE ... – Ian Y. Dec 4 '19 at 5:15
  • 1
    Caution when using INSERT IGNORE or IODKU: The next auto_inc value is grabbed before deciding whether to do an INSERT. That is, it will "burn" some ids. – Rick James Dec 4 '19 at 5:18
1

For Unique Impression IP as there is no need for a autoincrement primary key, use VARBINARY(16) for IP address, and as the primary key. Use INET6_ATON() when inserting it to keep it in a compact form.

Use:

INSERT IGNORE INTO `Unique Impression IP` VALUES (INET6_ATON($var))

And look at rows affected to see if that exists or not for your UPDATE Banner statement branch. As it's a unique key (primary keys are unique) it will only be inserted once. This removes a race condition in your current code.

|improve this answer|||||
  • Thanks for suggesting VARBINARY(16) and changing the primary key. I might need to check if INSERT IGNORE works for me as it can increase the auto increment value even when no record is inserted like @RickJames mentioned. – Ian Y. Dec 4 '19 at 7:42
  • 1
    Note I looked at avoiding an auto increment on the Unique Impression Information/IP table. Also for as a general rule, make auto_increment values unsigned - negative space is unusable anyway. Consider making them bigint too, it doesn't take much space to prepare this scaling up front. – danblack Dec 4 '19 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.