SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    May 2008
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    accessing previous row in a dynamic data table

    Am outputting data from from database into a table by looping.what i want to do is add to previous total to date cell + current daily = current total to date.
    now this is difficult because the data is dynamic,so how do i put previous cell information in a variable then add it to current daily net to produce current total net.

    also finally how do i make a monthly archive so that users can view record by month without me having to create a page for each of the month.?

    see my entire code below

    <?php
    require("inc/conn.php");
    $page = 7;
    //get this page's content from the database
    $sql = "SELECT * FROM tblpages WHERE pageID = " .$page;
    if ($result = mysql_query($sql)) {
    $row = mysql_fetch_array($result);
    $pageHeading = $row['pageHeading'];
    $pageContent = $row['pageContent'];
    }

    //build and issue query
    $sql = "SELECT * FROM trackrecord ORDER BY date";
    $result = @mysql_query($sql) or die(mysql_error());
    $num=mysql_numrows($result);
    mysql_close();



    require_once('header.php'); ?>

    <script type="text/javascript" src="jquery.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
    //hide the all of the element with class msg_body
    $(".msg_body").hide();
    //toggle the componenet with class msg_body
    $(".msg_head").click(function(){
    $(this).next(".msg_body").slideToggle(600);
    });
    });
    </script>


    <div id="content_alert">
    <div class="post1">
    <h1>Track Record</h1>

    <div class="msg_list">
    <p class="msg_head">2009 Track record by Month</p>
    <div class="msg_body">
    <ul>
    <li><a href="01_track.php">january</a></li>
    <li><a href="02_track.php">february</a></li>
    <li><a href="03_track.php">march</a></li>
    <li><a href="04_track.php">april</a></li>
    <li><a href="05_track.php">may</a></li>
    <li><a href="06_track.php">june</a></li>
    <li><a href="07_track.php">july</a></li>
    <li><a href="08_track.php">august</a></li>
    <li><a href="09_track.php">september</a></li>
    <li><a href="10_track.php">october</a></li>
    <li><a href="11_track.php">november</a></li>
    <li><a href="12_track.php">december</a></li>
    </ul>
    <!--orem ipsum dolor sit amet, consectetuer adipiscing elit orem ipsum dolor sit amet, consectetuer adipiscing elit-->
    </div>
    </div>
    <div style="clear: both;">&nbsp;</div>
    <table id="mytable_ie" cellspacing="0" summary="">
    <caption>Track record(Pips) </caption>
    <tr>
    <th scope="col" abbr="date">date</th>
    <th scope="col" abbr="currency pair">currency pair</th>
    <th scope="col" abbr="recommended lot size">entry price</th>
    <th scope="col" abbr="suggested entry">profit 1</th>
    <th scope="col" abbr="suggested stop">profit 2</th>
    <th scope="col" abbr="time of call">profit 3</th>
    <th scope="col" abbr="valid till">daily net</th>
    <th scope="col" abbr="target p1">total to date</th>

    </tr>

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

    $field1 =mysql_result($result,$i,"date");
    $field2 =mysql_result($result,$i,"pair");
    $field3 =mysql_result($result,$i,"entry_price");
    $field4 =mysql_result($result,$i,"profit_1");
    $field5=mysql_result($result,$i,"profit_2");
    $field6 =mysql_result($result,$i,"profit_3");
    $field7 =mysql_result($result,$i,"daily_net");
    $field8 =mysql_result($result,$i,"total_to_date");
    ?>


    <tr>
    <td><? echo $field1; ?></td>
    <td><? echo $field2; ?></td>
    <td><? echo $field3; ?></td>
    <td><? echo $field4; ?></td>
    <td><? echo $field5; ?></td>
    <td><? echo $field6; ?></td>
    <td><? echo $field7; ?></td>
    <td><? echo $field8; ?></td>

    </tr>

    <?
    $i++;
    }

    echo "</table>";
    ?>
    <div class="">&nbsp;</div>

    <div style="float: left; width: 800px;">
    <div class=""><img src="includes/graph.php" alt="graph"/></div>
    </div>

    </div>
    </div>

    <?php require_once('footer.php'); ?>

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    view_records_for_month.php?month=4&year=2007
    PHP Code:
    $month = (int) $_GET['month'];
    $year = (int) $_GET['year'];
    $day 1;

    #sanity check
    if (!checkdate($month$day$year) || $year 1900) {
        
    #error
    }

    $sql "
    SELECT `date`
         , pair
         , entry_price
         , profit_one
         , profit_two
         , profit_three
         , daily_net
      FROM trackrecord
     WHERE `date` BETWEEN '
    $year/$month/$day' AND LAST_DAY('$year/$month/$day')
    ORDER
        BY `date`
    "
    ;

    $result mysql_query($sql) or die('bad query');
    if (
    mysql_num_rows($result) < 1) {
        
    #no records
    }

    $running_total 0;
    while (
    $row mysql_fetch_assoc($result)) {
        
    $running_total += $row['daily_net'];
        echo 
    "<td>$row[date]</td>\n";
        
    #etc...
        
    echo "<td>$row[daily_net]</td>\n";
        echo 
    "<td>$running_total</td>\n";

    Don't name your column date. It's a reserved mysql keyword. http://dev.mysql.com/doc/refman/5.1/...ved-words.html

    List the fields you want from the database. Don't use the *, it's slower, and your query becomes less self documenting.

    mysql_fetch_assoc() is generally much easier to use than a ton of calls to mysql_result(). It's generally faster too.

    Make sure you have an index on the date column.

  3. #3
    SitePoint Addict
    Join Date
    May 2008
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply,also do i need to put the line break "\n" in the TD tag?

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No.

  5. #5
    SitePoint Addict
    Join Date
    May 2008
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry i meant to say after each td tag like the one you did: echo "<td>$row[date]</td>\n";
    all the cells are supposed to be on the same line meaning same row

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    html tables use <tr> tags to group a set of <td> tags into a row. I put the newline chars in because it makes your html easier to read. html ignores these.

    In case it's not obvious, my code is not complete. You're supposed to complete it.

  7. #7
    SitePoint Addict
    Join Date
    May 2008
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks


Bookmarks

Posting Permissions

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