Hello Listies,
Given: MySQL 4.0.12, I need to implement a pageview log with a
resolution of 1 day.
I propose this table:
CREATE TABLE `pageviews` (
`id` int(11) NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`url` char(120) NOT NULL default '',
`views` mediumint(9) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `date` (`date`,`url`),
KEY `url` (`url`)
) TYPE=InnoDB;
So that an update will look like:
> UPDATE pageviews SET views=views+1 WHERE date='<DATE>' AND
url='<ARTIST>'
Of course I need to INSERT the record if one does not match my WHERE.
This would be easy if I had 4.1 -- "INSERT ... ON DUPLICATE KEY UPDATE",
I think -- but I do not.
So, how should I write my queries so that when a new day dawns, I don't
have 2 connections racing to INSERT?
I suspect I could do something like this (in PHP, line numbers added:
01 $link = connect2Db();
02 $sql = "SELECT * FROM pageviews WHERE date='<DATE>' AND
url='<ARTIST>' LOCK IN SHARE MODE";
03 if ( mysql_num_rows(mysql_query($sql,$link)) ) {
04 // UPDATE
05 } else {
06 // INSERT
07 }
Also, should I explicitly "mysql_query('COMMIT',$link)" on line 8?
Thanks all!
--
wellington
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
