Hi,
   I wonder if anyone has come across this before, I am trying to create a script that tracks where people have clicked to your website from. I have seen the links with

.php?source=thiswebsite,
or
.php?source=anothersite

and wondered how you would create a script to increment it so it would be entered into the database (I am using MySQL) and then you could check it to see where most of your customers are clicking from. This would be handy to know as I would like to know what customers are coming from sites with my link on. I could change the links on sites that I have created to reflect this change.

Thanks
Sami

Well, you'll need a table with at least two columns -- one for the source site and one for the hit count for that site.

The easiest way to do it is to update the counter for the site. If the update query affects zero rows, then you can infer that the source site doesn't exist in the database and you can insert the row.

$query = "UPDATE hits SET count=count+1 WHERE site='{$_GET['source']}";
$result = mysql_query($query);

if (0 == mysql_affected_rows($result)) // source site not in table yet
{
   $query = "INSERT INTO hits (site, count) VALUES ('{$_GET['source']}', 1)";
   $result = mysql_query($query);
}


I leave it to you to add error/validity checks, but the above would get it done assuming there are no errors.



Take care,

Nik
http://www.bigaction.org/

Oh, and you can figure out which sites are referring the most people by sorting the hit count in a descending (highest first) order:

$query = "SELECT site FROM hits ORDER BY count DESC";




Take care,

Nik
http://www.bigaction.org/

Thankyou very much for the information, it is exactly what I wanted. I had tried many combinations but had not come up with the correct formula.
Thanks
Sami

Hi,
I have got half of the code working, there was a missing ' on $query line in your code, but got that sorted:

  if(isset($HTTP_GET_VARS['source']))
  {

    $query = "UPDATE hits SET count=count+1 WHERE site='{$HTTP_GET_VARS['source']}'";

    $result = mysql_query($query);
   }

This works great(had to use $HTTP_GET_VARS as host running 4.06), but when I try and execute the remainder of the code:

   if (0 == mysql_affected_rows($result)) // source site not in table yet
   {
     $query = "INSERT INTO hits (site, count) VALUES ('{$_GET['source']}', 1)";
     $result = mysql_query($query);
   }
I get an error on this line:

   if (0 == mysql_affected_rows($result))
of
Supplied argument is not a valid MySQL-Link resource?

So, I got it to update the counter if it already existed in the DB, but cant get it to insert it as a new record/entry if it doesn't.
Hope you can help, thanks for your assistance so far
Sami

Since you have no choice with your current server you can design a bit of code to convert the $HTTP_*_VARS to the new superglobal names which will preserve portability.

You can use this with or without the conditional statement. In PHP 5.0 I've read that the long names are going to be disabled. So it would be a good idea to keep version portability in mind. The only difference that you're going to notice with this method is that the recreations here are not auto-globals, or superglobals, which means that you still have to observe normal variable scoping rules, which should be no matter for you since the old long names are not superglobals either.

If you have any way of auto-prepending the code that would work best. If your ISP allows .htaccess, or httpd.conf that value can be set there using the directive:
auto_prepend_file = "filepath"

More information can be found at the manual page for the ini_set() function.
http://www.php.net/ini_set

http://www.php.net/manual/en/languag...predefined.php

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

And also to help you track down your problem, insert the following after preforming a query.

if (empty($result))
{
    echo mysql_error().": ";
    echo mysql_errno()."<br />\n";
}

This will tell you where mysql is choking.

Database wrapper functions are actually fabulous for this sort of thing, if you have a large project.

example call:
$result = query("SELECT * FROM my_table", $link, __LINE__, __FILE__);

This kind of writing just helps to reduce redundancy, and I might add that the error reporting there is only reccommended for inclusion in a development enviornment. Printing out your entire query including line number and file name for the user to see would, well, be bad! But can be enormously helpful when developing. Personally, I define a constant for using my custom error_reporting so that I can turn it off in a live env. But that's just my 2 cents!

hth,
: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

Hi,
I finally got this one working, the problem is that the

"mysql_affected_rows() works with connection handles returned by mysql_connect() or mysql_pconnect(),
not with result handles returned by mysql_query() or mysql_db_query()"

quote from http://uk2.php.net/manual/en/functio...ected-rows.php

so if I change:
    if (0 == mysql_affected_rows($result))
to
    if (mysql_affected_rows()==0)

....it works, I could put in the (link_id) from my mysql_connect but will will work ok without it.

Thanks for the input quesadilla, I am still working on the addition info you passed to me ie. the $_GET conversion.

sami

Ah crap, sorry about that.

Two more comments, though:

1: Rich, I don't think it's a good idea to echo ANYTHING out to the client within a wrapper function. You should set an error, or call a debug logging function that handles debug output. This debug function lets you, surprise surprise, wrap how debug statements are handled... you can swap in a function body that suppresses these output messages, only outputs them if a certain user (or user type) is logged in, write these messages to file, email them to an administrator, etc...

2: You should use version_compare() to compare the version string returned by php_version(). One caveat: version_compare isn't available in 4.0.6!! (lame, huh)? SO here's the all-encompassing way to do it:

if (!function_exists('version_compare') ||
     version_compare(php_version(), '4.1.0', '<'))
{
    // version is pre-4.1.0, copy into fake superglobals...
}


Take care,

Nik
http://www.bigaction.org/

Nikolai.. let's not break my balls here.

OK, in the wrapper functions that I have designed and used personally, I DON'T echo anything directly to the client and I do exactly that. This is why I suggested using a constant to toggle error reporting. I *assumed* that sami was intelligent enough to heed my warnings about error output and take the idea a step further to include some sort of scheme to prevent the average user from seeing any error output, to write that output to a log, or do whatever with it...

Here is a modified version which does this:

So here we have the creation of a constant... my applications flow through one central file so in my case I only need to set this once at the top of the hierarchy. You can also use a .htaccess file, as I suggested in my last post, to set the error_reporting directive of php.ini, and turn it off or change it to log errors, as well as use a PHP function to determine whether *that* value is on/off and have your custom error reporting toggle based on its value. An example would be this:

if (ini_get('display_errors') == 1)
{
// display errors...
}

But that all depends on whether your ISP even allows that. Most do.

define("ERROR_BOOLEAN", true);

function query($query, $link = $GLOBALS["link"], $line = null, $file = null, $js_alert = false)
{
    $result = mysql_query($query, $link);

    if (empty($result))
    {
        $error = mysql_error().": ";
        $error .= mysql_errno()."\n";
        $error .= $query."\n";
        if (!empty($line)) $error .= "@ Line: $line";
        if (!empty($file)) $error .= " in File: $file\n\n";

        error($error, $js_alert);
    }

    return $result;
}

Personally I take all that error garbage in the middle there and put it through a custom error function which checks the value of the constant has options for file logging, formatting for the screen and/or a Javascript alert via window.attachEvent.

function error($error, $js_alert = false)
{

    # If error reporting is enabled or the user is admin output the error
    if (ERROR_BOOLEAN == true || $_SESSION["user_id"] == 1)
    {
        if ($js_alert == false)

            echo "<div style='color: red'>".nl2br($error)."</div>";

        else

            echo "<script type='text/javascript'>window.attachEvent('onload', alert('$error'));</script>";

    }

    else
    {
        # do a plain text log here, or log into a database table
    }
}

And there you have it. The Cadillac of query wrapper functions, IMHO. Well even that can be expanded upon. You can design levels of error severity, write the program to email you if a certain severity level has been reached. Debugging never ends.

Thanks for pointing this out. The idea that I got came from the user-contributed notes for the predefined variables page.

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::