I need to make a database that has rows of IP addresses and columns of number of times that IP address has come to page2.php during the day. I need it to count every time they go to the page, regardless of time or whatever. The purpose is so that any IP address can only go to the page2.php a certain number of times a day.

Here's my psuedocode so far:

Incrementation:
Reads the IP address upon entry to page2.php, searches the database to see if that IP address has already been to page2.php

if so, increment the count in the column corresponding with the current day (today) as well as filling all previous columns with zeroes/nulls

if not, add a new row and increment the column for the corresponding day (today)

Updating the date:
Create a updatedate.php file, have it create a new column for the table created by page2.php and put zeroes in all of the rows, and tell page2.php to start incrementing in the new column.

Have updatedate.php run every 24 hours.

My problems/questions:
Getting the updatedate.php to run every 24 hours automatically. (I've seen cron / crontab, but I don't think I will have access to those on the server I am hosting with)

page2.php knowing which column to increment the count in. (Is there a mySQL query that will go to the end of a row? or maybe I will have a little table with a variable that tells page2.php which column to increment in)

I haven't really worked with mySQL much, but I'm here to learn. Also, if you think this isn't the way I should be making the daily IP logger, tell me what would work better.

Thank you in advance for you help!

    You need to re-think how you are storing this data. You approach is all wrong and will end up causing you more headaches.

    I'm going to assume that you want to know how many times an IP address (you know dialup users and some DSL users get there IP address changed very regularly don't you?) has logged in today and all previous days. To make this happen you will need 2 tables:

    IPAddresses
-----------
IPAddress_id int not null auto_increment primary key
IPAddress varchar(15)

Connections
-----------
Connection_id int not null auto_increment primary key
IPAddress_id int not null
day date
connections int

    Now using these 2 tables your psudo code would be something like this:

    Check for incomming IP address in IPAddresses table, by selecting from the IPAddresses table.

    • If IPAddress Doesn't Exist -
      Insert new row into IPAddresses for new IPAddress
      Get newly inserted IPAddress_id
      Insert new row into Connections using IPAddress_id and having todays date and a connections of 1

    Select everything from Connections where IPAddress_id matches the IPAddress_id in the IPAddresses table for this IP Address and the day = today.

    • If Connection Row Exists -
      Check if the connections field is greater than your threshold for diaplaying this page.

    -- If Connections are greater than your threshold --
    Display page telling user they have surpassed there viewing limit for today.

    -- If Connections are less than your threshold --
    Update the Connections table by adding 1 to the connections field. Display page.

    • If Connection Row Doesn't Exist -
      Insert new row into Connections using the IPAddress_id you got at the very beginning and todays date and a 1 for the connections. Display your page.

    ========================================
    Sorry about the formatting. You should be able to figure out how the nesting works. Using 2 tables like this means that you don't need a cron job since the page will take care of everything for you.

    If this is the first time an given IP Address is visiting this page the script will create a record for them and count this Connection.

    If this is the first visit for today then the script will generate the appropriate Connections record and count the visit.

    If they have visited before today and they haven't surpassed the threshold then they get to see the page and there connection count goes up.

    If they have visited today and surpassed the threshold then they are show the sorry connections surpassed page and can't view this page anymore today.

      It should be noted that IP addresses are NOT reliable enough to dis-allow visitors to view your website more than once per day.

      You definitally DO NOT want to be adding columns to your database all the time... column counts should be static. You'll seriously muck up the database if you do this.

      If you can't use Crontab, think about either scheduling a browser on your home computer to open up that page once a day to run the script... or do a check for the date on every page load:

      <?php

$fp = fopen ("the_dat_it_might_be.txt", "r");
$contents = fread ($fp, filesize ("the_date_it_might_be.txt"));
fclose ($fp);

if ($contents == date ("Y-m-d")) {

// do nothing, its the same day still

} else {

// run all the stuff you need to run every 24 hours
// update file "the_date_it_might_be.txt" to the current date

}

?>

        Why use a database at all. Just drop a cookie on the client. Check for the cookie and update it's value each time the client accesses the web page. Once they reach their threshold, reject them.

        The problem with IP addresses is that many users within a company may share the same external address via NAT. If sales.Bill exceeds the threshold by lunch, accounting.Judy won't be able to access the page for the remainder of the day.

        The problem with the cookie approach is that more advanced users will figure out you are using a cookie and will clear it from their browser once they reach the threshold. I doubt this would be a wide-scale problem. If it becomes a problem (you could tell by checking your web server's access logs), then secure the page with a login/password or reject IPs that appear to be abusing your policy by deleting their cookies.

          As you can see there are many solutions to your problem. Personally I would institute a login approach. Make your users register to view the page and then just store the users daily access counts in a database. As you can see I don't agree with superwormy, a database is designed to hold your data, and it's designed to be dynamic not static. Adding and removing rows from your database won't screw it up unless you have a very cheesy database, but if you are using any of the standards like MySQL or PostgreSQL you'll be fine, and they even have tools that you can run on a regular/semi-regular basis to help clean up any errors that might happen.

          Now if you don't institute a login style access, you cando what freedomics suggested and use cookies, but I believe that he outlined the major drawbacks to that, and to using the IP Addresses.

            Write a Reply...