creating new columns with specific data

Hi,

I run a website that collects data on an hourly basis throughout the day from many (about 100) different places. This data is stored in a database, but every time we recollect the data the database is updated and the old data is deleted.

What I am trying to do is basically store the value from a certain column of Table1 once per day into another identical row in Table2. The thing is that I want to create a record of data, so for instance, for Row 1 I would like to store the data once each day in a new column. So for instance every week 7 new columns would be created in Table2 in which my data from that week would be stored. After two weeks, there would be 14 columns, etc.

What I have tried to do is first retreive the specific data I need from my first table and then tried using it in my second. I think the code will give you an idea of what I mean

This sounds very confusing, but if someone who understands what I am trying to do has a better way of doing it, I'd be happy to hear. Here is a piece of the code so far, but I can't figure out to create a new column based on my variable $trendDate (which would just be the date):

swellData = "table1"
trendData= "table2"


//test variables
$beach = "Sandy Beach";
$trendDate = date("F j, Y");

//get finalScore from swellData

$query="SELECT * FROM swellData WHERE spotName = '$beach' ";
$result=mysql_query($query);

$num=mysql_numrows($result);

$i=0;
while ($i < $num) {

$finalScore=mysql_result($result,$i,"finalScore");
$i++;
}


//script
$newCol = "ALTER TABLE trendData ADD '$trendDate' VARCHAR(30)";

mysql_query($newCol);

$save = "UPDATE trendData SET $trendDate = '$finalScore' WHERE spotName = '$beach'";

mysql_query($save);

I would really appreciate any help at all! Thanks

This is the wrong way to go about it; you want to add rows, not columns. After a year your table2 would have 52 columns in it!

Instead simply create a column in table2 and name it trend_date. Each day do an INSERT query on the table, using the data from table1. At the end of the year you'll have 52 rows stored.