Help me optimise my query please!

[adzeds]

Ok. The gist:
I am writing a small app that collects webstats. I am focusing on the bit that works out return visits by judging if 30 minutes past since they last accessed a page.

I am searching the database for distinct IP address, then comparing the time field to their last accessed time. I have a field that stores a DATETIME of access and also one for DATE and TIME broken down individually.

Feel free to ask any questions, here is my code (it is probs awful)

//return visits
        $Query = "SELECT DISTINCT ipaddress from analytics";
        $Result = mysql_query( $Query );
        $num = mysql_num_rows($Result);
        $uniques = mysql_num_rows($Result);
        $return[1] = array("title" => "Unique Visits", "value" => "$uniques");    
        
        $visitcount = 0;
                
        $i=0;
        while ($i < $num) {
        $ipaddress[] = mysql_result($Result,$i,"ipaddress");
        $i++;
        }
        
        $a=0;
        while ($a < $num) {
        $Query = "SELECT time from analytics WHERE ipaddress = '" . $ipaddress[$a] . "'";
        $Result = mysql_query( $Query );
        $num2 = mysql_num_rows($Result);
        
            $b = 0;
            while ($b < $num2) {
            $accessed = mysql_result($Result,$b,"time");
                
            
            
            if (isset($lastaccessed)) 
            {
                $temptime = strtotime("30 minutes", $lastaccessed);
                if($accessed > $temptime) 
                {
                $visitcount++;
                $pagesvisited[$b]++;
                } else {
                $pagesvisited[$b]++;
                }
                
            }
            else     
            {
            $lastaccessed = $accessed;
            $visitcount++;
            $pagesvisited[$b]++;
            } 
            $b++;
            }
            
        $a++;
        }
        
        $return[2] = array("title" => "Visits", "value" => "$visitcount"); 

[WingedPanther]

What is the purpose of having both a datetime and distinct date and time fields?

[adzeds]

It just made things a little easier for querying.

Am I doing this wrong?

[Orjan]

you have built in functions in MySQL to return the time or the date from a datetime field, that can be used easier than doublestore like this, they are time() and date().

MySQL has very good support to perform many different tasks for dates and times built in, see MySQL :: MySQL 5.0 Reference Manual :: 11.6 Date and Time Functions for a list and explanation.

I guess your php-part in bottom can be replaced with a single SQL query
I haven't tested it, but something like this should do the job:

SELECT count(ipaddress) AS visits FROM analytics WHERE ipaddress='$ip' AND
time(now()) < timeadd(time(`timefield`), "00:30:00"))