Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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"

    PHP Code:
    //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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •