inserting results of query into table

[ucbones]

Hey,

I'm using MySQL for a simple hitcounter, that logs dates, times, pages of entry etc. However, it creates a new row for each new entry, and this is getting a little large, with roughly 2000 rows in 2 weeks, so I was thinking of doing something like this...

SELECT page, COUNT(*) AS hits FROM hitcount GROUP BY page

This command works fine, to show me what's going on, but how can I place the output of this command into a new table?

Many thanks,

ucbones :o

[Lars_Berg]

insert into newtable(page,hits)
SELECT page, COUNT(*) AS hits FROM hitcount GROUP BY page 

[phplearner]

This is the closest post I could find to something I'm trying to do, but with a variation.

Here is my proposed DB SQL:

-- t1_refsource table

drop table if exists t1_refsource;
CREATE TABLE t1_refsource (
 ID int(11) NOT NULL auto_increment primary key,
 refname varchar(50) NOT NULL default '',
 refip varchar(15) NOT NULL default '',
 refdate DATE NOT NULL default ''

);


-- t1_pagehits table

drop table if exists t1_pagehits;
CREATE TABLE t1_pagehits (
 ID int(11) NOT NULL auto_increment primary key,
 page1 INT(11) NOT NULL default '0',
 page2 INT(11) NOT NULL default '0',
 page3 INT(11) NOT NULL default '0',
 page4 INT(11) NOT NULL default '0',
 page5 INT(11) NOT NULL default '0',
 page6 INT(11) NOT NULL default '0',
 page7 INT(11) NOT NULL default '0'

);

In my case, I'm trying to figure out how to:

1. Store the 'Referring URL' and DATE to ONLY the entry 'Page1' of a website. If from 'Google', etc., it would be helpful to know which search word(s) were used (if that's even possible). After periodic review, I'll manually delete the rows and start over. Then...

2. For ALL pages, ONLY update the individual page# hit count/tally by 1 without-adding-additional-rows. In other words, if Page3 already has 392 hits, the next one will update the column/row to read 393. This will keep a very small table size with only the few rows.

From what I can guesstimate, several of all of the following must be used in some combination but I just dunno how to pull it off:

$HTTP_SERVER_VARS['REMOTE_ADDR'];
gethostbyname
mysql_row_seek()
MYSQL_ROW_OFFSET 
mysql_store_result()

Umh, on each page would I just assign something like $page3 = 3; to try the 'hit' by page number?

I'm thinking I could do something like:

SQL = "UPDATE t1_pagehits SET page1=page1+1 WHERE ID=$id;

...but now I'm wondering if some kind of "LOCK TABLES" is also needed?

Thanks for any help with this.