opening a page with the sole purpose of running an sql statement

[windandwaves]

Hi Folk

I am running a site (A) that has a US mirror (B). This means that all
the data from A is copied daily to B.

On the site, I keep some statistics of the pages people visit, tracking
them with a session and noting their searches, etc... for statistical
purposes.

The problem I have is that if someone visits site B then at the end of
the day, their statistical data is going to be overriden by the update
from A.

To counter this, I envisaged a system where the stats dont just run an
sql statement to update my MySql database, but instead, the statements
"include" a URL on A that runs the statement.

For example:

---
Old procedure:
function add_user_page() {
mysql_query("IN SERT INTO USER (PAGEID, USERID) VALUES (1,2);");
}

---
New procedure:
function add_user_page() {
if(site B) {

include_once("h ttp://A.com/runstats.php?fu nction=add_user _pageB&table=US ER&V1=8&V2=9") ;
}
mysql_query("IN SERT INTO USER (PAGEID, USERID) VALUES (8,9);");
}

---
I would then create a file runstats.php with the following code:

<?php
call_user_funct ion($_GET["function"], $_GET);
function add_user_pageB ($name, $v1, $v2) {
mysql_query("IN SERT INTO USER (PAGEID, USERID) VALUES
('.$v1.','.$v2. ');");
}
?>

This is obviously super simplified and not tested.

The questions I have are

1. is this a good method?
2. can I / should I use "include_on ce" or is there a better way to run
(call) the runstats.php page?

NOTE: my ISP does not allow me to connect directly from B to the
database on A.

Thanks in advance for any comments

Nicolaas

[petersprc@gmail.com]

Re: opening a page with the sole purpose of running an sql statement

If replication, or a federated table (MySQL), or a remote mysql
connection isn't possible, the remote URL would work. Another option
would be to use a tracking image (1x1 transparent gif) that pointed to
server A. A would then have access to the page being viewed through
HTTP_REFERER. This method allows keep-alive to be used.

For collecting site/page stats, you might be interested in Google
Analytics (google.com/analytics) which is free, or a service like
DeepMetrix.

Depending on the app, you might take a performance hit if you open a
remote URL with each request. To optimize this, you could continue
storing the stats locally on B (say in some new tables that don't get
overwritten), and then occasionally post a dump of those tables to
server A using cURL. Batching the updates instead of sending each
insertion.

As a basic precaution, you would probably want to check the remote IP
($_SERVER['REMOTE_ADDR']) before evaluating any code. There are
additional measures you could take security-wise.

On Oct 8, 8:39 pm, "windandwav es" <nfranc...@gmai l.comwrote:

Hi Folk
>
I am running a site (A) that has a US mirror (B). This means that all
the data from A is copied daily to B.
>
On the site, I keep some statistics of the pages people visit, tracking
them with a session and noting their searches, etc... for statistical
purposes.
>
The problem I have is that if someone visits site B then at the end of
the day, their statistical data is going to be overriden by the update
from A.
>
To counter this, I envisaged a system where the stats dont just run an
sql statement to update my MySql database, but instead, the statements
"include" a URL on A that runs the statement.
>
For example:
>
---
Old procedure:
function add_user_page() {
mysql_query("IN SERT INTO USER (PAGEID, USERID) VALUES (1,2);");
>
}---
New procedure:
function add_user_page() {
if(site B) {
>
include_once("h ttp://A.com/runstats.php?fu nction=add_user _pageB&table=US ER&V1=8&V2=9") ;
}
mysql_query("IN SERT INTO USER (PAGEID, USERID) VALUES (8,9);");
>
}---
I would then create a file runstats.php with the following code:
>
<?php
call_user_funct ion($_GET["function"], $_GET);
function add_user_pageB ($name, $v1, $v2) {
mysql_query("IN SERT INTO USER (PAGEID, USERID) VALUES
('.$v1.','.$v2. ');");}?>
>
This is obviously super simplified and not tested.
>
The questions I have are
>
1. is this a good method?
2. can I / should I use "include_on ce" or is there a better way to run
(call) the runstats.php page?
>
NOTE: my ISP does not allow me to connect directly from B to the
database on A.
>
Thanks in advance for any comments
>
Nicolaas

[Peter Fox]

Re: opening a page with the sole purpose of running an sql statement

Following on from windandwaves's message. . .

>Hi Folk
>
>I am running a site (A) that has a US mirror (B). This means that all
>the data from A is copied daily to B.
>
>On the site, I keep some statistics of the pages people visit, tracking
>them with a session and noting their searches, etc... for statistical
>purposes.

The 'statistical purposes' bit is the clue.

You don't need real-time data reporting. That's a relief because what
happens when site A is down for maintenance and site B is on-line?

Try something like:
PrintToStatsLog (UserId,PageId, GetSiteId());
where the log would be local to each system.
This could be in raw form 4,5,B...6,7,A etc or ready to run SQL
(printf-ing instead of doing a live query.)

You can now read these in when it's convenient.

Putting things in log files gives is an opportunity to think more about
your data eg
* perhaps weed page-backs (Systems view: It's not a new visit)
* perhaps identify page-back (User view : No that's not what I wanted)
* perhaps identify page-backs (Returned to page may be one of the more
useful ones)
* perhaps measure how long it takes people to find what they're looking
for.

I'd put a timestamp on as well.

I'd also make sure any search terms were sanitized before letting them
anywhere near a database.

--
PETER FOX Not the same since the e-commerce business came to a .
peterfox@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>

[windandwaves]

Re: opening a page with the sole purpose of running an sql statement

Thank you both for all your help! I need some time to work with
this... Thanks a million again! It cleared up a lot for me.

Nicolaas