Page 1 of 2

Simple Stat Tracker

Posted: Mon Apr 17, 2006 11:35 pm
by saltriver
Hi All,

Long time no see. I've run into a bit of a wall. I'm trying to track stats for a pop up/detail page based on the "name" variable passed from the master page. This Master/Detail combo is used throughout my site and my tracking package only measures the number of times the file was opened, ignoring the contents that appeared inside of it. I've had some success with inputing some of the information I need into my db with the following code:

Code: Select all

$recordID = $_GET['recordID'];
$domain = GetHostByName($REMOTE_ADDR);
$db = mysql_select_db($database_my_stats, $my_stats) or die (mysql_error());
$sql = "insert into popup values ('', '$recordID', now(), '$domain')"; 
mysql_query($sql, $my_stats);
This is using a connection script and a db with the fields 'id', 'name', 'time' & 'ipaddress'.

This does give me an interesting quirk that I think may be caused by my router. I (and a friend of mine) am getting two entries in the db every time (after the first one or two hits) we open the pop up page.

I'd really rather not put an entry into the db every time the page is called. I saw some tutorials on the web that said to do just that, but I can only imagine the bloated db that would make.

What I was trying to figure out, before settling on this solution was this. I'll write it in half code/half english...

$recordID = the NAME
IF
record exists for NAME in STATS db
do this
UPDATE HITS where NAME=NAME by adding 1(++1)
ELSE
insert into STATS values (' ', '$recordID', '++1')

This is using a db with the fields 'id', 'name', 'hits'.

Is this the right way to go about getting some very simple stats?
I plan on using a similar solution when measuring our random ad impressions.

Steve

Posted: Tue Apr 18, 2006 3:24 am
by Ollie Saunders
Wow i've been doing exactly the same thing in a system of mine
$recordID = the NAME
IF
record exists for NAME in STATS db
do this
UPDATE HITS where NAME=NAME by adding 1(++1)
ELSE
insert into STATS values (' ', '$recordID', '++1')
yeah thats pretty much right

SELECT record FROM hits WHERE hitid = $recordID
if(mysql_num_rows()) {
INSERT INTO hits....
} else {
UPDATE hits SET counter = counter + 1 WHERE hitid = $recordID
}

I'm close

Posted: Tue Apr 18, 2006 10:11 pm
by saltriver
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


When I try this:

Code: Select all

$recordID = $_GET['recordID'];
mysql_select_db($database_healthy_mystats, $healthy_mystats);
$pophit = mysql_query("SELECT rest FROM popup WHERE rest = $recordID", $healthy_mystats);
if(mysql_num_rows($pophit)) {        ------------------------------------- //line 38
"INSERT INTO popup values ('', '$recordID', now(), '1')";
} else {
"UPDATE hits SET counter = counter + 1 WHERE rest = $recordID";
}
I get this error:

Code: Select all

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /_details_n.php on line 38

When I try taking out the $pophit, like this:

Code: Select all

$recordID = $_GET['recordID'];
mysql_select_db($database_healthy_mystats, $healthy_mystats);
$pophit = mysql_query("SELECT rest FROM popup WHERE rest = $recordID", $healthy_mystats);
if (mysql_num_rows()) {             ---------------------------------------- //line 38
"INSERT INTO popup values ('', '$recordID', now(), '1')";
} else {
"UPDATE hits SET counter = counter + 1 WHERE rest = $recordID";
}
I get this error:

Code: Select all

Warning: Wrong parameter count for mysql_num_rows() in /_details_n.php on line 38
As far as I can tell, my syntax is good, am inserting the wrong thing into that argument?

Steve


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Apr 18, 2006 10:15 pm
by feyd
see what mysql_error() has to say.

Posted: Tue Apr 18, 2006 11:36 pm
by saltriver
If you meant placing mysql_error(); on the line below the given code, all it did was tell me the same thing.

I did try

Code: Select all

if (!$result) die("Query Failed.");
which told me query failed. I did notice I forgot the single quotes in the SQL statement, but that didn't seem to work either. So I'm guessing there's something wrong with my query.

Code: Select all

$pophit = mysql_query("SELECT name FROM popup WHERE name = '$recordID'", $healthy_mystats);
With $healthy_mystats being a valid connect variable.[/quote]

Posted: Tue Apr 18, 2006 11:40 pm
by feyd
I hope you realize that mysql_error() returns a string. It doesn't echo out the error itself.

Posted: Wed Apr 19, 2006 12:04 am
by saltriver
Sorry to dash your hopes, but I didn't know that.

But, duh ya, of course that makes sense.

However, it doesn't seem to return anything when echoed. Does that mean the error is in my PHP and not my SQL?

Posted: Wed Apr 19, 2006 12:29 am
by feyd
It would depend a bit on where you call it in your code. I'd suggest calling it if $result isn't a resource..

Posted: Wed Apr 19, 2006 3:09 am
by Ollie Saunders
OK here's how I would write it see if this improves anything, you need to define(LOCAL_DEVELOPMENT,true):

Code: Select all

// connection / setup code appears before everything else
mysql_select_db($dbName, $dbHandle) or exit('Error selecting database');

// clean and validate input
$mysql = array();
$mysql['recordID'] = (int)$_GET['recordID'];
if($mysql['recordID'] == 0) exit('You must supply a valid ID');

// get into the habbit of putting the query in a variable, this will give you more
// flexibilty with what you can do when the query fails and allows you to perform 
// operations on it
$q = 'SELECT rest FROM popup WHERE rest = '.$mysql['recordID'];

// function to handle failure nicely 
function queryWrap($q,$dbHandle) {
	$result = mysql_query($q,$dbHandle);
	if(!$result) {
		if(LOCAL_DEVELOPMENT) $exitStr = 'Error with query --'.$q;
		else exitStr = 'Error with query, please contact the developer';
		exit($exitStr);
	}
	return $result;
}
$result = queryWrap($q,$dbHandle);
if(mysql_num_rows($result)) {
	$q = 'UPDATE hits SET counter = counter + 1 WHERE rest = '.$mysql['recordID'];	
} else {
	$q = 'INSERT INTO popup values (\'\', '.$mysql['recordID'].', NOW(), 1)';
}
$result = queryWrap($q,$dbHandle);
echo 'w00t, we got through without errors';

Posted: Wed Apr 19, 2006 11:26 pm
by saltriver
<?php
Wow ()

Let me take a day or two to digest. I just wanted to say...
echo $wow;
?>

Posted: Wed Apr 19, 2006 11:29 pm
by saltriver
// get into the habbit of putting the query in a variable, this will give you more
// flexibilty with what you can do when the query fails and allows you to perform
// operations on it



That's the best phrased piece of advice I've ever gotten here.

Posted: Thu Apr 20, 2006 7:01 pm
by saltriver
Ok.

I tried it, but its not working. I noticed a missing $ on the following line:

Code: Select all

else exitStr = 'Error with query, please contact the developer';
I put it in front of exitStr.

After that, it kept telling me "You must supply a valid ID", even though I know it was being passed. So just to test it, I concacted the variable onto the end of the error message so I could see it get passed, like this:

Code: Select all

if($mysql['recordID'] == 0) exit('You must supply a valid ID'.$recordID);
and it returned "You must supply a valid IDPans South Hero Pizza"

This line isn't looking into the database. It's just asking for the recordID, right?

Further down the code is where it checks the db to see if any record for the 'rest' ($recordID) exists. If it doesn't, it creates one, if it does it just adds to the number of 'hits'.

Posted: Thu Apr 20, 2006 7:27 pm
by Ollie Saunders
why is your record id equal to Pans South Hero Pizza? It should be a number.

Posted: Thu Apr 20, 2006 7:53 pm
by saltriver
recordID is the restaurant name from the previous page. The page I need the counter on is the detail page. The restaurant/recordID is passed to the detail page and the corresponding info from the database is displayed.

Here's what the url looks like:

......../vt_details_n.php?recordID=Pans ... ro%20Pizza

Posted: Thu Apr 20, 2006 11:17 pm
by saltriver
Half way there. I've got it so it will update an existing record, but it still won't insert a new record.

Code: Select all

$recordID = $_GET['recordID'];
$db = mysql_select_db($db_name, $db_handle) or die (mysql_error());
$rc = "select * from popup where rest = '$recordID'";
$restchk = mysql_query($rc) or die (mysql_error());
$num_rows = mysql_num_rows($restchk);
echo "$num_rows Rows\n";
if ($num_rows = 0) {
$sql = "insert into popup values ('', '$recordID', now(), '1')"; 
mysql_query($sql);
} else {
$sqlu = "update popup set hits = hits ++1 where rest = '$recordID'";
mysql_query($sqlu);
};
echo $restchk;
echo $rc;
It's echoing 3 things and I'm not getting any errors.
For a restaurant that's not in the db it echos:
0 Rows Resource id #10select * from popup where rest = 'Restaurant Name'

For a restaurant that's in the db it echos:
1 Rows Resource id #10select * from popup where rest = 'Restaurant Name'

I have no idea where Resource#10 is coming from, but I'm not sure it matters.

Any ideas?

Steve