Need help with my database

Richardknox · #1 15-Jan-2007, 16:26

I am keeping track of visitors, their IP address, the last date visited, and the total number of times they have visited, in a Mysql database.

I have two parts to my script. One part is supposed to check to see if they have previously visited the site, and if so, update their date and number of visits.

If the first part of the script does not locate them inside the database, then the other part of the script is supposed to add them to the database, set their visits at one, with todays date.

I keep having problems with the script adding the visitor info multiple times, while its scrolling through the database. I have been struggling with this the last several days, multiple interations, and still no good.
Any help would be greatfull.

PHP Code:


<?
// visitor.php
include "db_connection.php";
?>
<?
$user_ip = getenv("REMOTE_ADDR");
$bin_ip = ip2long($user_ip);
$today=date("Y-m-d");

$query = "SELECT * FROM wcvisitor_ip";
$result = mysql_query($query);
$row=mysql_fetch_row($result);

while ($row=mysql_fetch_row($result))
{
    if ($row[0]==($bin_ip))
     {
              if ($row[2]==($today))
            {
            
            }
            else
            {
            $query = "UPDATE wcvisitor_ip SET lastdate = '$today', visits = visits+1 WHERE ip_bin = $bin_ip";
            mysql_query($query);
             }
      }
    else    
    {     
            $sql="INSERT INTO wcvisitor_ip (ip_bin, ip,lastdate) VALUES('$bin_ip', '$user_ip', NOW())";
            mysql_query($sql);
     }    
}
?>

JdS · #2 15-Jan-2007, 22:06

I won't comment on the logic of what you're trying to do with this script, I will assume this is just an experiment.

ip2long - I haven't used this before, and now looking at the manual again, I remember why.

Something from the manual, about this function:

Quote:

Note: Because PHP's integer type is signed, and many IP addresses will result in negative integers, you need to use the "%u" formatter of sprintf() or printf() to get the string representation of the unsigned IP address.

Your first SQL query. You are fetching ALL the rows? Just to find ONE that matches? I think it's a waste of processing power and memory space. As your database grows - and it will - this script will not scale well.

Instead, you should allow MySQL to handle the 'matching' since it is designed to do that really (REALLY) well.

I would suggest something like this:

PHP Code:


$searchIP = mysql_escape_string( $_SERVER['REMOTE_ADDR'] );

$sql = "SELECT `lastdate` FROM `wcvisitor_ip` WHERE `ip_bin`=INET_ATON('$searchIP')";

if( $resource=@mysql_unbuffered_query($sql, $connection) )
{
    $sql = false;
    
    if( $row=@mysql_fetch_row($resource) )
    {
        if( $row[0] != date('Y-m-d') )
        {
            // update.
            $sql =    "UPDATE
                        `wcvisitor_ip`
                    SET
                        `lastdate`=CURDATE(),
                        `visits`=`visits`+1
                    WHERE
                        `ip_bin`=INET_ATON('$searchIP')";
        }
    }
    else
    {
        // insert.
        $sql =    "INSERT INTO
                    `wcvisitor_ip`
                    ( `ip_bin`, `ip`, `lastdate`, `visits` )
                    VALUES
                    ( INET_ATON('$searchIP'), '$searchIP', CURDATE(), 1 )";
    }
    
    if( $sql )
    {
        @mysql_unbuffered_query( $sql, $connection )
            or die( mysql_errno($connection) . ' - ' . mysql_error($connection) );
    }
}
else
{
    trigger_error( "Cannot add/update visitor data to `wcvisitor_ip`; SQL query error; searchIP=$searchIP", E_USER_NOTICE );
}

Please note that I am typing this without testing it. I don't have the (db) table and data to run it. Expect some (syntax) errors and typos.

Richardknox · #3 17-Jan-2007, 11:24

Thanks, worked first time, right out of the box.

I am self teaching myself MySql & PHP. So, I have difficulties trying to make certain things work, and that due to just lack of knowledge.

I do appreceate your help. This script helps me keep track of visitors, where they hail from, and the number of times they stop by.

Thanks.
Richard

admin · #4 18-Jan-2007, 00:29

I understand, and I appreciate the feedback.

Richardknox · #5 30-Jan-2007, 16:15

I need help with a part of the above mentioned script.

This is part of the above mentioned script.

if( $row[0] != date('Y-m-d')

How does the ' $row[0] ' part of the script work. I have been searching, and have not had any luck finds any reference for this.

Thanks,
Richard

JdS · #6 31-Jan-2007, 17:52

I will try to explain. If I don't do a good job, please let me know.

Because the SQL was

PHP Code:


$sql = "SELECT `lastdate` FROM ..."

$row[0] is the data (value) of `lastdate`.

If the SQL was

PHP Code:


$sql = "SELECT `some_other_column_name`, `lastdate` FROM ..."

Then the data (value) of `lastdate` would be inside $row[1].

$row is a numerically indexed array because of the mysql_fetch_row() function used in the example script. If we had used mysql_fetch_assoc(), then the line you asked about would look like this:

PHP Code:


if( $row['lastdate'] != date('Y-m-d') )
{

Read up on the following (3) functions, if you haven't already. They do the same thing basically...

mysql_fetch_row()
mysql_fetch_assoc()
mysql_fetch_array()

Richardknox · #7 12-Feb-2007, 18:52

I now have another question concerning the script that you showed me. I am using the script, in the same form that you originally wrote. The only trouble that I am having, is every now and then I get duplicates in the database for the same IP address. Instead of updating the count associated with the IP address in the table, it inserts a new row.

I have been looking over the script, and unsure why its doing this.

I am uploading a jpg of the table listing, and another copy of the script again:

PHP Code:


<?
// visitor.php
include "db_connection.php";
?>
<?

$dblink = mysql_connect($dbhostname,$dbusername, $dbpassword) OR DIE ("Unable to connect to database! Please try again later.");
mysql_select_db($dbname, $dblink);

$query = "SELECT * FROM wcvisitor_ip";
$result = mysql_query($query);
$row=mysql_fetch_row($result);

$searchIP = mysql_escape_string( $_SERVER['REMOTE_ADDR'] );

$sql = "SELECT `lastdate` FROM `wcvisitor_ip` WHERE `ip_bin`=INET_ATON('$searchIP')";

if( $resource=@mysql_unbuffered_query($sql, $dblink) )
{
    $sql = false;
    
    if( $row=@mysql_fetch_row($resource) )
    {
        if( $row[0] != date('Y-m-d') )
        {
            // update.
            $sql =    "UPDATE
                        `wcvisitor_ip`
                    SET
                        `lastdate`=CURDATE(),
                        `visits`=`visits`+1
                    WHERE
                        `ip_bin`=INET_ATON('$searchIP')";
                        
        }
    }
    else
    {
        // insert.
        $sql =    "INSERT INTO
                    `wcvisitor_ip`
                    ( `ip_bin`, `ip`, `lastdate`, `visits` )
                    VALUES
                    ( INET_ATON('$searchIP'), '$searchIP', CURDATE(), 1 )";
                    
    }
    
    if( $sql )
    {
        @mysql_unbuffered_query( $sql, $dblink )
            or die( mysql_errno($dblink) . ' - ' . mysql_error($dblink) );
    }
}
else
{
    trigger_error( "Cannot add/update visitor data to `wcvisitor_ip`; SQL query error; searchIP=$searchIP", E_USER_NOTICE );
} 
?>

admin · #8 13-Feb-2007, 07:45

I can't see anything wrong with the script. The only way an "INSERT" will happen is when a matching IP is not found. That should prevent duplicates.

Also, I am amazed that your (db) table actually let the "INSERT" happen at all! This is a symptom of a table that is not created very well. I would expect that the `ip_bin` column would be created as a PRIMARY KEY, refusing duplicates in any case.

Describe the (db) table...

And, why is this bit (see comments) still in the script?

PHP Code:


<?
// visitor.php
include "db_connection.php";
?>
<?

$dblink = mysql_connect($dbhostname,$dbusername, $dbpassword) OR DIE ("Unable to connect to database! Please try again later.");
mysql_select_db($dbname, $dblink);

///////// What is this? //////////////
$query = "SELECT * FROM wcvisitor_ip";
$result = mysql_query($query);
$row=mysql_fetch_row($result);
///////// What is this? //////////////

Richardknox · #9 13-Feb-2007, 13:03

Actually there is no primary key for this table. I guess thats my problem.

Here is a picture of what the table looks like.

Richardknox · #10 13-Feb-2007, 13:10

PHP Code:


<?
// visitor.php
include "db_connection.php";
?>
<?

$dblink = mysql_connect($dbhostname,$dbusername, $dbpassword) OR DIE ("Unable to connect to database! Please try again later.");
mysql_select_db($dbname, $dblink);

///////// What is this? //////////////
$query = "SELECT * FROM wcvisitor_ip";
$result = mysql_query($query);
$row=mysql_fetch_row($result);
///////// What is this? //////////////

Actually, that was part of the origional script, and I just didn't remove it. It's not affecting the script, so I guess I can just delete it now.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help with Classes and database arrays	brookeville	C++ Forum	1	13-Apr-2005 00:26
Limit combo box and date time picker choice according to database created in folder	shinyhui	C++ Forum	0	22-Feb-2005 20:16
Limit combo box and date time picker choice according to database created in folder	shinyhui	MS Visual C++ / MFC Forum	0	22-Feb-2005 02:13
Limit combo box choice according to database created in folder	shinyhui	MS Visual C++ / MFC Forum	3	21-Feb-2005 21:58
cgi txt database to mysql	erhanharputlu	MySQL / PHP Forum	0	27-Sep-2004 01:30

Need help with my database

Re: Need help with my database

Re: Need help with my database

Re: Need help with my database

Re: Need help with my database

Re: Need help with my database

Re: Need help with my database

Re: Need help with my database

Re: Need help with my database

Re: Need help with my database