Session Variables to MySQL

[xProteuSx]

I am working on a little login script.  I have it working finally, and am beginning work on starting sessions and registering variables.  I understand how to start a session and set variables, but I have a complex table and need these variables to be imported into MySQL.  Below is a rundown of my table columns:

[code]
users_id int(6) NOT NULL auto_increment,
users_handle varchar(20) default NULL,
users_password varchar (20) default NULL,
users_email varchar (40) default NULL,
users_datejoined timestamp NOT NULL,
users_visits int (6) default '0',
users_lastvisit timestamp NOT NULL,
users_questionsanswered int(6) default '0',
users_correctanswers int(6) default '0',
users_percentcorrect float default '0',
users_totalscore int (6) default '0',
users_pagesviewed int(8) default '0',
users_visitbonus int(6) default '0',
users_activity int(6) default '0',
[/code]

As you can see, there are quite a few variables to be tracked.  But lets work with one for now.  Third from last is 'users_pagesviewed'.  How do I get the database to increment that value by 1 every time the visitor views a page on my website?  I don't even know where to start.

zanus ... I know you're around somewhere ...  ;D

[btherl]

The statement

[code=php:0]UPDATE users SET users_pagesviewed = users_pagesviewed + 1 WHERE users_id = $users_id[/code]


will work, assuming $users_id is set to the id of your user.

[xProteuSx]

I have not had a chance to implement the incrementation of the pages viewed variable because I am stuck on something similar:

[code]
$sqlvisit = "UPDATE `users` SET `users_lastvisit` = NOW( ) WHERE `users`.`users_handle` = '$username' LIMIT 1 ;";
$sql_result = mysql_query($sqlvisit) or die ("<p>Shit didn't work yo!</p>");
[/code]

for some reason these two columns in the db are linked:

[code]
users_datejoined timestamp NOT NULL
users_lastvisit timestamp NOT NULL
[/code]

The database updates, however the columns 'users_lastvisit' and 'users_datejoined' are both updated to the new value of NOW().  Isn't that weird??

[btherl]

Are you sure that that sql statement is the only one being executed?

Limit 1 won't do anything if users_handle is unique (which it should be).

If users_handle doesn't have a unique index on it already, try adding one.  It'll make your lookups faster and will help to detect bugs.

[xProteuSx]

Yeah, the 'users_handle' column is UNIQUE.  Thanks for the tip though.

[btherl]

There's only two possibilities if an update of one column always triggers an update of another column.

1.  The columns ARE linked.  Maybe the table was setup so an update of one column triggers another.  This seems very unusual for those columns though.
2.  There's another sql statement being executed.

My gut feeling is that it's #2, because it doesn't make sense to trigger an update of datejoined when lastvisit is changed.  An initially time-consuming but worthwhile way to check this is to replace mysql_query() with a wrapper function.  Then have the wrapper function print out every query that comes through it.  Eg

[code=php:0]function __mysql_query($sql) {
  print "$sql<br>";
  return mysql_query($sql);
}[/code]


Then use __mysql_query() instead of mysql_query() everywhere.  When you want to switch off the debugging output, just comment out that single print statement.

Later you can use this wrapper to do timing of sql statements and other useful stuff.  You can also put error checking inside the wrapper, to save yourself having to check for errors after every query.