query

[LoOpy]

CREATE TABLE IF NOT EXISTS `pt_votes` (
  `id` tinyint(4) NOT NULL auto_increment,
  `user` tinyint(4) NOT NULL default '0',
  `poor` int(10) NOT NULL default '0',
  `fair` int(10) NOT NULL default '0',
  `good` int(10) NOT NULL default '0',
  `very_good` int(10) NOT NULL default '0',
  `excellent` int(10) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

 

INSERT INTO pt_votes
SET user='1', good = '1', date = NOW()
ON DUPLICATE KEY UPDATE good = good+1;

 

basically this is a rating system for users initially was just going to be a constant thing, but now I'm going to divide into months. I currently only have 1 row per user i just want to create a new one every month. was just wondering if i can do this in mySQL with one query with out having to use php to query the database, check for the months etc.

 

Thanks

[fenway]

A few things... tinyint is way too small for a UID, go with INT UNSIGNED; also, date is a reserved keyword, switch it to something like last_voted_on.

 

That being said, I'm not sure how you mean... you can use the PERIOD in mysql to figure out which YYMM you're talking abuot.