[Resolved] store number of times a page is wiewed

Patioman

Hello all....
Been a while since my last post. Good to know I can always come
back for answers. Our company sends customers a link to quotes
for buildings via email. This link returns them to our website
to view the quote so it can retreive info from our database.
What I am wanting to due is to be able to keep track of how many
times they view this page and the last date viewed.
I have 2 columns defined in my mysql table.

VIEWCOUNT (TEXT)
VIEWDATE (DATE)

I want the VIEWCOUNT value to increment by 1 each time the page is
viewed and the VIEWDATE column to contain the current date. This is
the script as I have it so far. I received no errors and the script
does nothing.

<?php
$dbcnx=mysql_connect("$server","$user","$password");
mysql_select_db("cannon");
$custinfo2=mysql_query("SELECT * FROM CUSTINFO WHERE ID=$id");
if (!$custinfo2) {
echo("Error retrieving info from database! ".
"Error: " . mysql_error() . "");
}

while ($custinfo = mysql_fetch_array($custinfo2)){
$id=$custinfo["ID"];
$viewcount=$custinfo["VIEWCOUNT"];
}

if($viewcount == NULL) {
$viewcount="1";
} else {
$viewcount=($viewcount + 1);
}

$sql="UPDATE CUSTINFO SET ".
"VIEWCOUNT=[$viewcount],".
"VIEWDATE=CURDATE() ".
"WHERE ID=[$id];";
mysql_close();
?>

Thanks a bunch......

paulnaj

It's probably not important, but you have enclosed strings with []
rather than single quotes in the sql ...

sql="UPDATE CUSTINFO SET ". 
"VIEWCOUNT=[$viewcount],". 
"VIEWDATE=CURDATE() ". 
"WHERE ID=[$id];";

macmatt

I did something similar with a site selling movie posters, that updated the viewcount of a poster in order to creat a 'Top ten of popular posters'. I don't have the code to hand, not being at my own computer, but I seem to rememebr that I just updated the database with something like viewcount = viewcount +1

Also, my viewcount columntype was INT with a default of 0, your appears to be TEXt. If you made it INT you shouldn't need this bit of your code:

if($viewcount == NULL) {
$viewcount="1";
} else {
$viewcount=($viewcount + 1);
}

As viewcount (the column) would always be a integer that could be increased by 1.

You say the page does nothing, does it not even update the viewdate?

I've not been doing this very long, so someone may come in with a better answer, but the method I've described certainly worked for me.

Patioman

I have modified the script as shown below. I have put in place
echo prompts to display values. Everything works fine except
my database update. I have set the VIEWCOUNT column to INT
w/ a default of 0. The script reads in the current values from DB
OK but does not update.

while ($custinfo = mysql_fetch_array($custinfo2)){
$id=$custinfo["ID"];
$viewcount=$custinfo["VIEWCOUNT"];
}
mysql_close();

echo($viewcount);
echo" ";

$viewcount=($viewcount + 1);
echo($viewcount);
echo" ";

$servertime = time();
$second = date("s", ($servertime));
$minute = date("i", ($servertime));
$hour = date("G", ($servertime));
$day = date("j", ($servertime));
$month = date("n", ($servertime));
$year = date("y", ($servertime))+2000;
$viewdate = $year.'-'.$month.'-'.$day.' '.$hour.':'.$minute.':'.$second ;

$dbcnx=mysql_connect("$server","$user","$password");
mysql_select_db("cannon");
$sql="UPDATE CUSTINFO SET ".
"VIEWCOUNT='$viewcount',".
"VIEWDATE='$viewdate' ".
"WHERE ID='$id';";
mysql_close();

echo($viewcount);
echo" ";
echo($viewdate);
echo" ";
echo($id);
?>

macmatt

Here's this SQL query I used for my update:

"UPDATE posters SET viewcount = viewcount +1 WHERE ID
= %s"

%s is the ID of the poster from the URL. Try using this somewhere near the top of your code:

"UPDATE CUSTINFO SET VIEWCOUNT = VIEWCOUNT +1 , VIEWDATE = CURDATE() WHERE ID = $id"

Patioman

Thanks for trying to help. I have un-install MySql & re-installled.
That didn't solve the problem. Here is the coding that I have tried
last. It simply doesn't update the DB. I can update the DB manually from the DOS prompt with no problems.


$dbcnx=mysql_connect("$server","$user","$password");
mysql_select_db("cannon");
$sql="UPDATE CUSTINFO SET VIEWCOUNT = VIEWCOUNT +1 , VIEWDATE = CURDATE() WHERE ID = $id;";
mysql_close();

stolzyboy

spose not, you didn't run the query with mysql_query()

Patioman

No Errors, no anything....I ran into this once before but I forget
what the problem was. I'm down to this script.


$dbcnx=mysql_connect("$server","$user","$password");
mysql_select_db("cannon");
$custinfo2=mysql_query("SELECT * FROM CUSTINFO WHERE ID=$id"); 
if (!$custinfo2) { 
echo("<P>Error retrieving info from database!<BR>". 
"Error: " . mysql_error() . "</P>"); 
} 

while ($custinfo = mysql_fetch_array($custinfo2)){ 
$id=$custinfo["ID"]; 
$viewcount=$custinfo["VIEWCOUNT"]; 
} 

$sql="UPDATE CUSTINFO SET VIEWCOUNT = VIEWCOUNT +1 , VIEWDATE = CURDATE() WHERE ID = $id;";
mysql_close();

stolzyboy

did you even read my post, you AREN'T running the update query with mysql_query();

Patioman

Sorry for being thick-headed today. I must of taken a stupid pill this morning. This is the script I am using and it works.
Thanks a bunch for your help!!!

    $servertime = time();
    $second = date("s", ($servertime));
    $minute = date("i", ($servertime));
    $hour = date("G", ($servertime));
    $day = date("j", ($servertime));
    $month = date("n", ($servertime));
    $year = date("y", ($servertime))+2000;
    $viewdate = $year.'-'.$month.'-'.$day.' '.$hour.':'.$minute.':'.$second ;
$dbcnx=mysql_connect("$server","$user","$password");
mysql_select_db("cannon");
$custinfo2=mysql_query("SELECT * FROM CUSTINFO WHERE ID=$id"); 
if (!$custinfo2) { 
echo("<P>Error retrieving info from database!<BR>". 
"Error: " . mysql_error() . "</P>"); 
} 

while ($custinfo = mysql_fetch_array($custinfo2)){ 
$id=$custinfo["ID"]; 
$viewcount=$custinfo["VIEWCOUNT"]; 
} 

$sql=mysql_query("UPDATE CUSTINFO SET VIEWCOUNT = VIEWCOUNT +1 , VIEWDATE = '$viewdate' WHERE ID = $id");
mysql_close();

stolzyboy

np, sometimes it takes a set of new :rolleyes: to make sense of things