...

View Full Version : creating new columns with specific data



nesurfer87
06-30-2007, 06:17 AM
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

Fumigator
06-30-2007, 09:46 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum