MySQL PHP Forum
Forum for MySQL and PHP.
Updated: 2 hours 20 min ago
How to setup Password for db under 1 Host(server) (no replies)
Hi
I have different DB in lying on MySQL/Apache server. I need to know how to setup the password for each db lying on my server.
Thank You
I have different DB in lying on MySQL/Apache server. I need to know how to setup the password for each db lying on my server.
Thank You
Order by days (1 reply)
Hi i displayed a list of the lecture's class schedule on the datagrid as shown,
as you can see, it is currently displaying in Mon, Thu,Tue, Wed. How do i actually sort the days in ascending order like, monday, tuesday, wednesday...?
the php coding that retrieve the data
SELECT * FROM timetable WHERE staffid = '$staffid';
I cannot put this code
SELECT * FROM timetable WHERE staffid = '$staffid' order by day asc;
as the day column is in string data type. What should i do?
as you can see, it is currently displaying in Mon, Thu,Tue, Wed. How do i actually sort the days in ascending order like, monday, tuesday, wednesday...?
the php coding that retrieve the data
SELECT * FROM timetable WHERE staffid = '$staffid';
I cannot put this code
SELECT * FROM timetable WHERE staffid = '$staffid' order by day asc;
as the day column is in string data type. What should i do?
PAGING: mysql_fetch_assoc($rs) vs mysql_num_rows($rs) (no replies)
Hi,
I have selected first 20 records with the combination of inner and left join in mysql select query for php page. Now, I would like to implement paging (single record on each page). If we use select.... limit 20; then paging becomes difficult and if we use select .... limit 0, 1 then it might return more than 20 records to be page.
So, i wish to write loop statement like:-
while ($row_rs = mysql_num_rows($rs) <= 20);
instead of
while ($row_rs = mysql_fetch_assoc($rs));
I would like to know, will it return same results as we expect from mysql_fetch_assoc($rs) and how much it is practical way to solve the problem.
Thanks
I have selected first 20 records with the combination of inner and left join in mysql select query for php page. Now, I would like to implement paging (single record on each page). If we use select.... limit 20; then paging becomes difficult and if we use select .... limit 0, 1 then it might return more than 20 records to be page.
So, i wish to write loop statement like:-
while ($row_rs = mysql_num_rows($rs) <= 20);
instead of
while ($row_rs = mysql_fetch_assoc($rs));
I would like to know, will it return same results as we expect from mysql_fetch_assoc($rs) and how much it is practical way to solve the problem.
Thanks
help: enter a data from the table into a variable (3 replies)
Hi all,
I am trying to write this part:
$sql=mysql_query("select filename from pictures where row=1");
$row = mysql_fetch_array($sql);
echo "$row";
it soppused to take a word from the table and then write it, and I just don't success it.
Thanks for any help.
I am trying to write this part:
$sql=mysql_query("select filename from pictures where row=1");
$row = mysql_fetch_array($sql);
echo "$row";
it soppused to take a word from the table and then write it, and I just don't success it.
Thanks for any help.
MySql table crashes frequently (no replies)
I'm facing a weird problem with a table in mysql. This table crashes every now and then. We have created a table which will be used concurrently by multiple users and every second there are 100's of records inserted into it and read from it.
We are also using a cron job (1hour interval) to delete records from this table which are inserted 1hour back and then optimize the table immediately.
This table crashes more than 4 times in a day and because of optimize command running through the cron job, it gets repaired at that time. When I run check table after it crashes there are 3 messages displayed for it.
1. Warning: Table is marked as crashed.
2. error: Found n-1 keys of n
3. error: corrupt
Following are the properties of the table:
Engine:MyISAM, Version:9, Row_format:Dynamic, Max_data_length:4294967295, Index_length:1024
There are 19 fields in this table and indexes are there on five fields. I'm not sure why this is getting crashed. Can anybody tell me the reason why it is happening and what is the solution for it?
Thanks in advance,
JP
We are also using a cron job (1hour interval) to delete records from this table which are inserted 1hour back and then optimize the table immediately.
This table crashes more than 4 times in a day and because of optimize command running through the cron job, it gets repaired at that time. When I run check table after it crashes there are 3 messages displayed for it.
1. Warning: Table is marked as crashed.
2. error: Found n-1 keys of n
3. error: corrupt
Following are the properties of the table:
Engine:MyISAM, Version:9, Row_format:Dynamic, Max_data_length:4294967295, Index_length:1024
There are 19 fields in this table and indexes are there on five fields. I'm not sure why this is getting crashed. Can anybody tell me the reason why it is happening and what is the solution for it?
Thanks in advance,
JP
Table check and repair (1 reply)
Hello,
I'm using mysql with php for a web-based software. This software runs for a cash desk but some days ago a blackout crashed a table. I had to attend and repair it. I would like to know if it is rare to crash a table with a blackout and if a script with a "CHECK TABLE" query in and then if necessary a "REPAIR TABLE "query maybe can solve some of this problems. (Maybe i can run this script every 10 minutes... or something like this)
Thank you.
I'm using mysql with php for a web-based software. This software runs for a cash desk but some days ago a blackout crashed a table. I had to attend and repair it. I would like to know if it is rare to crash a table with a blackout and if a script with a "CHECK TABLE" query in and then if necessary a "REPAIR TABLE "query maybe can solve some of this problems. (Maybe i can run this script every 10 minutes... or something like this)
Thank you.
group by trouble... (1 reply)
Hi,
I have a trouble with the group function using php Mysql. Performing the following :
"SELECT * FROM table WHERE owner LIKE'$username' GROUP BY date"
returns the first row fitting the condition even if several rows do fit the condition.
What I would need is to return the maximum of each column of all the rows fitting the condition (I have lots of columns).
For instance, with the following table,
id=1 - owner=you - bla1=aaa - bla2=222 - bla3=222
id=2 - owner=you - bla1=bbb - bla2=111 - bla3=333
I would like to get :
-----> owner=you - bla1=bbb - bla2=222 - bla3=333
Can anybody help me ?
Many thanks in advance!!!
I have a trouble with the group function using php Mysql. Performing the following :
"SELECT * FROM table WHERE owner LIKE'$username' GROUP BY date"
returns the first row fitting the condition even if several rows do fit the condition.
What I would need is to return the maximum of each column of all the rows fitting the condition (I have lots of columns).
For instance, with the following table,
id=1 - owner=you - bla1=aaa - bla2=222 - bla3=222
id=2 - owner=you - bla1=bbb - bla2=111 - bla3=333
I would like to get :
-----> owner=you - bla1=bbb - bla2=222 - bla3=333
Can anybody help me ?
Many thanks in advance!!!
Where i could download mysql.class.php? (1 reply)
Is there any thing like mysql.class.php? I am developing free os cms, and i want somehow "improve" compactibility with class'es that are used in cms, but not loaded in host...
Percent symbol (1 reply)
I have this in a php doc.
$rs_menucategories = mysql_query('SELECT `MenuCategoryID`, `MenuCategoryName` FROM `universitymenucategory`');
$q_menuitems = 'SELECT `MenuCategoryID`, `ItemName`, `Order`, `ItemCost`, `ItemDescription`'
. ' FROM `universitymenu`'
. ' WHERE `MenuCategoryID` = %d'
. ' ORDER BY `Order` ASC';
The only reference I can find for %d is date formatting. Nothing in the DB has anything to do with date. What exactly does the %d do here?
$rs_menucategories = mysql_query('SELECT `MenuCategoryID`, `MenuCategoryName` FROM `universitymenucategory`');
$q_menuitems = 'SELECT `MenuCategoryID`, `ItemName`, `Order`, `ItemCost`, `ItemDescription`'
. ' FROM `universitymenu`'
. ' WHERE `MenuCategoryID` = %d'
. ' ORDER BY `Order` ASC';
The only reference I can find for %d is date formatting. Nothing in the DB has anything to do with date. What exactly does the %d do here?
IF syntax (1 reply)
Hello, please, help me...
What is wrong on this query?
$dotaz = "IF((SELECT COUNT(*) AS pocet FROM jos_product WHERE kod_zbozi = '".$kod_zbozi."') = 0, , INSERT INTO jos_vm_product_parametry ( parametr_publish, nazev, hodnota, kod_zbozi, poradi, editovano ) VALUES ( 1, IF((SELECT COUNT(*) AS pocet FROM jos_atc_parametry_kody WHERE kod = '".$kod_parametru."') = 0,'".$kod_parametru."' , (SELECT nazev FROM jos_atc_parametry_kody WHERE kod = '".$kod_parametru."')) , '".$hodnota."', (SELECT product_id FROM jos_vm_product WHERE kod_zbozi = '".$kod_zbozi."' ), 99, 'ne' ));";
the query send me this error mesage:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(((SELECT COUNT(*) AS pocet FROM jos_product WHERE kod_zbozi = '2055149105') =' at line 1
Thank you very much!
(and sory for my bad english :-\ )
What is wrong on this query?
$dotaz = "IF((SELECT COUNT(*) AS pocet FROM jos_product WHERE kod_zbozi = '".$kod_zbozi."') = 0, , INSERT INTO jos_vm_product_parametry ( parametr_publish, nazev, hodnota, kod_zbozi, poradi, editovano ) VALUES ( 1, IF((SELECT COUNT(*) AS pocet FROM jos_atc_parametry_kody WHERE kod = '".$kod_parametru."') = 0,'".$kod_parametru."' , (SELECT nazev FROM jos_atc_parametry_kody WHERE kod = '".$kod_parametru."')) , '".$hodnota."', (SELECT product_id FROM jos_vm_product WHERE kod_zbozi = '".$kod_zbozi."' ), 99, 'ne' ));";
the query send me this error mesage:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(((SELECT COUNT(*) AS pocet FROM jos_product WHERE kod_zbozi = '2055149105') =' at line 1
Thank you very much!
(and sory for my bad english :-\ )
Going mad with error..! (4 replies)
Upgraded to Mysql 5.0.51, and now getting the
You have an error in your SQL syntax;
from the following code which worked well before:
$result=mysql_query("SELECT event_ID from inout WHERE (email='$Email' and day='$day' and month='$month' and year='$year')");
if (!$result) {
echo mysql_error();
}
else{
$rowin = mysql_fetch_row($result);
$event_ID = $rowin[6];
}
I can for the life of me not see any error here in this PHP code......
Here are the tables from the dump:
`month` char(2) NOT NULL,
`day` char(2) NOT NULL,
`year`char(4) NOT NULL default '',
`start_time` char(10) NOT NULL default '',
`end_time` char(10) NOT NULL default '',
`information` varchar(100) NOT NULL default '',
`event_ID` int(7) NOT NULL auto_increment,
`email` varchar(50) NOT NULL default '',
Now I use similar queries elsewhere which works very well, which leads me to believe that it is not the PHP syntax, but something else. What then is it?
Been sitting with this now for a day - any help will be much appreciated!
This is the full error when run:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inout WHERE (email='ABCDEF' and day='29' and month='08' and year='2008')' at line 1
You have an error in your SQL syntax;
from the following code which worked well before:
$result=mysql_query("SELECT event_ID from inout WHERE (email='$Email' and day='$day' and month='$month' and year='$year')");
if (!$result) {
echo mysql_error();
}
else{
$rowin = mysql_fetch_row($result);
$event_ID = $rowin[6];
}
I can for the life of me not see any error here in this PHP code......
Here are the tables from the dump:
`month` char(2) NOT NULL,
`day` char(2) NOT NULL,
`year`char(4) NOT NULL default '',
`start_time` char(10) NOT NULL default '',
`end_time` char(10) NOT NULL default '',
`information` varchar(100) NOT NULL default '',
`event_ID` int(7) NOT NULL auto_increment,
`email` varchar(50) NOT NULL default '',
Now I use similar queries elsewhere which works very well, which leads me to believe that it is not the PHP syntax, but something else. What then is it?
Been sitting with this now for a day - any help will be much appreciated!
This is the full error when run:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inout WHERE (email='ABCDEF' and day='29' and month='08' and year='2008')' at line 1
Issue about passing a table name from php to MySQL procedure to create a table (2 replies)
Hi everyone,
I want to pass a table name from php to a MySQL procedure and then use the name to create a table.
The procedure is like following.
===============================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`ct` $$
CREATE PROCEDURE `test`.`ct` (IN tableName VARCHAR(200))
BEGIN
SET @createTab=CONCAT("CREATE TABLE ", tableName, " (tmp_template_id varchar(10))");
PREPARE stmtCreate FROM @createTab;
EXECUTE stmtCreate;
END $$
DELIMITER ;
===============================================================
I can call the procedure and create a table in MySQL Browser by the following command.
CALL ct('aaa');
But when I call the procedure from php page using the following script, I cannot crate the table.
$query = "call pt('aaa');";
$result = mysql_query($query);
I want to pass a table name from php to a MySQL procedure and then use the name to create a table.
The procedure is like following.
===============================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`ct` $$
CREATE PROCEDURE `test`.`ct` (IN tableName VARCHAR(200))
BEGIN
SET @createTab=CONCAT("CREATE TABLE ", tableName, " (tmp_template_id varchar(10))");
PREPARE stmtCreate FROM @createTab;
EXECUTE stmtCreate;
END $$
DELIMITER ;
===============================================================
I can call the procedure and create a table in MySQL Browser by the following command.
CALL ct('aaa');
But when I call the procedure from php page using the following script, I cannot crate the table.
$query = "call pt('aaa');";
$result = mysql_query($query);
Multiple line SQL query with PHP (3 replies)
So come on, what's the secret?
I am a newbie wanting to know how to execute a query with multiple SQL statements using PHP? The first statement sets the inital values of some @variables and the second is a SELECT statement returning one result set.
I am using PHP 5.2.5 and MySQL 4.1.22.
I am a newbie wanting to know how to execute a query with multiple SQL statements using PHP? The first statement sets the inital values of some @variables and the second is a SELECT statement returning one result set.
I am using PHP 5.2.5 and MySQL 4.1.22.
1054 - Unknown column 'supertracker' in 'field list' select supertracker from admin_access where customers_id = '1' [XT SQL Error] (1 reply)
Hello!
Im a beginner in programming and cant find the solution to my problem.
I want to use this two PHP files in my shopsystem, but at some place the code is wrong and cant access the database: MySQL 4.0...
Will publish here the code, maybe some crack can see easy what must be done to run this code proper!
Error Code:
1054 - Unknown column 'supertracker' in 'field list'
select supertracker from admin_access where customers_id = '1'
[XT SQL Error]
File #1:
START
***********************************************************
class supertracker {
function supertracker(){
}
function update() {
global $cart, $HTTP_GET_VARS, $customer_id;
//// **** CONFIGURATION SECTION **** ////
//Comma Separate List of IPs which should not be recorded, for instance, your own PCs IP address, or
//that of your server if you are using Cron Jobs, etc
$excluded_ips = '';
//// **** CONFIGURATION SECTION EOF **** ////
$record_session = true;
$ip_address = $_SERVER['REMOTE_ADDR'];
if ($excluded_ips != '') {
$ex_array = explode(',',$excluded_ips);
foreach ($ex_array as $key => $ex_ip) {
if ($ip_address == $ex_ip) $record_session = false;
}
}
/* EXCLUDE BOTS BEGIN - REMOVE THIS LINE IF YOU WANT TO EXCLUDE BOTS FROM THE STATS
$agent = $_SERVER['HTTP_USER_AGENT'];
$bot_here = false;
$arr = array("msnbot", "googlebot", "slurp", "abot", "dbot", "ebot", "hbot", "kbot", "mbot", "nbot", "obot", "pbot", "rbot", "sbot", "tbot", "ybot", "bot.", "crawl", "slurp", "spider", "accoona", "acoon", "ah-ha.com", "ahoy", "altavista", "ananzi", "anthill", "appie", "arachnophilia", "arale", "araneo", "aranha", "architext", "aretha", "arks", "aspseek", "asterias", "atlocal", "atn", "atomz", "augurfind", "backrub", "bannana_bot", "big brother", "bjaaland", "blackwidow", "bloodhound", "boitho", "booch", "bradley", "calif", "cassandra", "ccubee", "cfetch", "churl", "cienciaficcion", "cmc", "collective", "combine system", "computingsite", "cusco", "deepindex", "deweb", "die blinde kuh", "digger", "dmoz", "docomo", "download express", "dwcp", "ebiness", "e-collector", "ejupiter", "emacs-w3 search engine", "esther", "evliya celebi", "ezresult", "falcon", "felix ide", "ferret", "fetchrover", "fido", "findlinks", "fireball", "fish search", "fouineur", "funnelweb", "gazz", "gcreep", "getterroboplus", "geturl", "glx", "goforit", "golem", "grabber", "grapnel", "griffon", "gromit", "grub", "gulliver", "hamahakki", "harvest", "havindex", "helix", "heritrix", "hku www octopus", "homerweb", "htdig", "html index", "html_analyzer", "htmlgobble", "hubater", "hyper-decontextualizer", "ia_archiver", "ibm_planetwide", "ichiro", "iconsurf", "iltrovatore", "image.kapsi.net", "imagelock", "incywincy", "indexer", "infobee", "informant", "infoseek", "ingrid", "inktomisearch.com", "inspector web", "intelliagent", "internet shinchakubin", "ip3000", "iron33", "israeli-search", "ivia", "jack", "jakarta", "java/", "javabee", "jetbot", "jumpstation", "katipo", "kdd-explorer", "kilroy", "knowledge", "kototoi", "labelgrabber", "lachesis", "larbin", "legs", "libwww", "linkalarm", "link validator", "linkscan", "linkwalker", "lockon", "lwp", "lycos", "magpie", "mantraagent", "marvin/", "mattie", "mediafox", "mediapartners", "mercator", "merzscope", "miva", "mj12", "mnogosearch", "moget", "monster", "moose", "motor", "multitext", "muncher", "muscatferret", "mwd.search", "myweb", "nameprotect", "nationaldirectory", "nazilla", "ncsa beta", "nec-meshexplorer", "nederland.zoek", "netcarta webmap engine", "netmechanic", "netresearchserver", "netscoop", "newscan-online", "ng/", "nhse", "nomad", "nutch", "nzexplorer", "objectssearch", "occam", "omni", "open text", "openfind", "orb search", "osis-project", "pack rat", "pageboy", "parasite", "partnersite", "patric", "pear.", "pegasus", "peregrinator", "pgp key agent", "phantom", "phpdig", "picosearch", "piltdownman", "pimptrain", "pinpoint", "pioneer", "piranha", "plumtreewebaccessor", "pompos", "poppelsdorf", "poppi", "popular iconoclast", "rambler", "raven search", "roach", "road runner", "roadhouse", "robbie", "robofox", "robozilla", "rules", "salty", "sbider", "scooter", "scrubby", "search.", "searchprocess", "seeker", "semanticdiscovery", "senrigan", "sg-scout", "shai'hulud", "shark", "shopwiki", "sidewinder", "sift", "simmany", "site searcher", "site valet", "sitetech-rover", "skymob.com", "sleek", "smartwit", "sna-", "snooper", "sohu", "speedfind", "spinner", "spyder", "steeler/", "suke", "suntek", "supersnooper", "surfnomore", "sven", "sygol", "szukacz", "tach black widow", "tarantula", "templeton", "/teoma", "t-h-u-n-d-e-r-s-t-o-n-e", "titan", "titin", "tkwww", "toutatis", "t-rex", "tutorgig", "ucsd", "udmsearch", "ultraseek", "url check", "vagabondo", "valkyrie", "verticrawl", "victoria", "vision-search", "volcano", "voyager/", "w3c_validator", "w3m2", "w3mir", "wallpaper", "wanderer", "web core", "web hopper", "web wombat", "webbandit", "webcatcher", "webcopy", "webfoot", "weblayers", "weblinker", "weblog monitor", "webmirror", "webquest", "webreaper", "websitepulse", "websnarf", "webstolperer", "webvac", "webwalk", "webwatch", "webwombat", "webzinger", "wget", "whatuseek", "whizbang", "whowhere", "wild ferret", "wire", "worldlight", "wwwc", "xenu", "xget", "xift", "yandex", "zao/", "zippp", "zyborg");
foreach ($arr as $bot) {
if (eregi($bot, $agent)) {
$bot_here = true;
}
}
if ($bot_here) {
$record_session = false;
}
EXCLUDE BOTS END OF SECTION - REMOVE THIS LINE IF YOU WANT TO EXCLUDE BOTS FROM THE STATS */
//Big If statement that stops us doing anything more if this IP is one of the
//ones we have chosen to exclude
if ($record_session) {
$existing_session = false;
$thirty_ago_timestamp = strtotime("now") - (30*60);
$thirty_mins_ago = date('Y-m-d H:i:s', $thirty_ago_timestamp);
$browser_string = addslashes(tep_db_input(urldecode($_SERVER['HTTP_USER_AGENT'])));
$ip_array = explode ('.',$ip_address);
$ip_start = $ip_array[0] . '.' . $ip_array[1];
//Find out if this user already appears in the supertracker db table
//First thing to try is customer_id, if they are signed in
if (isset($customer_id)) {
$query = "select * from supertracker where customer_id ='" . $customer_id . "' and last_click > '" . $thirty_mins_ago . "'";
$result = tep_db_query($query);
if (tep_db_num_rows ($result) > 0) {
$existing_session = true;
}
}
//Next, we try this: compare first 2 parts of the IP address (Class B), and the browser
//Identification String, which give us a good chance of locating the details for a given user. I reckon
//that the chances of having more than 1 user within a 30 minute window with identical values
//is pretty small, so hopefully this will work and should be more reliable than using Session IDs....
if (!$existing_session) {
$query = "select * from supertracker where browser_string ='" . $browser_string . "' and ip_address like '" . $ip_start . "%' and last_click > '" . $thirty_mins_ago . "'";
$result = tep_db_query($query);
if (tep_db_num_rows ($result) > 0) {
$existing_session = true;
}
}
//If that didn't work, and we have something in the cart, we can use that to try and find the
//record instead. Obviously, people with things in their cart don't just appear from nowhere!
if (!$existing_session) {
if ($cart->count_contents()>0) {
$query = "select * from supertracker where cart_total ='" . $cart->show_total() . "' and last_click > '" . $thirty_mins_ago . "'";
$result = tep_db_query($query);
if (tep_db_num_rows ($result) > 0) {
$existing_session = true;
}
}
}
//Having worked out if we have a new or existing user session lets record some details....!
if ($existing_session) {
//Existing tracked session, so just update relevant existing details
$tracking_data = tep_db_fetch_array($result);
$tracking_id = $tracking_data['tracking_id'];
$products_viewed=$tracking_data['products_viewed'];
$added_cart = $tracking_data['added_cart'];
$completed_purchase = $tracking_data['completed_purchase'];
$num_clicks = $tracking_data['num_clicks']+1;
$categories_viewed = unserialize($tracking_data['categories_viewed']);
$cart_contents = unserialize($tracking_data['cart_contents']);
$cart_total = $tracking_data['cart_total'];
$order_id = $tracking_data['order_id'];
if (isset($customer_id)) $cust_id=$customer_id;
else $cust_id=$tracking_data['customer_id'];
$current_page=addslashes(tep_db_input(urldecode($_SERVER['PHP_SELF'])));
$last_click = date('Y-m-d H:i:s');
//Find out if the customer has added something to their cart for the first time
if (($added_cart!='true') && ($cart->count_contents()>0)) $added_cart = 'true';
//Has a purchase just been completed?
if ((strstr($current_page, 'checkout_success.php'))&& ($completed_purchase!='true')) {
$completed_purchase='true';
$order_q = "select orders_id from orders where customers_id = '" . $cust_id . "' ORDER BY date_purchased DESC";
$order_result = tep_db_query($order_q);
if (tep_db_num_rows($order_result) > 0) {
$order_row = tep_db_fetch_array($order_result);
$order_id = $order_row['orders_id'];
}
}
//If customer is looking at a product, add it to the list of products viewed
if (strstr($current_page, 'product_info.php')) {
$current_product_id = (int)$HTTP_GET_VARS['products_id'];
if (!strstr($products_viewed, '*' . $current_product_id . '?')) {
//Product hasn't been previously recorded as viewed
$products_viewed .= '*' . $current_product_id . '?';
}
}
//Store away their cart contents
//But, the cart is dumped at checkout, so we don't want to overwrite the stored cart contents
//In this case
$current_cart_contents = serialize($cart->contents);
if (strlen($current_cart_contents)>6) {
$cart_contents = $current_cart_contents;
$cart_total = $cart->show_total();
}
//If we are on index.php, but looking at category results, make sure we record which category
if (strpos($current_page, 'index.php')) {
if (isset($_GET['cPath'])) {
$cat_id = $_GET['cPath'];
$cat_id_array = explode('_',$cat_id);
$cat_id = $cat_id_array[sizeof($cat_id_array)-1];
$categories_viewed[$cat_id]=1;
}
}
$categories_viewed = serialize($categories_viewed);
$query = "UPDATE supertracker set last_click='" . $last_click . "', exit_page='" . $current_page . "', num_clicks='" . $num_clicks . "', added_cart='" . $added_cart . "', categories_viewed='" . $categories_viewed . "', products_viewed='" . $products_viewed . "', customer_id='" . $cust_id . "', completed_purchase='" . $completed_purchase . "', cart_contents='" . $cart_contents . "', cart_total = '" . $cart_total . "', order_id = '" . $order_id . "' where tracking_id='" . $tracking_id . "'";
tep_db_query($query);
}
else {
//New vistor, so record referrer, etc
//Next line defines pages on which a new visitor should definitely not be recorded
$prohibited_pages = 'login.php,checkout_shipping.php,checkout_payment.php,checkout_process.php,checkout_confirmation.php,checkout_success.php';
$current_page=addslashes(tep_db_input(urldecode($_SERVER['PHP_SELF'])));
if (!strpos($prohibited_pages, $current_page)) {
$refer_data = addslashes(tep_db_input(urldecode($_SERVER['HTTP_REFERER'])));
$refer_data = explode('?', $refer_data);
$referrer=$refer_data[0];
$query_string=$refer_data[1];
$ip = $_SERVER['REMOTE_ADDR'];
$browser_string = addslashes(tep_db_input(urldecode($_SERVER['HTTP_USER_AGENT'])));
include(DIR_WS_INCLUDES . "geoip.inc");
$gi = geoip_open(DIR_WS_INCLUDES . "GeoIP.dat",GEOIP_STANDARD);
$country_name = geoip_country_name_by_addr($gi, $ip);
$country_code = strtolower(geoip_country_code_by_addr($gi, $ip));
geoip_close($gi);
$time_arrived = date('Y-m-d H:i:s');
$landing_page = addslashes(tep_db_input(urldecode($_SERVER['REQUEST_URI'])));
$query = "INSERT INTO `supertracker` (`ip_address`, `browser_string`, `country_code`, `country_name`, `referrer`,`referrer_query_string`,`landing_page`,`exit_page`,`time_arrived`,`last_click`) VALUES ('" . $ip . "','" . $browser_string . "','" . $country_code . "', '" . $country_name . "', '" . $referrer . "', '" . $query_string . "','" . $landing_page . "','" . $current_page . "','" . $time_arrived . "','" . $time_arrived . "')";
tep_db_query($query);
}//end if for prohibited pages
}//end else
}//End big If statement (Record Exclusion for certain IPs)
}//End function update
}//End Class
?>
***************************************************************
END
***************************************************************
File #2 , display the result
START
***************************************************************
// ********** PAY PER CLICK CONFIGURATION SECTION ************
// Pay per click referral URLs used - to make this work you have to set up your pay-per-click
// URLs like this : http://www.yoursite.com/catalog/index.php?ref=xxx&keyw=yyy
// where xxx is a code representing the PPC service and yyy is the keyword being used
// to generate that referral. Here's an example :
// http://www.yoursite.com/catalog/index.php?ref=googled&keyw=gameboy
// which might be used for the keyword "gameboy" in a google adwords campaign.
// The keyword part is optional - if you don't use it in a particular campaign, you
// Just set up the $ppc array like that in the example for googlead below
$ppc = array ('googlead' => array ('title' => 'Google Adwords', 'keywords' => 'shortcode1:friendlyterm1,shortcode1:friendlyterm2'));
//Set the following to true to enable the PPC referrer report
//Eventually, this will probably be moved into the configuration menu
//in admin, where it really should be!
define ('SUPERTRACKER_USE_PPC', false);
// ********** PAY PER CLICK CONFIGURATION SECTION EOF ************
require('includes/application_top.php');
require(DIR_WS_LANGUAGES . $language . '/supertracker.php');
include ('includes/classes/currencies.php');
$currency = new currencies();
function draw_geo_graph($geo_hits,$country_names,$total_hits) {
echo '<table cellpadding=2 cellspacing=0 border=0>';
$max_pixels = 200;
arsort($geo_hits);
foreach ($geo_hits as $country_code=>$num_hits) {
$country_name = $country_names[$country_code];
$bar_length = ($num_hits/$total_hits) * $max_pixels;
$percent_hits = round(($num_hits/$total_hits) * 100,2);
//Create a random colour for each bar
srand((double)microtime()*1000000);
$r = dechex(rand (0, 255));
$g = dechex(rand (0, 255));
$b = dechex(rand (0, 255));
echo '<tr><td>' . $country_name . ': </td><td><div style="display:justify;background:#' . $r . $g . $b . ';border:1px solid #000;height:10px;width:' . $bar_length . '"></div></td><td>' . $percent_hits . '%</td></tr>';
}
echo '</table>';
}//end function
if (isset($_GET['action'])) $action = $_GET['action'];
if ($action == 'del_rows') {
$rows_to_delete = $_POST['num_rows'];
$del_query = "DELETE from supertracker ORDER by tracking_id ASC LIMIT " . $rows_to_delete;
$del_result = xtc_db_query ($del_query);
}
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script language="javascript">
function page_redirect(url) {
url=url.value;
location.href = url;
}
</script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
<tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
</table></td>
<!-- body_text //-->
<td width="100%" valign="top">
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td>
<div class="supertracker_title">Supertracker</div>
<?php /* comented by azer
<div class="supertracker_contact">
<strong><?php echo TEXT_EXTERNAL_LINK;?></strong><br>
<a class="supertracker_link" href="http://forums.oscommerce.com/index.php?showtopic=166141"><?php echo TEXT_SUPPORT; ?></a>
<a class="supertracker_link" href="http://forums.oscommerce.com/index.php?showtopic=166839"><?php echo TEXT_THREAD; ?></a><br>
*/ ?>
</div>
<div class="supertracker_contact">
<strong><?php echo TEXT_DATABASE_INFO; ?></strong>
<?php
$maint_query = "select tracking_id, time_arrived from supertracker order by tracking_id ASC";
$maint_result = xtc_db_query($maint_query);
$num_rows = xtc_db_num_rows($maint_result);
$maint_row = xtc_db_fetch_array($maint_result);
echo '<span class="supertracker_text">' . sprintf(TEXT_TABLE_DATABASE, $num_rows, xtc_date_short($maint_row['time_arrived'])) . '</span><br>';
echo '<form name="del_rows" action="supertracker.php?action=del_rows" method="post">' . TEXT_TABLE_DELETE . '<input name="num_rows" size=10><input type="submit" value="' . TEXT_BUTTON_GO . '"></form>';
?>
</div>
<div class="supertracker_text">
<?php echo TABLE_TEXT_MENU_DESC_TEXTE; ?> <form name="report_select"><select name="report_selector" onchange="page_redirect(this)">
<option value=""><?php echo TABLE_TEXT_MENU_TEXTE; ?></option>
<option value="supertracker.php?report=refer"><?php echo TEXT_TOP_REFERRERS; ?></option>
<option value="supertracker.php?report=success_refer"><?php echo TEXT_TOP_SALES;?></option>
<option value="supertracker.php?special=geo"><?php echo TEXT_VISITORS;?></option>
<option value="supertracker.php?special=keywords"><?php echo TEXT_SEARCH_KEYWORDS;?></option>
<option value="supertracker.php?special=keywords_last24"><?php echo TEXT_SEARCH_KEYWORDS_24;?></option>
<option value="supertracker.php?special=keywords_last72"><?php echo TEXT_SEARCH_KEYWORDS_3;?></option>
<option value="supertracker.php?special=keywords_lastweek"><?php echo TEXT_SEARCH_KEYWORDS_7;?></option>
<option value="supertracker.php?special=keywords_lastmonth"><?php echo TEXT_SEARCH_KEYWORDS_30;?></option>
<option value="supertracker.php?report=exit"><?php echo TEXT_TOP_EXIT_PAGES;?></option>
<option value="supertracker.php?report=exit_added"><?php echo TEXT_TOP_EXIT_PAGES_NO_SALE;?></option>
<option value="supertracker.php?report=ave_clicks"><?php echo TEXT_AVERAGE_CLICKS;?></option>
<option value="supertracker.php?report=ave_time"><?php echo TEXT_AVERAGE_TIME_SPENT;?></option>
<option value="supertracker.php?special=prod_coverage"><?php echo TEXT_PRODUCTS_VIEWED_REPORT;?></option> <!-- modifed by azer//-->
<option value="supertracker.php?special=last_ten"><?php echo TEXT_LAST_TEN_VISITORS;?></option>
<option value="supertracker.php?report=agent"><?php echo TEXT_TOP_AGENTS; //MB: Add agent tracking?></option>
<?php if (SUPERTRACKER_USE_PPC) {?>
<option value="supertracker.php?special=ppc_summary"><?php echo TEXT_PPC_REFERRAL;?></option>
<?php } ?>
</select>
<input type="submit" value="Go"></form>
</div>
</div>
</td>
</tr>
<?php
//MB: Add agent tracking
if (isset($HTTP_GET_VARS['byagent'])){
$groupby = 'agent';
} else {
$groupby = 'referrer';
} //MB end
if (isset($HTTP_GET_VARS['report'])) {
$report=$HTTP_GET_VARS['report'];
$headings=array();
$row_data=array();
if ($report=='refer') {
$title = TEXT_TOP_REFFERING_URL;
$headings[]=TEXT_RANKING;
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_NUMBER_OF_HITS;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker GROUP BY ' . $groupby . ' order by total DESC'; //MB Add agent tracking
}
if ($report=='success_refer') {
$title = TEXT_SUCCESSFUL;
$headings[]=TEXT_SERIAL;
//MB Add agent tracking
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_NUMBER_OF_SALES;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker WHERE completed_purchase = "true" group by '. $groupby . ' order by total DESC'; //MB Add agent tracking
}
if ($report=='exit') {
$title =TEXT_TOP_PAGES_EXIT;
$headings[]=TEXT_SERIAL;
$headings[]=TEXT_EXIT_PAGE;
$headings[]=TEXT_NUMBER_OF_OCCURRENCES;
$row_data[]='exit_page';
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker where completed_purchase="false" group by exit_page order by total DESC';
}
if ($report=='exit_added') {
$title = TEXT_TOP_EXIT_PAGES_NO_SALE;
$headings[]=TEXT_SERIAL;
$headings[]=TEXT_EXIT_PAGE;
$headings[]=TEXT_NUMBER_OF_OCCURRENCES;
$row_data[]='exit_page';
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker where completed_purchase="false" and added_cart="true" group by exit_page order by total DESC';
}
if ($report=='ave_clicks') {
$title = TEXT_CLICKS_BY_REFFERRER_REPORT;
$headings[]=TEXT_SERIAL;
//MB Add agent tracking
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_NUMBER_OF_CLICKS;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='ave_clicks';
$tracker_query_raw='SELECT *, AVG(num_clicks) as ave_clicks FROM supertracker group by '. $groupby . ' order by ave_clicks DESC';//MB Add agent tracking
}
if ($report=='ave_time') {
$title = TEXT_AVERAGE_TIME_ON_SITE_BY;
$headings[]=TEXT_SERIAL;
//MB Add agent tracking
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_AVERAGE_LENGTH_OF_TIME;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='ave_time';
$tracker_query_raw='SELECT *, AVG(UNIX_TIMESTAMP(last_click) - UNIX_TIMESTAMP(time_arrived))/60 as ave_time FROM supertracker group by '. $groupby . ' order by ave_time DESC'; //MB Add agent tracking
}
//MB: Add agent tracking
if ($report=='agent') {
$title = TEXT_TOP_AGENTS;
$headings[]=TEXT_RANKING;
$headings[]=TEXT_TOP_AGENTS;
$headings[]=TEXT_NUMBER_OF_HITS;
$row_data[]='agent';
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker GROUP BY agent order by total DESC';
}
//MB: end
$tracker_query = xtc_db_query($tracker_query_raw);
?>
<tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td class="pageHeading"><?php echo $title; ?></td>
<td class="pageHeading" align="right"><?php echo xtc_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
</tr>
</table></td>
</tr>
<tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr class="dataTableHeadingRow">
<?php
foreach ($headings as $h) {
echo '<td class="dataTableHeadingContent">' . $h . '</td>';
}
?>
</tr>
<?php
$counter = 0;
while ($tracker = xtc_db_fetch_array($tracker_query)) {
$counter++;
?>
<tr class="dataTableRow">
<td class="dataTableContent"><?php echo $counter?></td>
<?php
foreach ($row_data as $r) {
if (strlen($tracker[$r]) > 50) $tracker[$r] = substr($tracker[$r],0,50);
echo '<td class="dataTableContent"' . $style_override . '>' . $tracker[$r] . '</td>';
}
?>
</tr>
<?php
}
?>
</table></td>
</tr>
</table></td>
</tr>
</table>
<?php
//MB Add agent tracking report option
if ($groupby == 'referrer'){
echo '<br>' . TEXT_THIS_REPORT . ' ('.$report . $special. ') <form name="filter_agent" action="supertracker.php" method="get" onchange="this.submit()">
<input type="hidden" name="special" value="' . $special . '">
<input type="hidden" name="report" value="'. $report . '">
<input type="hidden" name="byagent" value="1">
<input type="submit" value = "' . TEXT_BY_AGENT . '"><br>
<form>';
} //MB agent tracking
} //End if
if (isset($HTTP_GET_VARS['special'])) {
if ($HTTP_GET_VARS['special'] == 'keywords_last24') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_24 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 1 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last 24h
if ($HTTP_GET_VARS['special'] == 'keywords_last72') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_3 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 3 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last 72h
if ($HTTP_GET_VARS['special'] == 'keywords_lastweek') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_7 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 7 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last 7d
if ($HTTP_GET_VARS['special'] == 'keywords_lastmonth') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_30 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 30 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last month
if ($HTTP_GET_VARS['special'] == 'keywords') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report
if ($HTTP_GET_VARS['special'] == 'last_ten') {
if (isset($HTTP_GET_VARS['offset'])) $offset = $HTTP_GET_VARS['offset'];
else $offset = 0;
if (isset($HTTP_GET_VARS['refer_match'])) {
$match_refer_string = " and referrer like '%" . $HTTP_GET_VARS['refer_match'] . "%'";
$refer_match = $HTTP_GET_VARS['refer_match'];
}
else {
$match_refer_string = '';
$refer_match = '';
}
//MB: landing page
if (isset($HTTP_GET_VARS['landing_match'])) {
$match_landing_string = " and landing_page like '%" . $HTTP_GET_VARS['landing_match'] . "%'";
$landing_match = $HTTP_GET_VARS['landing_match'];
}
else {
$match_landing_string = '';
$landing_match = '';
}
//MB: End landing page
if (isset($HTTP_GET_VARS['filter'])) {
$filter = $HTTP_GET_VARS['filter'];
}
else $filter = 'all';
switch ($filter) {
case 'all' :
if ($refer_match == ''){
if ($landing_match == '') {
$lt_query = "select * from supertracker ORDER by last_click DESC LIMIT " . $offset . ",10";
}else {
$lt_query = "select * from supertracker where landing_page like '%" . $landing_match . "%' ORDER by last_click DESC LIMIT " . $offset . ",10";
}
}else $lt_query = "select * from supertracker where referrer like '%" . $refer_match . "%'" . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
case 'bailed' :
$lt_query = "select * from supertracker where added_cart = 'true' and completed_purchase = 'false' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
case 'completed' :
$lt_query = "select * from supertracker where completed_purchase = 'true' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
case 'completed' :
$lt_query = "select * from supertracker where completed_purchase = 'true' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
//MB Add exclude googlebot option
case 'google' :
$lt_query = "select * from supertracker where browser_string not like '%google%' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
} // end switch
$lt_result= xtc_db_query ($lt_query);
?>
<table width="100%" border=0 cellspacing=0 cellpadding=0>
<tr>
<td class="dataTableContent">
<form name="filter_select" action="supertracker.php" method="get" onchange="this.submit()">
<input type="hidden" name="special" value="last_ten">
Show : <select name="filter">
<option value="all" <?php if ($filter == 'all') echo 'selected';?>><?php echo TEXT_SHOW_ALL; ?></option>
<option value="bailed" <?php if ($filter == 'bailed') echo 'selected';?>><?php echo TEXT_BAILED_CARTS; ?></option>
<option value="completed" <?php if ($filter == 'completed') echo 'selected';?>><?php echo TEXT_SUCCESSFUL_CHECKOUTS; ?></option>
<?php //MB add exclude google ?>
<option value="google" <?php if ($filter == 'completed') echo 'selected';?>><?php echo TEXT_EXCLUDE_GOOGLE; ?></option>
</select>
<br><?php echo TEXT_AND_OR_ENTER; ?><input type="text" size="15" name="refer_match" value="<?php echo $refer_match;?>">
<input type="submit" value = "Update">
<br><?php //MB: add landing page search
echo TEXT_AND_LANDING; ?><input type="text" size="15" name="landing_match" value="<?php echo $landing_match;?>">
<input type="submit" value = "Update">
<?php //MB: end add landing page ?>
</form>
</td>
</tr>
</table>
<?php
while ($lt_row = xtc_db_fetch_array($lt_result)) {
$customer_ip = $lt_row['ip_address'];
$country_code = $lt_row['country_code'];
$country_name = $lt_row['country_name'];
$customer_id = $lt_row['customer_id'];
//MB add order id
$order_id = $lt_row['order_id'];
//MB end add order id
//MB Add agent tracking
$agent = $lt_row['agent'];
//MB end add agent tracking
if ($customer_id != 0) {
$cust_query = "select * from customers where customers_id ='" . $customer_id . "'";
$cust_result = xtc_db_query ($cust_query);
$cust_row = xtc_db_fetch_array($cust_result);
$customer_name = $cust_row['customers_firstname'] . ' ' . $cust_row['customers_lastname'];
}
else $customer_name = "Guest";
$referrer = $lt_row['referrer'] . '?' . $lt_row['referrer_query_string'];
if ($referrer == '?') $referrer = 'Direct Access / Bookmark';
$landing_page = $lt_row['landing_page'];
$last_page_viewed = $lt_row['exit_page'];
$time_arrived = $lt_row['time_arrived'];
$last_click = $lt_row['last_click'];
$num_clicks = $lt_row['num_clicks'];
$added_cart = $lt_row['added_cart'];
$completed_purchase = $lt_row['completed_purchase'];
$browser_string = $lt_row['browser_string'];
if ($lt_row['products_viewed'] != '') {
$products_viewed = $lt_row['products_viewed'];
$prod_view_array = explode ('*',$products_viewed);
}
else $products_viewed = '';
if($country_code==''){
$country_code='pixel_trans';
}
echo '<table width="100%" border=0 cellspacing=0 cellpadding=5 style="border:1px solid #000;">';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_IP . '</b><a href="http://www.showmyip.com/?ip=' . $customer_ip . '" target="_blank">' . $customer_ip . ' (' . $country_name . ')' . xtc_image(DIR_WS_IMAGES . 'geo_flags/' . $country_code . '.gif') . ' - ' . gethostbyaddr($customer_ip) . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_CUSTOMER_BROWSER_IDENT . '</b>' . $browser_string . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_NAME . '</b>' . $customer_name . '</td></tr>';
//MB add order number
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_ORDER . ':</b>
<a href="/shop/admin/orders.php?oID=' . $order_id . '">' . $order_id . '</a></td></tr>';
//MB end add order number
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_REFFERED_BY . '<a href="' . $referrer . '" target="_blank">' . $referrer . '</a></b></td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_AGENT . ':</b> ' . $agent . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_LANDING_PAGE . '</b>' . $landing_page . '</td></tr>'; //MB Add Agent tracking
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_LAST_PAGE_VIEWED . '</b>' . $last_page_viewed . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_TIME_ARRIVED . '</b>' . xtc_datetime_short($time_arrived) . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_LAST_CLICK . '</b>' . xtc_datetime_short($last_click) . '</td></tr>';
$time_on_site = strtotime($last_click) - strtotime($time_arrived);
$hours_on_site = floor($time_on_site /3600);
$minutes_on_site = floor( ($time_on_site - ($hours_on_site*3600)) / 60);
$seconds_on_site = $time_on_site - ($hours_on_site *3600) - ($minutes_on_site * 60);
$time_on_site = $hours_on_site . 'hrs ' . $minutes_on_site . 'mins ' . $seconds_on_site . ' seconds';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_TIME_ON_SITE . '</b>' . $time_on_site . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_NUMBER_OF_CLICKS . '</b>' . $num_clicks . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_ADDED_CART . '</b>' . $added_cart . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_COMPLETED_PURCHASE . '</b>' . $completed_purchase . '</td></tr>';
if ($completed_purchase == 'true') {
$order_q = "select ot.text as order_total from orders as o, orders_total as ot where o.orders_id=ot.orders_id and o.orders_id = '" . $lt_row['order_id'] . "' and ot.class='ot_total'";
$order_result = xtc_db_query($order_q);
if (xtc_db_num_rows($order_result)>0) {
$order_row = xtc_db_fetch_array($order_result);
echo '<tr><td class="dataTableContent">' . TABLE_TEXT_ORDER_VALUE . $order_row['order_total'] . '</td></tr>';
}
}
$categories_viewed = unserialize($lt_row['categories_viewed']);
if (!empty($categories_viewed)) {
$cat_string = '';
foreach ($categories_viewed as $cat_id=>$val) {
$cat_query = "select * from categories as c, categories_description as cd where c.categories_id=cd.categories_id and c.categories_id='" . $cat_id . "'";
$cat_result = xtc_db_query($cat_query);
$cat_row = xtc_db_fetch_array($cat_result);
$cat_string .= $cat_row['categories_name'] . ',';
}
$cat_string = rtrim($cat_string, ',');
echo '<tr><td class="dataTableContent"><strong>' . TABLE_TEXT_CATEGORIES . '</strong>' . $cat_string . '</td></tr>';
}
if ($products_viewed != '') {
echo '<tr><td class="dataTableContent"><strong>' . TABLE_TEXT_PRODUCTS . ' </strong><table cellspacing=0 cellpadding=0 border=0><tr>';
foreach ($prod_view_array as $key=>$product_id) {
$product_id = rtrim($product_id, '?');
if ($product_id != '') {
$prod_query = "select * from products as p, products_description as pd where p.products_id=pd.products_id and p.products_id='" . $product_id . "'";
$prod_result = xtc_db_query($prod_query);
$prod_row = xtc_db_fetch_array($prod_result);
echo '<td><table cellspacing=0 cellpadding=2 border=0 align="center" style="border:1px solid #000;"><tr><td align="center">' . xtc_image(DIR_WS_CATALOG_IMAGES . $prod_row['products_image'], $prod_row['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</td></tr><tr><td class="dataTableContent" align="center">' . $prod_row['products_name'] . '</td></tr></table></td>';
}
}
echo '</tr></table></td></tr>';
}
$cart_contents = unserialize($lt_row['cart_contents']);
if (!empty($cart_contents)) {
echo '<tr><td class="dataTableContent"><strong>' . TABLE_TEXT_CUSTOMERS_CART . '(value=' . $currency->format($lt_row['cart_total']) . ') : </strong><table cellspacing=0 cellpadding=0 border=0><tr>';
foreach ($cart_contents as $product_id => $qty_array) {
$prod_query = "select * from products as p, products_description as pd where p.products_id=pd.products_id and p.products_id='" . $product_id . "'";
$prod_result = xtc_db_query($prod_query);
$prod_row = xtc_db_fetch_array($prod_result);
echo '<td><table cellspacing=0 cellpadding=2 border=0 align="center" style="border:1px solid #000;"><tr><td align="center">' . xtc_image(DIR_WS_CATALOG_IMAGES . $prod_row['products_image'], $prod_row['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</td></tr><tr><td class="dataTableContent" align="center">' . $prod_row['products_name'] . '</td></tr><tr><td class="dataTableContent">' . TABLE_TEXT_QUANTITY . $qty_array['qty'] . '</td></tr></table></td>';
}
echo '</tr></table></td></tr>';
}
echo '</table>';
}//End While
?>
<strong><a href="supertracker.php?special=last_ten&offset=<?php echo $offset + 10;?>&filter=<?php echo $filter;?>&refer_match=<?php echo $refer_match;?>&landing_match=<?php echo $landing_match;?>"><?php echo TABLE_TEXT_NEXT_TEN_RESULTS; ?></a></strong>
<?php
}//End Special "Last Ten" Report
if ($HTTP_GET_VARS['special'] == 'ppc_summary') {
echo '<table width="100%" border=0 cellspacing=0 cellpadding=5 style="border:1px solid #000;">';
foreach ($ppc as $ref_code => $details) {
$scheme_name = $details['title'];
$keywords = $details['keywords'];
$ppc_q = "SELECT * from supertracker where landing_page like '%ref=" . $ref_code . "%'";
$ppc_result = xtc_db_query ($ppc_q);
$ppc_num_refs = xtc_db_num_rows($ppc_result);
echo '<tr><td style="font-weight:bold;text-decoration:underline;">' . $scheme_name . ' - Total Referrals ' . $ppc_num_refs . '</td></tr>';
if ($keywords != '') {
$keyword_array = explode(',',$keywords);
foreach ($keyword_array as $key => $val) {
$colon_pos = strpos ($val, ':');
$keyword_code = substr($val,0,$colon_pos);
$keyword_friendly_name = substr($val,$colon_pos+1,strlen($val)-$colon_pos);
$ppc_key_q = "SELECT *, count(*) as count, AVG(num_clicks) as ave_clicks, AVG(UNIX_TIMESTAMP(last_click) - UNIX_TIMESTAMP(time_arrived))/60 as ave_time from supertracker where landing_page like '%ref=" . $ref_code . "&keyw=" . $keyword_code . "%' group by landing_page";
$ppc_key_result = xtc_db_query($ppc_key_q);
$ppc_row = xtc_db_fetch_array($ppc_key_result);
$ppc_key_refs = $ppc_row['count'];
echo '<tr><td>' . $keyword_friendly_name . ' : ' . $ppc_key_refs . TABLE_TEXT_REFERRALS_AVERAGE_TIME . $ppc_row['ave_time'] . TABLE_TEXT_MINS_AVERAGE_CLICKS . $ppc_row['ave_clicks'] . '</td></tr>';
}
}
}
echo '</table>';
}//End PPC Summary Report
if ($HTTP_GET_VARS['special'] == 'geo') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_COUNTRY . '</td></tr>';
$geo_query = "select count(*) as count, country_code, country_name from supertracker GROUP by country_code";
$geo_result = xtc_db_query($geo_query);
$geo_hits = array();
$country_names = array();
$total_hits = 0;
while ($geo_row = xtc_db_fetch_array($geo_result)) {
$total_hits += $geo_row['count'];
$country_code = strtolower($geo_row['country_code']);
$geo_hits[$country_code] = $geo_row['count'];
$country_names[$country_code] = $geo_row['country_name'];
}
draw_geo_graph($geo_hits,$country_names,$total_hits);
}//End Geo Report
if ($HTTP_GET_VARS['special'] == 'prod_coverage') {
if (isset($HTTP_GET_VARS['agent_match'])) {
$agent_match = $HTTP_GET_VARS['agent_match'];
$match_agent_string = " and browser_string like '%" . $agent_match . "%'";
}
else {
$match_agent_string = '';
$agent_match = '';
}
?>
<table width="100%" border=0 cellspacing=0 cellpadding=0>
<tr>
<td class="dataTableContent">
<form name="filter_select" action="supertracker.php" method="get" onchange="this.submit()">
<input type="hidden" name="special" value="prod_coverage">
<?php echo TEXT_STRING_FILTER; // modified by azer ?>
<input type="text" size="15" name="agent_match" value="<?php echo $agent_match;?>">
<input type="submit" value = "Update">
</form>
</td>
</tr>
</table>
<?php
$prod_q = "select p.products_id, pd.products_name from products as p, products_description as pd where p.products_id=pd.products_id and p.products_status='1'";
$prod_result = xtc_db_query($prod_q);
$prod_coverage = array();
while ($prod_row = xtc_db_fetch_array($prod_result)) {
$cov_q = "select * from supertracker where products_viewed like '%" . $prod_row['products_id'] . "%'" . $match_agent_string ;
$cov_result = xtc_db_query($cov_q);
$prod_coverage[$prod_row['products_name']] = xtc_db_num_rows($cov_result);
} // End While loop
arsort($prod_coverage);
?>
<table cellpadding=2 cellspacing=0 border=0 width="100%">
<tr><td class="pageHeading" colspan=2 align="left"><?php echo TABLE_TEXT_PRODUCT_COVERAGE_REPORT; ?></td></tr>
<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent"><?php echo TABLE_TEXT_PRODUCT_NAME; ?></td><td class="dataTableHeadingContent"><?php echo TABLE_TEXT_NUMBER_OF_VIEWING; ?></td></tr>
<?php
foreach ($prod_coverage as $prod_name => $hits) {
echo '<tr><td class="dataTableContent">' . $prod_name . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
<?php
} // End Product Coverage Report
}
?>
</td>
<!-- body_text_eof //-->
</tr>
</table>
<!-- body_eof //-->
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
********************************************
END
Thanks very much for any help!!!!!!!!
Im a beginner in programming and cant find the solution to my problem.
I want to use this two PHP files in my shopsystem, but at some place the code is wrong and cant access the database: MySQL 4.0...
Will publish here the code, maybe some crack can see easy what must be done to run this code proper!
Error Code:
1054 - Unknown column 'supertracker' in 'field list'
select supertracker from admin_access where customers_id = '1'
[XT SQL Error]
File #1:
START
***********************************************************
class supertracker {
function supertracker(){
}
function update() {
global $cart, $HTTP_GET_VARS, $customer_id;
//// **** CONFIGURATION SECTION **** ////
//Comma Separate List of IPs which should not be recorded, for instance, your own PCs IP address, or
//that of your server if you are using Cron Jobs, etc
$excluded_ips = '';
//// **** CONFIGURATION SECTION EOF **** ////
$record_session = true;
$ip_address = $_SERVER['REMOTE_ADDR'];
if ($excluded_ips != '') {
$ex_array = explode(',',$excluded_ips);
foreach ($ex_array as $key => $ex_ip) {
if ($ip_address == $ex_ip) $record_session = false;
}
}
/* EXCLUDE BOTS BEGIN - REMOVE THIS LINE IF YOU WANT TO EXCLUDE BOTS FROM THE STATS
$agent = $_SERVER['HTTP_USER_AGENT'];
$bot_here = false;
$arr = array("msnbot", "googlebot", "slurp", "abot", "dbot", "ebot", "hbot", "kbot", "mbot", "nbot", "obot", "pbot", "rbot", "sbot", "tbot", "ybot", "bot.", "crawl", "slurp", "spider", "accoona", "acoon", "ah-ha.com", "ahoy", "altavista", "ananzi", "anthill", "appie", "arachnophilia", "arale", "araneo", "aranha", "architext", "aretha", "arks", "aspseek", "asterias", "atlocal", "atn", "atomz", "augurfind", "backrub", "bannana_bot", "big brother", "bjaaland", "blackwidow", "bloodhound", "boitho", "booch", "bradley", "calif", "cassandra", "ccubee", "cfetch", "churl", "cienciaficcion", "cmc", "collective", "combine system", "computingsite", "cusco", "deepindex", "deweb", "die blinde kuh", "digger", "dmoz", "docomo", "download express", "dwcp", "ebiness", "e-collector", "ejupiter", "emacs-w3 search engine", "esther", "evliya celebi", "ezresult", "falcon", "felix ide", "ferret", "fetchrover", "fido", "findlinks", "fireball", "fish search", "fouineur", "funnelweb", "gazz", "gcreep", "getterroboplus", "geturl", "glx", "goforit", "golem", "grabber", "grapnel", "griffon", "gromit", "grub", "gulliver", "hamahakki", "harvest", "havindex", "helix", "heritrix", "hku www octopus", "homerweb", "htdig", "html index", "html_analyzer", "htmlgobble", "hubater", "hyper-decontextualizer", "ia_archiver", "ibm_planetwide", "ichiro", "iconsurf", "iltrovatore", "image.kapsi.net", "imagelock", "incywincy", "indexer", "infobee", "informant", "infoseek", "ingrid", "inktomisearch.com", "inspector web", "intelliagent", "internet shinchakubin", "ip3000", "iron33", "israeli-search", "ivia", "jack", "jakarta", "java/", "javabee", "jetbot", "jumpstation", "katipo", "kdd-explorer", "kilroy", "knowledge", "kototoi", "labelgrabber", "lachesis", "larbin", "legs", "libwww", "linkalarm", "link validator", "linkscan", "linkwalker", "lockon", "lwp", "lycos", "magpie", "mantraagent", "marvin/", "mattie", "mediafox", "mediapartners", "mercator", "merzscope", "miva", "mj12", "mnogosearch", "moget", "monster", "moose", "motor", "multitext", "muncher", "muscatferret", "mwd.search", "myweb", "nameprotect", "nationaldirectory", "nazilla", "ncsa beta", "nec-meshexplorer", "nederland.zoek", "netcarta webmap engine", "netmechanic", "netresearchserver", "netscoop", "newscan-online", "ng/", "nhse", "nomad", "nutch", "nzexplorer", "objectssearch", "occam", "omni", "open text", "openfind", "orb search", "osis-project", "pack rat", "pageboy", "parasite", "partnersite", "patric", "pear.", "pegasus", "peregrinator", "pgp key agent", "phantom", "phpdig", "picosearch", "piltdownman", "pimptrain", "pinpoint", "pioneer", "piranha", "plumtreewebaccessor", "pompos", "poppelsdorf", "poppi", "popular iconoclast", "rambler", "raven search", "roach", "road runner", "roadhouse", "robbie", "robofox", "robozilla", "rules", "salty", "sbider", "scooter", "scrubby", "search.", "searchprocess", "seeker", "semanticdiscovery", "senrigan", "sg-scout", "shai'hulud", "shark", "shopwiki", "sidewinder", "sift", "simmany", "site searcher", "site valet", "sitetech-rover", "skymob.com", "sleek", "smartwit", "sna-", "snooper", "sohu", "speedfind", "spinner", "spyder", "steeler/", "suke", "suntek", "supersnooper", "surfnomore", "sven", "sygol", "szukacz", "tach black widow", "tarantula", "templeton", "/teoma", "t-h-u-n-d-e-r-s-t-o-n-e", "titan", "titin", "tkwww", "toutatis", "t-rex", "tutorgig", "ucsd", "udmsearch", "ultraseek", "url check", "vagabondo", "valkyrie", "verticrawl", "victoria", "vision-search", "volcano", "voyager/", "w3c_validator", "w3m2", "w3mir", "wallpaper", "wanderer", "web core", "web hopper", "web wombat", "webbandit", "webcatcher", "webcopy", "webfoot", "weblayers", "weblinker", "weblog monitor", "webmirror", "webquest", "webreaper", "websitepulse", "websnarf", "webstolperer", "webvac", "webwalk", "webwatch", "webwombat", "webzinger", "wget", "whatuseek", "whizbang", "whowhere", "wild ferret", "wire", "worldlight", "wwwc", "xenu", "xget", "xift", "yandex", "zao/", "zippp", "zyborg");
foreach ($arr as $bot) {
if (eregi($bot, $agent)) {
$bot_here = true;
}
}
if ($bot_here) {
$record_session = false;
}
EXCLUDE BOTS END OF SECTION - REMOVE THIS LINE IF YOU WANT TO EXCLUDE BOTS FROM THE STATS */
//Big If statement that stops us doing anything more if this IP is one of the
//ones we have chosen to exclude
if ($record_session) {
$existing_session = false;
$thirty_ago_timestamp = strtotime("now") - (30*60);
$thirty_mins_ago = date('Y-m-d H:i:s', $thirty_ago_timestamp);
$browser_string = addslashes(tep_db_input(urldecode($_SERVER['HTTP_USER_AGENT'])));
$ip_array = explode ('.',$ip_address);
$ip_start = $ip_array[0] . '.' . $ip_array[1];
//Find out if this user already appears in the supertracker db table
//First thing to try is customer_id, if they are signed in
if (isset($customer_id)) {
$query = "select * from supertracker where customer_id ='" . $customer_id . "' and last_click > '" . $thirty_mins_ago . "'";
$result = tep_db_query($query);
if (tep_db_num_rows ($result) > 0) {
$existing_session = true;
}
}
//Next, we try this: compare first 2 parts of the IP address (Class B), and the browser
//Identification String, which give us a good chance of locating the details for a given user. I reckon
//that the chances of having more than 1 user within a 30 minute window with identical values
//is pretty small, so hopefully this will work and should be more reliable than using Session IDs....
if (!$existing_session) {
$query = "select * from supertracker where browser_string ='" . $browser_string . "' and ip_address like '" . $ip_start . "%' and last_click > '" . $thirty_mins_ago . "'";
$result = tep_db_query($query);
if (tep_db_num_rows ($result) > 0) {
$existing_session = true;
}
}
//If that didn't work, and we have something in the cart, we can use that to try and find the
//record instead. Obviously, people with things in their cart don't just appear from nowhere!
if (!$existing_session) {
if ($cart->count_contents()>0) {
$query = "select * from supertracker where cart_total ='" . $cart->show_total() . "' and last_click > '" . $thirty_mins_ago . "'";
$result = tep_db_query($query);
if (tep_db_num_rows ($result) > 0) {
$existing_session = true;
}
}
}
//Having worked out if we have a new or existing user session lets record some details....!
if ($existing_session) {
//Existing tracked session, so just update relevant existing details
$tracking_data = tep_db_fetch_array($result);
$tracking_id = $tracking_data['tracking_id'];
$products_viewed=$tracking_data['products_viewed'];
$added_cart = $tracking_data['added_cart'];
$completed_purchase = $tracking_data['completed_purchase'];
$num_clicks = $tracking_data['num_clicks']+1;
$categories_viewed = unserialize($tracking_data['categories_viewed']);
$cart_contents = unserialize($tracking_data['cart_contents']);
$cart_total = $tracking_data['cart_total'];
$order_id = $tracking_data['order_id'];
if (isset($customer_id)) $cust_id=$customer_id;
else $cust_id=$tracking_data['customer_id'];
$current_page=addslashes(tep_db_input(urldecode($_SERVER['PHP_SELF'])));
$last_click = date('Y-m-d H:i:s');
//Find out if the customer has added something to their cart for the first time
if (($added_cart!='true') && ($cart->count_contents()>0)) $added_cart = 'true';
//Has a purchase just been completed?
if ((strstr($current_page, 'checkout_success.php'))&& ($completed_purchase!='true')) {
$completed_purchase='true';
$order_q = "select orders_id from orders where customers_id = '" . $cust_id . "' ORDER BY date_purchased DESC";
$order_result = tep_db_query($order_q);
if (tep_db_num_rows($order_result) > 0) {
$order_row = tep_db_fetch_array($order_result);
$order_id = $order_row['orders_id'];
}
}
//If customer is looking at a product, add it to the list of products viewed
if (strstr($current_page, 'product_info.php')) {
$current_product_id = (int)$HTTP_GET_VARS['products_id'];
if (!strstr($products_viewed, '*' . $current_product_id . '?')) {
//Product hasn't been previously recorded as viewed
$products_viewed .= '*' . $current_product_id . '?';
}
}
//Store away their cart contents
//But, the cart is dumped at checkout, so we don't want to overwrite the stored cart contents
//In this case
$current_cart_contents = serialize($cart->contents);
if (strlen($current_cart_contents)>6) {
$cart_contents = $current_cart_contents;
$cart_total = $cart->show_total();
}
//If we are on index.php, but looking at category results, make sure we record which category
if (strpos($current_page, 'index.php')) {
if (isset($_GET['cPath'])) {
$cat_id = $_GET['cPath'];
$cat_id_array = explode('_',$cat_id);
$cat_id = $cat_id_array[sizeof($cat_id_array)-1];
$categories_viewed[$cat_id]=1;
}
}
$categories_viewed = serialize($categories_viewed);
$query = "UPDATE supertracker set last_click='" . $last_click . "', exit_page='" . $current_page . "', num_clicks='" . $num_clicks . "', added_cart='" . $added_cart . "', categories_viewed='" . $categories_viewed . "', products_viewed='" . $products_viewed . "', customer_id='" . $cust_id . "', completed_purchase='" . $completed_purchase . "', cart_contents='" . $cart_contents . "', cart_total = '" . $cart_total . "', order_id = '" . $order_id . "' where tracking_id='" . $tracking_id . "'";
tep_db_query($query);
}
else {
//New vistor, so record referrer, etc
//Next line defines pages on which a new visitor should definitely not be recorded
$prohibited_pages = 'login.php,checkout_shipping.php,checkout_payment.php,checkout_process.php,checkout_confirmation.php,checkout_success.php';
$current_page=addslashes(tep_db_input(urldecode($_SERVER['PHP_SELF'])));
if (!strpos($prohibited_pages, $current_page)) {
$refer_data = addslashes(tep_db_input(urldecode($_SERVER['HTTP_REFERER'])));
$refer_data = explode('?', $refer_data);
$referrer=$refer_data[0];
$query_string=$refer_data[1];
$ip = $_SERVER['REMOTE_ADDR'];
$browser_string = addslashes(tep_db_input(urldecode($_SERVER['HTTP_USER_AGENT'])));
include(DIR_WS_INCLUDES . "geoip.inc");
$gi = geoip_open(DIR_WS_INCLUDES . "GeoIP.dat",GEOIP_STANDARD);
$country_name = geoip_country_name_by_addr($gi, $ip);
$country_code = strtolower(geoip_country_code_by_addr($gi, $ip));
geoip_close($gi);
$time_arrived = date('Y-m-d H:i:s');
$landing_page = addslashes(tep_db_input(urldecode($_SERVER['REQUEST_URI'])));
$query = "INSERT INTO `supertracker` (`ip_address`, `browser_string`, `country_code`, `country_name`, `referrer`,`referrer_query_string`,`landing_page`,`exit_page`,`time_arrived`,`last_click`) VALUES ('" . $ip . "','" . $browser_string . "','" . $country_code . "', '" . $country_name . "', '" . $referrer . "', '" . $query_string . "','" . $landing_page . "','" . $current_page . "','" . $time_arrived . "','" . $time_arrived . "')";
tep_db_query($query);
}//end if for prohibited pages
}//end else
}//End big If statement (Record Exclusion for certain IPs)
}//End function update
}//End Class
?>
***************************************************************
END
***************************************************************
File #2 , display the result
START
***************************************************************
// ********** PAY PER CLICK CONFIGURATION SECTION ************
// Pay per click referral URLs used - to make this work you have to set up your pay-per-click
// URLs like this : http://www.yoursite.com/catalog/index.php?ref=xxx&keyw=yyy
// where xxx is a code representing the PPC service and yyy is the keyword being used
// to generate that referral. Here's an example :
// http://www.yoursite.com/catalog/index.php?ref=googled&keyw=gameboy
// which might be used for the keyword "gameboy" in a google adwords campaign.
// The keyword part is optional - if you don't use it in a particular campaign, you
// Just set up the $ppc array like that in the example for googlead below
$ppc = array ('googlead' => array ('title' => 'Google Adwords', 'keywords' => 'shortcode1:friendlyterm1,shortcode1:friendlyterm2'));
//Set the following to true to enable the PPC referrer report
//Eventually, this will probably be moved into the configuration menu
//in admin, where it really should be!
define ('SUPERTRACKER_USE_PPC', false);
// ********** PAY PER CLICK CONFIGURATION SECTION EOF ************
require('includes/application_top.php');
require(DIR_WS_LANGUAGES . $language . '/supertracker.php');
include ('includes/classes/currencies.php');
$currency = new currencies();
function draw_geo_graph($geo_hits,$country_names,$total_hits) {
echo '<table cellpadding=2 cellspacing=0 border=0>';
$max_pixels = 200;
arsort($geo_hits);
foreach ($geo_hits as $country_code=>$num_hits) {
$country_name = $country_names[$country_code];
$bar_length = ($num_hits/$total_hits) * $max_pixels;
$percent_hits = round(($num_hits/$total_hits) * 100,2);
//Create a random colour for each bar
srand((double)microtime()*1000000);
$r = dechex(rand (0, 255));
$g = dechex(rand (0, 255));
$b = dechex(rand (0, 255));
echo '<tr><td>' . $country_name . ': </td><td><div style="display:justify;background:#' . $r . $g . $b . ';border:1px solid #000;height:10px;width:' . $bar_length . '"></div></td><td>' . $percent_hits . '%</td></tr>';
}
echo '</table>';
}//end function
if (isset($_GET['action'])) $action = $_GET['action'];
if ($action == 'del_rows') {
$rows_to_delete = $_POST['num_rows'];
$del_query = "DELETE from supertracker ORDER by tracking_id ASC LIMIT " . $rows_to_delete;
$del_result = xtc_db_query ($del_query);
}
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script language="javascript">
function page_redirect(url) {
url=url.value;
location.href = url;
}
</script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
<tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
</table></td>
<!-- body_text //-->
<td width="100%" valign="top">
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td>
<div class="supertracker_title">Supertracker</div>
<?php /* comented by azer
<div class="supertracker_contact">
<strong><?php echo TEXT_EXTERNAL_LINK;?></strong><br>
<a class="supertracker_link" href="http://forums.oscommerce.com/index.php?showtopic=166141"><?php echo TEXT_SUPPORT; ?></a>
<a class="supertracker_link" href="http://forums.oscommerce.com/index.php?showtopic=166839"><?php echo TEXT_THREAD; ?></a><br>
*/ ?>
</div>
<div class="supertracker_contact">
<strong><?php echo TEXT_DATABASE_INFO; ?></strong>
<?php
$maint_query = "select tracking_id, time_arrived from supertracker order by tracking_id ASC";
$maint_result = xtc_db_query($maint_query);
$num_rows = xtc_db_num_rows($maint_result);
$maint_row = xtc_db_fetch_array($maint_result);
echo '<span class="supertracker_text">' . sprintf(TEXT_TABLE_DATABASE, $num_rows, xtc_date_short($maint_row['time_arrived'])) . '</span><br>';
echo '<form name="del_rows" action="supertracker.php?action=del_rows" method="post">' . TEXT_TABLE_DELETE . '<input name="num_rows" size=10><input type="submit" value="' . TEXT_BUTTON_GO . '"></form>';
?>
</div>
<div class="supertracker_text">
<?php echo TABLE_TEXT_MENU_DESC_TEXTE; ?> <form name="report_select"><select name="report_selector" onchange="page_redirect(this)">
<option value=""><?php echo TABLE_TEXT_MENU_TEXTE; ?></option>
<option value="supertracker.php?report=refer"><?php echo TEXT_TOP_REFERRERS; ?></option>
<option value="supertracker.php?report=success_refer"><?php echo TEXT_TOP_SALES;?></option>
<option value="supertracker.php?special=geo"><?php echo TEXT_VISITORS;?></option>
<option value="supertracker.php?special=keywords"><?php echo TEXT_SEARCH_KEYWORDS;?></option>
<option value="supertracker.php?special=keywords_last24"><?php echo TEXT_SEARCH_KEYWORDS_24;?></option>
<option value="supertracker.php?special=keywords_last72"><?php echo TEXT_SEARCH_KEYWORDS_3;?></option>
<option value="supertracker.php?special=keywords_lastweek"><?php echo TEXT_SEARCH_KEYWORDS_7;?></option>
<option value="supertracker.php?special=keywords_lastmonth"><?php echo TEXT_SEARCH_KEYWORDS_30;?></option>
<option value="supertracker.php?report=exit"><?php echo TEXT_TOP_EXIT_PAGES;?></option>
<option value="supertracker.php?report=exit_added"><?php echo TEXT_TOP_EXIT_PAGES_NO_SALE;?></option>
<option value="supertracker.php?report=ave_clicks"><?php echo TEXT_AVERAGE_CLICKS;?></option>
<option value="supertracker.php?report=ave_time"><?php echo TEXT_AVERAGE_TIME_SPENT;?></option>
<option value="supertracker.php?special=prod_coverage"><?php echo TEXT_PRODUCTS_VIEWED_REPORT;?></option> <!-- modifed by azer//-->
<option value="supertracker.php?special=last_ten"><?php echo TEXT_LAST_TEN_VISITORS;?></option>
<option value="supertracker.php?report=agent"><?php echo TEXT_TOP_AGENTS; //MB: Add agent tracking?></option>
<?php if (SUPERTRACKER_USE_PPC) {?>
<option value="supertracker.php?special=ppc_summary"><?php echo TEXT_PPC_REFERRAL;?></option>
<?php } ?>
</select>
<input type="submit" value="Go"></form>
</div>
</div>
</td>
</tr>
<?php
//MB: Add agent tracking
if (isset($HTTP_GET_VARS['byagent'])){
$groupby = 'agent';
} else {
$groupby = 'referrer';
} //MB end
if (isset($HTTP_GET_VARS['report'])) {
$report=$HTTP_GET_VARS['report'];
$headings=array();
$row_data=array();
if ($report=='refer') {
$title = TEXT_TOP_REFFERING_URL;
$headings[]=TEXT_RANKING;
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_NUMBER_OF_HITS;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker GROUP BY ' . $groupby . ' order by total DESC'; //MB Add agent tracking
}
if ($report=='success_refer') {
$title = TEXT_SUCCESSFUL;
$headings[]=TEXT_SERIAL;
//MB Add agent tracking
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_NUMBER_OF_SALES;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker WHERE completed_purchase = "true" group by '. $groupby . ' order by total DESC'; //MB Add agent tracking
}
if ($report=='exit') {
$title =TEXT_TOP_PAGES_EXIT;
$headings[]=TEXT_SERIAL;
$headings[]=TEXT_EXIT_PAGE;
$headings[]=TEXT_NUMBER_OF_OCCURRENCES;
$row_data[]='exit_page';
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker where completed_purchase="false" group by exit_page order by total DESC';
}
if ($report=='exit_added') {
$title = TEXT_TOP_EXIT_PAGES_NO_SALE;
$headings[]=TEXT_SERIAL;
$headings[]=TEXT_EXIT_PAGE;
$headings[]=TEXT_NUMBER_OF_OCCURRENCES;
$row_data[]='exit_page';
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker where completed_purchase="false" and added_cart="true" group by exit_page order by total DESC';
}
if ($report=='ave_clicks') {
$title = TEXT_CLICKS_BY_REFFERRER_REPORT;
$headings[]=TEXT_SERIAL;
//MB Add agent tracking
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_NUMBER_OF_CLICKS;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='ave_clicks';
$tracker_query_raw='SELECT *, AVG(num_clicks) as ave_clicks FROM supertracker group by '. $groupby . ' order by ave_clicks DESC';//MB Add agent tracking
}
if ($report=='ave_time') {
$title = TEXT_AVERAGE_TIME_ON_SITE_BY;
$headings[]=TEXT_SERIAL;
//MB Add agent tracking
if ($groupby == 'agent') {
$headings[] = TEXT_BY_AGENT;
}
else {
$headings[]=TEXT_REFFERING_URL;
}
$headings[]=TEXT_AVERAGE_LENGTH_OF_TIME;
$row_data[]=$groupby; //MB Add agent tracking
$row_data[]='ave_time';
$tracker_query_raw='SELECT *, AVG(UNIX_TIMESTAMP(last_click) - UNIX_TIMESTAMP(time_arrived))/60 as ave_time FROM supertracker group by '. $groupby . ' order by ave_time DESC'; //MB Add agent tracking
}
//MB: Add agent tracking
if ($report=='agent') {
$title = TEXT_TOP_AGENTS;
$headings[]=TEXT_RANKING;
$headings[]=TEXT_TOP_AGENTS;
$headings[]=TEXT_NUMBER_OF_HITS;
$row_data[]='agent';
$row_data[]='total';
$tracker_query_raw='SELECT *, COUNT(*) as total FROM supertracker GROUP BY agent order by total DESC';
}
//MB: end
$tracker_query = xtc_db_query($tracker_query_raw);
?>
<tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td class="pageHeading"><?php echo $title; ?></td>
<td class="pageHeading" align="right"><?php echo xtc_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
</tr>
</table></td>
</tr>
<tr>
<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr class="dataTableHeadingRow">
<?php
foreach ($headings as $h) {
echo '<td class="dataTableHeadingContent">' . $h . '</td>';
}
?>
</tr>
<?php
$counter = 0;
while ($tracker = xtc_db_fetch_array($tracker_query)) {
$counter++;
?>
<tr class="dataTableRow">
<td class="dataTableContent"><?php echo $counter?></td>
<?php
foreach ($row_data as $r) {
if (strlen($tracker[$r]) > 50) $tracker[$r] = substr($tracker[$r],0,50);
echo '<td class="dataTableContent"' . $style_override . '>' . $tracker[$r] . '</td>';
}
?>
</tr>
<?php
}
?>
</table></td>
</tr>
</table></td>
</tr>
</table>
<?php
//MB Add agent tracking report option
if ($groupby == 'referrer'){
echo '<br>' . TEXT_THIS_REPORT . ' ('.$report . $special. ') <form name="filter_agent" action="supertracker.php" method="get" onchange="this.submit()">
<input type="hidden" name="special" value="' . $special . '">
<input type="hidden" name="report" value="'. $report . '">
<input type="hidden" name="byagent" value="1">
<input type="submit" value = "' . TEXT_BY_AGENT . '"><br>
<form>';
} //MB agent tracking
} //End if
if (isset($HTTP_GET_VARS['special'])) {
if ($HTTP_GET_VARS['special'] == 'keywords_last24') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_24 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 1 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last 24h
if ($HTTP_GET_VARS['special'] == 'keywords_last72') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_3 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 3 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last 72h
if ($HTTP_GET_VARS['special'] == 'keywords_lastweek') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_7 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 7 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last 7d
if ($HTTP_GET_VARS['special'] == 'keywords_lastmonth') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE_30 . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker where DATE_ADD(time_arrived, INTERVAL 30 DAY) >= now() ";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report last month
if ($HTTP_GET_VARS['special'] == 'keywords') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_KEY_PHRASE . '</td><td class="dataTableHeadingContent">' . TEXT_NUMBER_OF_HITS . '</td></tr>';
$keywords_used = array();
$keyword_query = "select * from supertracker";
$keyword_result = xtc_db_query ($keyword_query);
while ($keywords = xtc_db_fetch_array($keyword_result)) {
$key_array = explode ('&', $keywords[referrer_query_string]);
for ($i=0; $i<sizeof($key_array); $i++) {
if (substr($key_array[$i], 0,2) == 'q=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (substr($key_array[$i], 0,2) == 'p=') {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],2, strlen($key_array[$i])-2))] +=1;
}
if (strstr($key_array[$i], 'query=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],6, strlen($key_array[$i])-6))] +=1;
}
if (strstr($key_array[$i], 'keyword=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],8, strlen($key_array[$i])-8))] +=1;
}
if (strstr($key_array[$i], 'keywords=')) {
$keywords_used[str_replace('+', ' ', substr($key_array[$i],9, strlen($key_array[$i])-9))] +=1;
}
}
}
//Need a function to sort $keywords_used into order of no. of hits at some stage!
arsort($keywords_used);
foreach ($keywords_used as $kw=>$hits) {
echo '<tr class="dataTableRow"><td class="dataTableContent">' . $kw . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
</td>
</tr>
<?php
}//End Keywords Report
if ($HTTP_GET_VARS['special'] == 'last_ten') {
if (isset($HTTP_GET_VARS['offset'])) $offset = $HTTP_GET_VARS['offset'];
else $offset = 0;
if (isset($HTTP_GET_VARS['refer_match'])) {
$match_refer_string = " and referrer like '%" . $HTTP_GET_VARS['refer_match'] . "%'";
$refer_match = $HTTP_GET_VARS['refer_match'];
}
else {
$match_refer_string = '';
$refer_match = '';
}
//MB: landing page
if (isset($HTTP_GET_VARS['landing_match'])) {
$match_landing_string = " and landing_page like '%" . $HTTP_GET_VARS['landing_match'] . "%'";
$landing_match = $HTTP_GET_VARS['landing_match'];
}
else {
$match_landing_string = '';
$landing_match = '';
}
//MB: End landing page
if (isset($HTTP_GET_VARS['filter'])) {
$filter = $HTTP_GET_VARS['filter'];
}
else $filter = 'all';
switch ($filter) {
case 'all' :
if ($refer_match == ''){
if ($landing_match == '') {
$lt_query = "select * from supertracker ORDER by last_click DESC LIMIT " . $offset . ",10";
}else {
$lt_query = "select * from supertracker where landing_page like '%" . $landing_match . "%' ORDER by last_click DESC LIMIT " . $offset . ",10";
}
}else $lt_query = "select * from supertracker where referrer like '%" . $refer_match . "%'" . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
case 'bailed' :
$lt_query = "select * from supertracker where added_cart = 'true' and completed_purchase = 'false' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
case 'completed' :
$lt_query = "select * from supertracker where completed_purchase = 'true' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
case 'completed' :
$lt_query = "select * from supertracker where completed_purchase = 'true' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
//MB Add exclude googlebot option
case 'google' :
$lt_query = "select * from supertracker where browser_string not like '%google%' " . $match_refer_string . $match_landing_string . " ORDER by last_click DESC LIMIT " . $offset . ",10";
break;
} // end switch
$lt_result= xtc_db_query ($lt_query);
?>
<table width="100%" border=0 cellspacing=0 cellpadding=0>
<tr>
<td class="dataTableContent">
<form name="filter_select" action="supertracker.php" method="get" onchange="this.submit()">
<input type="hidden" name="special" value="last_ten">
Show : <select name="filter">
<option value="all" <?php if ($filter == 'all') echo 'selected';?>><?php echo TEXT_SHOW_ALL; ?></option>
<option value="bailed" <?php if ($filter == 'bailed') echo 'selected';?>><?php echo TEXT_BAILED_CARTS; ?></option>
<option value="completed" <?php if ($filter == 'completed') echo 'selected';?>><?php echo TEXT_SUCCESSFUL_CHECKOUTS; ?></option>
<?php //MB add exclude google ?>
<option value="google" <?php if ($filter == 'completed') echo 'selected';?>><?php echo TEXT_EXCLUDE_GOOGLE; ?></option>
</select>
<br><?php echo TEXT_AND_OR_ENTER; ?><input type="text" size="15" name="refer_match" value="<?php echo $refer_match;?>">
<input type="submit" value = "Update">
<br><?php //MB: add landing page search
echo TEXT_AND_LANDING; ?><input type="text" size="15" name="landing_match" value="<?php echo $landing_match;?>">
<input type="submit" value = "Update">
<?php //MB: end add landing page ?>
</form>
</td>
</tr>
</table>
<?php
while ($lt_row = xtc_db_fetch_array($lt_result)) {
$customer_ip = $lt_row['ip_address'];
$country_code = $lt_row['country_code'];
$country_name = $lt_row['country_name'];
$customer_id = $lt_row['customer_id'];
//MB add order id
$order_id = $lt_row['order_id'];
//MB end add order id
//MB Add agent tracking
$agent = $lt_row['agent'];
//MB end add agent tracking
if ($customer_id != 0) {
$cust_query = "select * from customers where customers_id ='" . $customer_id . "'";
$cust_result = xtc_db_query ($cust_query);
$cust_row = xtc_db_fetch_array($cust_result);
$customer_name = $cust_row['customers_firstname'] . ' ' . $cust_row['customers_lastname'];
}
else $customer_name = "Guest";
$referrer = $lt_row['referrer'] . '?' . $lt_row['referrer_query_string'];
if ($referrer == '?') $referrer = 'Direct Access / Bookmark';
$landing_page = $lt_row['landing_page'];
$last_page_viewed = $lt_row['exit_page'];
$time_arrived = $lt_row['time_arrived'];
$last_click = $lt_row['last_click'];
$num_clicks = $lt_row['num_clicks'];
$added_cart = $lt_row['added_cart'];
$completed_purchase = $lt_row['completed_purchase'];
$browser_string = $lt_row['browser_string'];
if ($lt_row['products_viewed'] != '') {
$products_viewed = $lt_row['products_viewed'];
$prod_view_array = explode ('*',$products_viewed);
}
else $products_viewed = '';
if($country_code==''){
$country_code='pixel_trans';
}
echo '<table width="100%" border=0 cellspacing=0 cellpadding=5 style="border:1px solid #000;">';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_IP . '</b><a href="http://www.showmyip.com/?ip=' . $customer_ip . '" target="_blank">' . $customer_ip . ' (' . $country_name . ')' . xtc_image(DIR_WS_IMAGES . 'geo_flags/' . $country_code . '.gif') . ' - ' . gethostbyaddr($customer_ip) . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_CUSTOMER_BROWSER_IDENT . '</b>' . $browser_string . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_NAME . '</b>' . $customer_name . '</td></tr>';
//MB add order number
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_ORDER . ':</b>
<a href="/shop/admin/orders.php?oID=' . $order_id . '">' . $order_id . '</a></td></tr>';
//MB end add order number
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_REFFERED_BY . '<a href="' . $referrer . '" target="_blank">' . $referrer . '</a></b></td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_AGENT . ':</b> ' . $agent . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_LANDING_PAGE . '</b>' . $landing_page . '</td></tr>'; //MB Add Agent tracking
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_LAST_PAGE_VIEWED . '</b>' . $last_page_viewed . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_TIME_ARRIVED . '</b>' . xtc_datetime_short($time_arrived) . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_LAST_CLICK . '</b>' . xtc_datetime_short($last_click) . '</td></tr>';
$time_on_site = strtotime($last_click) - strtotime($time_arrived);
$hours_on_site = floor($time_on_site /3600);
$minutes_on_site = floor( ($time_on_site - ($hours_on_site*3600)) / 60);
$seconds_on_site = $time_on_site - ($hours_on_site *3600) - ($minutes_on_site * 60);
$time_on_site = $hours_on_site . 'hrs ' . $minutes_on_site . 'mins ' . $seconds_on_site . ' seconds';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_TIME_ON_SITE . '</b>' . $time_on_site . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_NUMBER_OF_CLICKS . '</b>' . $num_clicks . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_ADDED_CART . '</b>' . $added_cart . '</td></tr>';
echo '<tr><td class="dataTableContent"><b>' . TABLE_TEXT_COMPLETED_PURCHASE . '</b>' . $completed_purchase . '</td></tr>';
if ($completed_purchase == 'true') {
$order_q = "select ot.text as order_total from orders as o, orders_total as ot where o.orders_id=ot.orders_id and o.orders_id = '" . $lt_row['order_id'] . "' and ot.class='ot_total'";
$order_result = xtc_db_query($order_q);
if (xtc_db_num_rows($order_result)>0) {
$order_row = xtc_db_fetch_array($order_result);
echo '<tr><td class="dataTableContent">' . TABLE_TEXT_ORDER_VALUE . $order_row['order_total'] . '</td></tr>';
}
}
$categories_viewed = unserialize($lt_row['categories_viewed']);
if (!empty($categories_viewed)) {
$cat_string = '';
foreach ($categories_viewed as $cat_id=>$val) {
$cat_query = "select * from categories as c, categories_description as cd where c.categories_id=cd.categories_id and c.categories_id='" . $cat_id . "'";
$cat_result = xtc_db_query($cat_query);
$cat_row = xtc_db_fetch_array($cat_result);
$cat_string .= $cat_row['categories_name'] . ',';
}
$cat_string = rtrim($cat_string, ',');
echo '<tr><td class="dataTableContent"><strong>' . TABLE_TEXT_CATEGORIES . '</strong>' . $cat_string . '</td></tr>';
}
if ($products_viewed != '') {
echo '<tr><td class="dataTableContent"><strong>' . TABLE_TEXT_PRODUCTS . ' </strong><table cellspacing=0 cellpadding=0 border=0><tr>';
foreach ($prod_view_array as $key=>$product_id) {
$product_id = rtrim($product_id, '?');
if ($product_id != '') {
$prod_query = "select * from products as p, products_description as pd where p.products_id=pd.products_id and p.products_id='" . $product_id . "'";
$prod_result = xtc_db_query($prod_query);
$prod_row = xtc_db_fetch_array($prod_result);
echo '<td><table cellspacing=0 cellpadding=2 border=0 align="center" style="border:1px solid #000;"><tr><td align="center">' . xtc_image(DIR_WS_CATALOG_IMAGES . $prod_row['products_image'], $prod_row['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</td></tr><tr><td class="dataTableContent" align="center">' . $prod_row['products_name'] . '</td></tr></table></td>';
}
}
echo '</tr></table></td></tr>';
}
$cart_contents = unserialize($lt_row['cart_contents']);
if (!empty($cart_contents)) {
echo '<tr><td class="dataTableContent"><strong>' . TABLE_TEXT_CUSTOMERS_CART . '(value=' . $currency->format($lt_row['cart_total']) . ') : </strong><table cellspacing=0 cellpadding=0 border=0><tr>';
foreach ($cart_contents as $product_id => $qty_array) {
$prod_query = "select * from products as p, products_description as pd where p.products_id=pd.products_id and p.products_id='" . $product_id . "'";
$prod_result = xtc_db_query($prod_query);
$prod_row = xtc_db_fetch_array($prod_result);
echo '<td><table cellspacing=0 cellpadding=2 border=0 align="center" style="border:1px solid #000;"><tr><td align="center">' . xtc_image(DIR_WS_CATALOG_IMAGES . $prod_row['products_image'], $prod_row['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</td></tr><tr><td class="dataTableContent" align="center">' . $prod_row['products_name'] . '</td></tr><tr><td class="dataTableContent">' . TABLE_TEXT_QUANTITY . $qty_array['qty'] . '</td></tr></table></td>';
}
echo '</tr></table></td></tr>';
}
echo '</table>';
}//End While
?>
<strong><a href="supertracker.php?special=last_ten&offset=<?php echo $offset + 10;?>&filter=<?php echo $filter;?>&refer_match=<?php echo $refer_match;?>&landing_match=<?php echo $landing_match;?>"><?php echo TABLE_TEXT_NEXT_TEN_RESULTS; ?></a></strong>
<?php
}//End Special "Last Ten" Report
if ($HTTP_GET_VARS['special'] == 'ppc_summary') {
echo '<table width="100%" border=0 cellspacing=0 cellpadding=5 style="border:1px solid #000;">';
foreach ($ppc as $ref_code => $details) {
$scheme_name = $details['title'];
$keywords = $details['keywords'];
$ppc_q = "SELECT * from supertracker where landing_page like '%ref=" . $ref_code . "%'";
$ppc_result = xtc_db_query ($ppc_q);
$ppc_num_refs = xtc_db_num_rows($ppc_result);
echo '<tr><td style="font-weight:bold;text-decoration:underline;">' . $scheme_name . ' - Total Referrals ' . $ppc_num_refs . '</td></tr>';
if ($keywords != '') {
$keyword_array = explode(',',$keywords);
foreach ($keyword_array as $key => $val) {
$colon_pos = strpos ($val, ':');
$keyword_code = substr($val,0,$colon_pos);
$keyword_friendly_name = substr($val,$colon_pos+1,strlen($val)-$colon_pos);
$ppc_key_q = "SELECT *, count(*) as count, AVG(num_clicks) as ave_clicks, AVG(UNIX_TIMESTAMP(last_click) - UNIX_TIMESTAMP(time_arrived))/60 as ave_time from supertracker where landing_page like '%ref=" . $ref_code . "&keyw=" . $keyword_code . "%' group by landing_page";
$ppc_key_result = xtc_db_query($ppc_key_q);
$ppc_row = xtc_db_fetch_array($ppc_key_result);
$ppc_key_refs = $ppc_row['count'];
echo '<tr><td>' . $keyword_friendly_name . ' : ' . $ppc_key_refs . TABLE_TEXT_REFERRALS_AVERAGE_TIME . $ppc_row['ave_time'] . TABLE_TEXT_MINS_AVERAGE_CLICKS . $ppc_row['ave_clicks'] . '</td></tr>';
}
}
}
echo '</table>';
}//End PPC Summary Report
if ($HTTP_GET_VARS['special'] == 'geo') {
?>
<tr>
<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
<?php
echo '<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent">' . TABLE_TEXT_COUNTRY . '</td></tr>';
$geo_query = "select count(*) as count, country_code, country_name from supertracker GROUP by country_code";
$geo_result = xtc_db_query($geo_query);
$geo_hits = array();
$country_names = array();
$total_hits = 0;
while ($geo_row = xtc_db_fetch_array($geo_result)) {
$total_hits += $geo_row['count'];
$country_code = strtolower($geo_row['country_code']);
$geo_hits[$country_code] = $geo_row['count'];
$country_names[$country_code] = $geo_row['country_name'];
}
draw_geo_graph($geo_hits,$country_names,$total_hits);
}//End Geo Report
if ($HTTP_GET_VARS['special'] == 'prod_coverage') {
if (isset($HTTP_GET_VARS['agent_match'])) {
$agent_match = $HTTP_GET_VARS['agent_match'];
$match_agent_string = " and browser_string like '%" . $agent_match . "%'";
}
else {
$match_agent_string = '';
$agent_match = '';
}
?>
<table width="100%" border=0 cellspacing=0 cellpadding=0>
<tr>
<td class="dataTableContent">
<form name="filter_select" action="supertracker.php" method="get" onchange="this.submit()">
<input type="hidden" name="special" value="prod_coverage">
<?php echo TEXT_STRING_FILTER; // modified by azer ?>
<input type="text" size="15" name="agent_match" value="<?php echo $agent_match;?>">
<input type="submit" value = "Update">
</form>
</td>
</tr>
</table>
<?php
$prod_q = "select p.products_id, pd.products_name from products as p, products_description as pd where p.products_id=pd.products_id and p.products_status='1'";
$prod_result = xtc_db_query($prod_q);
$prod_coverage = array();
while ($prod_row = xtc_db_fetch_array($prod_result)) {
$cov_q = "select * from supertracker where products_viewed like '%" . $prod_row['products_id'] . "%'" . $match_agent_string ;
$cov_result = xtc_db_query($cov_q);
$prod_coverage[$prod_row['products_name']] = xtc_db_num_rows($cov_result);
} // End While loop
arsort($prod_coverage);
?>
<table cellpadding=2 cellspacing=0 border=0 width="100%">
<tr><td class="pageHeading" colspan=2 align="left"><?php echo TABLE_TEXT_PRODUCT_COVERAGE_REPORT; ?></td></tr>
<tr class="dataTableHeadingRow"><td class="dataTableHeadingContent"><?php echo TABLE_TEXT_PRODUCT_NAME; ?></td><td class="dataTableHeadingContent"><?php echo TABLE_TEXT_NUMBER_OF_VIEWING; ?></td></tr>
<?php
foreach ($prod_coverage as $prod_name => $hits) {
echo '<tr><td class="dataTableContent">' . $prod_name . '</td><td class="dataTableContent">' . $hits . '</td></tr>';
}
?>
</table>
<?php
} // End Product Coverage Report
}
?>
</td>
<!-- body_text_eof //-->
</tr>
</table>
<!-- body_eof //-->
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
********************************************
END
Thanks very much for any help!!!!!!!!
PHP and Mysql aren't talking to each other (1 reply)
I have a few web-apps built in php on a server in our office. They have been running fine for some time now. All of a sudden, I can't access mysql via php. When I try and login to phpmyadmin I get the following message:
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
When I go to the login page of my web-app I get this error:
Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /Library/WebServer/Documents/themelon/includes/functions.php on line 3
Unable to connect to the database server at this time.
the code that generates the error message is (password changed):
$dbcnx = mysql_connect("localhost", "root", "xxxx");
if (!$dbcnx) {
print "<p>Unable to connect to the database server at this time.</p>";
exit();
}
I can access mysql using the command line on the server, so I know that mysql is running fine and all my data appears to be intact. PHP is also running fine, it just doesn't see mysql. phpinfo lists the mysql socket as /tmp/mysql.sock. When I run msqladmin to determine version etc it lists the following info:
Server version 5.0.45-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/mysql/mysql.sock
My PHP info is:
PHP Version 5.2.5
www.entropy.ch Release 6
Universal Binary i386/x86_64/ppc7400/ppc64 - this machine runs: x86_64
I am running this on an xServe running 10.5.3. In all honestly, I don't have much background with servers etc, and I could really use some help. Thanks!
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
When I go to the login page of my web-app I get this error:
Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /Library/WebServer/Documents/themelon/includes/functions.php on line 3
Unable to connect to the database server at this time.
the code that generates the error message is (password changed):
$dbcnx = mysql_connect("localhost", "root", "xxxx");
if (!$dbcnx) {
print "<p>Unable to connect to the database server at this time.</p>";
exit();
}
I can access mysql using the command line on the server, so I know that mysql is running fine and all my data appears to be intact. PHP is also running fine, it just doesn't see mysql. phpinfo lists the mysql socket as /tmp/mysql.sock. When I run msqladmin to determine version etc it lists the following info:
Server version 5.0.45-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/mysql/mysql.sock
My PHP info is:
PHP Version 5.2.5
www.entropy.ch Release 6
Universal Binary i386/x86_64/ppc7400/ppc64 - this machine runs: x86_64
I am running this on an xServe running 10.5.3. In all honestly, I don't have much background with servers etc, and I could really use some help. Thanks!
php loop into mysql query (2 replies)
i'd like to turn the following php loop into a mysql query. can someone help me please?
<?php
$all = mysql_query("SELECT product_number, order_number, tracking_number FROM dhl92") or die(mysql_error());
while ($row = mysql_fetch_array($all)) {
$matches = myql_query("SELECT id FROM dhl92 WHERE product_number = '" . $row['product_number'] . "' AND order_number = '" . $row['order_number'] . "' AND tracking_number = '" . $row['tracking_number'] . "'") or die(mysql_error());
$count = mysql_num_rows($matches);
for ($i = 1; $i < $count; $i++) {
$result = mysql_result($matches, $i);
$remove = mysql_query("DELETE FROM dhl92 WHERE id = '" . $result . "'") or die(mysql_error());
}
}
?>
this will remove all but one row that have the same product_number, order_number, and tracking_number. any help is much appreciated. thanks!
<?php
$all = mysql_query("SELECT product_number, order_number, tracking_number FROM dhl92") or die(mysql_error());
while ($row = mysql_fetch_array($all)) {
$matches = myql_query("SELECT id FROM dhl92 WHERE product_number = '" . $row['product_number'] . "' AND order_number = '" . $row['order_number'] . "' AND tracking_number = '" . $row['tracking_number'] . "'") or die(mysql_error());
$count = mysql_num_rows($matches);
for ($i = 1; $i < $count; $i++) {
$result = mysql_result($matches, $i);
$remove = mysql_query("DELETE FROM dhl92 WHERE id = '" . $result . "'") or die(mysql_error());
}
}
?>
this will remove all but one row that have the same product_number, order_number, and tracking_number. any help is much appreciated. thanks!
Delete specific row from a query (1 reply)
Is there a way to delete a specific row from a query? I have a page that retrieves all the information from an user specified database and table so my query is "SELECT * FROM {$dbtable}" and I have checkboxes for each row. I am wondering if there is some variation on "DELETE FROM {$dbtable} ROW '{$row}' OF (SELECT * FROM {$dbtable})" that is syntactically right.
Search All Syntax (1 reply)
I have a search page that searches in the location field in the database using a drop down menu with options values for example as <option value="England">England</option>.
What I want to do is have an option for select all but I am not sure what value to put for this. I know when using ASP I can use % as the value but am not sure with PHP.
I would appreciate any advise on this.
What I want to do is have an option for select all but I am not sure what value to put for this. I know when using ASP I can use % as the value but am not sure with PHP.
I would appreciate any advise on this.
Cannot Load Mysql extension please check PHP configuration (2 replies)
Hi
I installed in my computer these softwares:
1) win xp
2) apache 1.3.28
3)php 5.2.6
4)mysql client 5.0.11
I change like this in php.ini (remove ;)
;extension=php_mysql.dll -------> extension=php_mysql.dll
Also I wrote my extention directory!
but there is no any connection between db and php!
phpmyadmin shoe this error message:
Quote:Cannot load mysql extension. Please check your PHP configuration. - Documentation
Quote:1.20 I receive the error "cannot load MySQL extension, please check PHP Configuration".
To connect to a MySQL server, PHP needs a set of MySQL functions called "MySQL extension". This extension may be part of the PHP distribution (compiled-in), otherwise it needs to be loaded dynamically. Its name is probably mysql.so or php_mysql.dll. phpMyAdmin tried to load the extension but failed.
Usually, the problem is solved by installing a software package called "PHP-MySQL" or something similar.
but in theory all is okay in my computer:
extension_dir = "f:\www\php\ext\"
and
php_mysql.dll is in this directory
and other extension for exam gd2 work properly but only mysql extension not work properly!
in apache i see localhost had be changed to babe.the-killer.bz but i re install apache.
please help me and say me how i can reconnect between php and mysql?
thanks
I installed in my computer these softwares:
1) win xp
2) apache 1.3.28
3)php 5.2.6
4)mysql client 5.0.11
I change like this in php.ini (remove ;)
;extension=php_mysql.dll -------> extension=php_mysql.dll
Also I wrote my extention directory!
but there is no any connection between db and php!
phpmyadmin shoe this error message:
Quote:Cannot load mysql extension. Please check your PHP configuration. - Documentation
Quote:1.20 I receive the error "cannot load MySQL extension, please check PHP Configuration".
To connect to a MySQL server, PHP needs a set of MySQL functions called "MySQL extension". This extension may be part of the PHP distribution (compiled-in), otherwise it needs to be loaded dynamically. Its name is probably mysql.so or php_mysql.dll. phpMyAdmin tried to load the extension but failed.
Usually, the problem is solved by installing a software package called "PHP-MySQL" or something similar.
but in theory all is okay in my computer:
extension_dir = "f:\www\php\ext\"
and
php_mysql.dll is in this directory
and other extension for exam gd2 work properly but only mysql extension not work properly!
in apache i see localhost had be changed to babe.the-killer.bz but i re install apache.
please help me and say me how i can reconnect between php and mysql?
thanks
Help: MySQL field as a url (1 reply)
Hello, My name is Vince, President and CEO of EternalWolf,LLC. at eternalwolf.com.
I am one of few people who can program. Most of my employees are Market Researchers and dont know much about php or mysql.
I am trying to find a clean, userfriendly way to display all rows in the field, 'business_name', whether it be from a search or simply displayed on the webpage. The only problem is that I need the retrieved data to link to another page that will display all fields in the row (according to the 'business_name' field) to do something similar to what a form POST would do. There are a total of 120 rows so far.
I'll keep looking around to see what i can find.
but if anyone has an idea and can lend me a hand, i will be very grateful. and will have your name, or "codename" on the page itself.
Regards,
Vince Marra
EternalWolf,LLC
I am one of few people who can program. Most of my employees are Market Researchers and dont know much about php or mysql.
I am trying to find a clean, userfriendly way to display all rows in the field, 'business_name', whether it be from a search or simply displayed on the webpage. The only problem is that I need the retrieved data to link to another page that will display all fields in the row (according to the 'business_name' field) to do something similar to what a form POST would do. There are a total of 120 rows so far.
I'll keep looking around to see what i can find.
but if anyone has an idea and can lend me a hand, i will be very grateful. and will have your name, or "codename" on the page itself.
Regards,
Vince Marra
EternalWolf,LLC