Add row to custom database Table and delete all rows older than 1 day

Question

I'm editing a view counter plugin so that it logs the IP of every user that viewed a post. This is to prevent them from refreshing the page and thus creating artificial views.

The part I'm having trouble with is adding rows to the database table, and afterwards removing any rows that are older than an hour. I'm sure I'm doing something wrong but I can't figure out what. The Wordpress documentation on this is very confusing to me.

This is my database creation function

### Create Database for IP Logging
function postviews_create_table () {
    global $wpdb;

    $table_name = $wpdb->prefix . "postviews_ips";

$sql = "CREATE TABLE $table_name (
    postid MEDIUMINT NOT NULL,
    ip VARCHAR(15) NOT NULL,
    time DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL
);";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}

And this is the function that's supposed to add new rows and delete old ones.

### Add user IP to Database & Delete Entries Older Than 1 Day
function postviews_add_ip () {
    global $post;
        $id = $post->ID;
        $ip = $_SERVER['REMOTE_ADDR'];
        $time = date("Y-m-d H:i:s");

    $wpdb->insert( 
        'wp_postviews_ips', 
        array( 
            'postid' => $id,
            'ip' => $ip,
            'time' => $time
        )
        array(
            '%d',
            '%s', 
            '%d'
        ) 
    );

    $sql = "DELETE FROM wp_postviews_ips WHERE time_created < (NOW() - INTERVAL 1 DAY)";

    $wpdb->query($sql);
}

Answer 1

You have got a few things mixed up.

First, your query checks for time_created, which does not exist in your table.

Second, you try to insert the date as a integer using %d. You may have to treat it as a string.

Finally, you have to set $wpdb to global in your function

function postviews_add_ip () {
    global $post, $wpdb; // Database set to global
        $id = $post->ID;
        $ip = $_SERVER['REMOTE_ADDR'];
        $time = date("Y-m-d H:i:s");

    $wpdb->insert( 
        'wp_postviews_ips', 
        array( 
            'postid' => $id,
            'ip' => $ip,
            'time' => $time
        ), // added a colon
        array(
            '%d',
            '%s', 
            '%s' // treat it as a string
        ) 
    );

    $sql = "DELETE FROM wp_postviews_ips WHERE time < (NOW() - INTERVAL 1 DAY)"; //changed rowname

    $wpdb->query($sql);
}

Placement of the code and structure

I would strongly recommend to divide the functions of insertion and deletion. You could use wp-cron for deletion, so you do not have to call this function all the time. The function itself should work this way - but please be sure to optimze it :)