Forum Moderators: DixonJones
<?php
[red]
$dbh=mysql_connect ("localhost", "freefr4_user", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("freefr4_tracking");
[red]
if(empty($cookieid)) {
[red]
if (getenv("HTTP_CLIENT_IP")) $ip = getenv("HTTP_CLIENT_IP");
else if(getenv("HTTP_X_FORWARDED_FOR")) $ip = getenv("HTTP_X_FORWARDED_FOR");
else if(getenv("REMOTE_ADDR")) $ip = getenv("REMOTE_ADDR");
else $ip = "UNKNOWN";
$day = (int) date("mdY", mktime());
$in = mktime($h[0],$h[1],$h[2],1,1,1980);
setcookie("CookieID", mysql_insert_id(), time()+2592000, "/");
}
$out = mktime($h[0],$h[1],$h[2],1,1,1980);
$trail = $trail . "_" . $page;
$total = $out-$in;
[red]
$sql = "INSERT INTO pagetrack (id, ip_address, referral_id, keyword, day, in, out, total, trail) VALUES ('$id', '$ip', '$ref', '$OVRAW', '$day', '$in', '$out', '$total', '$trail')";
[red]
mysql_close( $dbh );
[red]
?>
i've included it at the very top of my "index.html" (which effectively includes it to every page of my site as it's all dynamic)
the cookie is being created on my computer but the id is not incrementing and no information is being stored in my database.
my database is "freefr4_tracking" and the table i'm trying to save information into is "pagetrack"
BTW: the script above is what i've been able to teach myself of php/mysql in the past 2 days so I'm a little sketchy on trying to debug it myself... it took a while to get it to run without displaying error messages...
basic idea of the script:
every visitor to the website is assigned a unique id number (a 6 digit integer auto-incremented in the database) which is stored in a cookie which lasts for 30 days (i might be using this cookie to track return visitors in the future...)
I get the time ("in") and date ("day") that they enter the site and store it to the table (seperate values for time and date)
every time they load a page it gets the time again ("out") and calculates the difference ("total") to tell me how long they've been on the site (I know, I know, I'll write a javascript thingy to more accurately track these things once the database is working...)
$page is a variable contained within the pages this script is included in (which is why it's not defined in the script as it doesn't need to be), I use this variable ("$path") to track the user's path through the site (once again sloppy I know but it's just something to start with)
$ip is of course the visitor's ip address
$ref is the referring site (i.e. google or overture)
$OVRAW is the keyword the visitor found us under on the search engines
"id" is set to PRIMARY and auto-increment in my database as I think it's supposed to be...
Is there anybody out there who understands all this stuff and can tell me what I'm doing wrong?
PLEASE! :-) :-) :-)
Thank You,
-Jason Lynch
BTW: this is what my cookie looks like:
CookieID
0
wwWebmasterWorldebsitenamegoeshere.com/
1536
2436152192
29631273
2300353552
29625238
*
Either set the value of $id or don't assign a value to the 'id' field.
Are you actually running the query anywhere?!?
volatilegx is right about the $id - you don't need to insert a value for it (or even mention it in the query) as the database does that for you.
Are you actually running the query anywhere?!?
ok, I removed the "id" and "$id" from the INSERT INTO command:
$sql = "INSERT INTO pagetrack (ip_address, referral_id, keyword, day, in, out, total, trail) VALUES ('$ip', '$ref', '$OVRAW', '$day', '$in', '$out', '$total', '$trail')";
but still nothing...
id isn't getting incremented (my cookie always has a value of zero, even after deleting it and revisiting the site)
of course i wouldn't expect it to get incremented as nothing is being stored in the database... yeah.. no dice...
and like I said, I'm new to this so I'm a little lost when you ask:
Are you actually running the query anywhere?!?
I can tell you that the code in my first post is my entire tracking code so I don't know if I'm missing something or what... I did set up the database using phpMyAdmin (which is provided by my host Lunarpages) with all the corresponding fields and I'm using the exact names when trying to store the values...
I'm under the impression that the $sql = "INSERT INTO.... is the part that actually writes to the database but maybe I'm wrong...
Please HELP!
Thank You.
-JL
Your assumption is off. The "INSERT INTO" statement is only a string at the point where you left off... you need to actually perform the query, something like this:
$result = mysql_query($sql);
if ($result){
# it worked. do something
} else {
# query was malformed. it didn't work. display the error or something.
}
setcookie("CookieID", mysql_insert_id(), time()+2592000, "/");
Should happen after the query is performed, otherwise the mysql_insert_id() will always return a zero. mysql_insert_id() returns the auto-incremented id from the previous INSERT query. In your code, since there was no previous INSERT operation, it returns a '0'.
See [us4.php.net...]
Invalid query: 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 'in, out, total, trail) VALUES ('****.****.107.144', '', '', '3172
current code:
<?php
[red]
$dbh=mysql_connect ("localhost", "freefr4_gilahkr", "nxtphase") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("freefr4_tracking");
[red]
if(empty($cookieid)) {
[red]
if (getenv("HTTP_CLIENT_IP")) $ip = getenv("HTTP_CLIENT_IP");
else if(getenv("HTTP_X_FORWARDED_FOR")) $ip = getenv("HTTP_X_FORWARDED_FOR");
else if(getenv("REMOTE_ADDR")) $ip = getenv("REMOTE_ADDR");
else $ip = "UNKNOWN";
$day = (int) date("mdY", mktime());
$in = mktime($h[0],$h[1],$h[2],1,1,1980);
}
$out = mktime($h[0],$h[1],$h[2],1,1,1980);
$trail = $trail . "_" . $page;
$total = $out-$in;
[red]
$sql = "INSERT INTO pagetrack (ip_address, referral_id, keyword, day, in, out, total, trail) VALUES ('$ip', '$ref', '$OVRAW', '$day', '$in', '$out', '$total', '$trail')";
$result = mysql_query($sql)
or die("Invalid query: " . mysql_error());
$EventID = mysql_insert_id();
printf("number:",$EventID);
setcookie("CookieID", mysql_insert_id(), time()+2592000, "/");
mysql_close( $dbh );
?>
I have some other things to work on right now but I'll work on this later and post my results.
Thanks again for all your help.
-JL
my table: (as best as I can do for a table on this messageboard...)
Type_________Attributes___Null__Default__Extra
id__________int(6)_______No____________auto_increment
ip_address___varchar(20)__No
referral_id___varchar(20)__No
keyword_____varchar(20)__No
day_________varchar(10)__No
in__________int(10)______No____0
out_________int(10)______No____0
total________int(10)______No____0
trail_________varchar(100)_No
Here's an example:
$sql = "INSERT INTO pagetrack (ip_address, referral_id, keyword, day, in, out, total, trail) VALUES ('".addslashes($ip)."', '".addslashes($ref)."', '".addslashes($OVRAW)."', '".addslashes($day)."', '".addslashes($in)."', '".addslashes($out)."', '".addslashes($total)."', '".addslashes($trail)."')";
Also, I noticed while reviewing your code again that you never actually access the value in your cookie. You check to see it exists, and assign values if it doesn't, but if it does exist, you don't read it. Also, the name of the cookie you are checking doesn't match the name of the cookie you write (different capitalization).
Dan
(edited to correct spelling)
There are many threads on that topic in the Tracking and logging forum. Let's keep this particular thread on-topic and related to gilahacker's question :)
