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 10 of 10
  1. #1
    New Coder
    Join Date
    Feb 2010
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    need help with mysql print to table!

    ok what i have is my sql db is like this:
    id,tier,src,school,startdate,enddate,allocation,generated,togoal,status,enrgoal,enrrate,notes,day[1-31].

    I have code that prints that into a html table and it works GREAT.

    BUT i need to remove the day1-31 from displaying. someone said to use an IF statement to not print those rows?

    How do i do that?
    (im not a coder, my lab partner was hit by a car and the rest of the group is picking up his slack, me being the most computer literate, got the coding job)

    also, i need to remove the last two cells in the table which is (generated & allocation-total) they are useless. i just dont want to mess the code up.

    'Generated' tab is in there twice, the one at the end..which i believe is in the code needs to be deleted, beacuse its already printing..look at my db layout at the top..its in the middle. the two cells at the end need to be removed.

    but i need in the 'generated' & 'togoal' to have math involved.
    day1+day2+day3+...day31; (day1-31) all added together to equal '$generated'. and subtract from 'allocation' to equal 'togoal'

    this what i had before & it worked, but its on another page and not like updated to the DB..just calculated it on the page:
    PHP Code:
    $day1 $day2 $day3 $day4 $day5 $day6 $day7 $day8 $day9 $day10 $day11 $day12 $day13 $day14 $day15 $day16 $day17 $day18 $day19 $day20 $day21 $day22 $day23 $day24 $day25 $day26 $day27 $day28 $day29 $day30 $day31 $generated


    $allocation 
    $generated $togoal 
    so what i need is: the math to go into those two cells(generated & togoal) & to mask/hide print the days 1-31.

    i belive the math is already in the code, but idk what im looking at. i just need those two cells to have that math. (generated & togoal are calucating)
    $allocation is updated on db, and is user entereed.

    THANKS

    (my code):
    PHP Code:
    //select all the data you need, sorted by tier and school 
    $sql "SELECT * FROM schoolinfo ORDER BY tier, school"
    $rs mysql_query($sql); 
    //if $rs is false, we have an error.  Unlikely (impossible, really) in this scenario, but get in the habit 
    if ( !$rs ) { 
        echo 
    "Error executing query: {$sql}<P />Error returned: " mysql_error(); 
        die(); 


    //initialize $previousTier to something 
    $previousTier ""

    //loop through the result set, assigning each new row to $row 
    while ( $row mysql_fetch_assoc($rs) ) { 
        
    //if the tier of the current row is different than the tier we have already been on... 
        
    if ( $row['tier'] != $previousTier ) { 
            
    //if previousTier is not empty, close the previous table 
            
    if  ( $previousTier != "" ) echo "</table>"
            
    //set $previousTier to the current tier 
            
    $previousTier $row['tier']; 
            
    //echo the current tier, and print the opening tags for a new table. 
            
    echo "<h3>{$row['tier']}</h3>"
            echo 
    "<table border=1 cellpadding=0>"
            
    //print a header row 
            
    echo "<tr>";         
            
    //print the headers, using the KEYS of $row.  Read the PHP manual page on arrays, and the manual page for array_keys 
            
    foreach ( array_keys($row) as $header ) { 
                echo 
    "<th>{$header}</th>"
            } 
            
    //we have two additional fields, total and allocation - total.  Print the headers: 
            
    echo "<th>Generated</th><th>Allocation - Total</th>"
            
    //close the header row 
            
    echo "</tr>"
        } 
        
    //loop through $row and print out all the data: 
        
    echo "<tr>"
        foreach ( 
    $row as $field ) { 
            echo 
    "<td>{$field}</td>"
        } 

    //close the last table.  
    echo "</table>";  
    ?> 
    like i said i am not a coder. if someone can frankenstein this together, that would be amazing. ive been googling and even rented books from library. but if someone can revise my code that would be amazing.
    and my partner is ok, dude got hit by a car, so as good as he can get..lol.

    recap:
    1)need to output table from db, WITHOUT the days1-31.
    2)need to remove the last two columns (generated & allocations-total) and add the math to the proper cells. (math is in there, just applied to wrong cells, i think..wrong cells = last two [labeld generated & allocations-total)

    either rewrite script? or revise this one?

    thanks so much really.
    Last edited by 1337hovie; 02-03-2010 at 09:53 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Alter you're query, only fetch what you need:
    Code:
    SELECT id,tier,src,school,startdate,enddate,allocation,generated,togoal,status,enrgoal,enrrate,notes FROM schoolinfo ORDER BY tier, school
    Assuming those are the correct field names, that should give you exactly what you're looking for.

    Edit:
    Missed half of that :P
    generated + allocations - total I assume is what you want in place of generated, allocations and total and just as a single cell? Or are you looking for the overall sum of this data?
    The above query will be kinda pointless, we'll need to alter you're code to remove those.
    Last edited by Fou-Lu; 02-03-2010 at 10:10 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    Feb 2010
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thats perfect. i actually wanted the generated (one at the end) and allocations-total to be REMOVED. so that above is correct. tahnk you.

    Now just to figure out how to add the calculating math to those fields? since they're not updated on the database. Those cells need to calculate from adding the fields together. how do i get this code
    PHP Code:
    $day1 $day2 $day3 $day4 $day5 $day6 $day7 $day8 $day9 $day10 $day11 $day12 $day13 $day14 $day15 $day16 $day17 $day18 $day19 $day20 $day21 $day22 $day23 $day24 $day25 $day26 $day27 $day28 $day29 $day30 $day31 $generated


    $allocation 
    $generated $togoal 
    into the middle of that table? being that its just outputting..like how do i add it into that fields?

    thanks

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Are these like the sum of all entries? I can't quite visualize what you have here.

    Edit:
    I think I have a pretty good idea now, but I'm just heading home from work. I'll be back in about .5 - .75 hour. Just confirm, you actually have columns in you're database like: day1, day2, day3, ....? (Selecting * is the way to go then >.<)
    Last edited by Fou-Lu; 02-03-2010 at 10:30 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    New Coder
    Join Date
    Feb 2010
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes the columns are individual day1, day2, day3, etc.

    i guess i need to involve them. just need to hide them.

    maybe print them into a table (just those values) and hide it somehow?

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Hmm, I'm debating the easiest approach to this. One query is most optimized, but to control the output like you want is going to be trickier. In this case.... probably the alterations to the query would be easiest. Actually, the easiest would be to normalize the representation of the day into a separate table and just query the count on a co-related subquery. But this will do for now.

    PHP Code:
    // I'm still not sure what the allocation - total is, is total a field?
    $sQry "SELECT id,tier,src,school,startdate,enddate,
    togoal,status,enrgoal,enrrate,notes, generated, allocation AS 'Allocation - Total', (day1 + day2 + day3 + day4 + day5 + day6 + day7 + day8 + day9 + day10 + day11 + day12 + day13 + 
    day14 + day15 + day16 + day17 + day18 + day19 + day20 + day21 + day22 + day23 + day24 + day25 + 
    day26 + day27 + day28 + day29 + day30 + day31) AS Day
    FROM schoolinfo
    ORDER BY tier, school"
    ;

    $rs mysql_query($sQry) or die('Unable to execute query: ' $sQry '; ' mysql_error());

    //initialize $previousTier to something 
    $previousTier ""

    //loop through the result set, assigning each new row to $row 
    while ( $row mysql_fetch_assoc($rs) ) { 
        
    //if the tier of the current row is different than the tier we have already been on... 
        
    if ( $row['tier'] != $previousTier ) { 
            
    //if previousTier is not empty, close the previous table 
            
    if  ( $previousTier != "" ) echo "</table>"
            
    //set $previousTier to the current tier 
            
    $previousTier $row['tier']; 
            
    //echo the current tier, and print the opening tags for a new table. 
            
    echo "<h3>{$row['tier']}</h3>"
            echo 
    "<table border=1 cellpadding=0>"
            
    //print a header row 
            
    echo "<tr>";         
            
    // Consider an unset($row['tier']); at this point so it won't show in results
            //print the headers, using the KEYS of $row.  Read the PHP manual page on arrays, and the manual page for array_keys 
            
    foreach ( array_keys($row) as $header ) { 
                echo 
    "<th>" ucwords($header) . "</th>"
            } 
            
    // These are no longer necessary
            //we have two additional fields, total and allocation - total.  Print the headers: 
            //echo "<th>Generated</th><th>Allocation - Total</th>"; 
            //close the header row 
            
    echo "</tr>"
        } 
        
    //loop through $row and print out all the data: 
        
    echo "<tr>"
        foreach ( 
    $row as $field ) { 
            echo 
    "<td>{$field}</td>"
        } 

    //close the last table.  
    echo "</table>";
    ?> 
    Try that, that what you want?

    To normalize you're database, split the day into a separate field. I'm afraid I don't know what it represents, but if I guess it right than I would have three fields: id, day (1 - 31) and a value of some sorts. Then you can actually query these with a SELECT sum(value) FROM thisnewtable WHERE id = $id. This is incredibly useful when using joins or nested subqueries.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    New Coder
    Join Date
    Feb 2010
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ill have to check it @ school, im on my laptop, but it looks good from here.

    so i just make another table on my database and use those values?

    how would i update the data on my 'update' page to post to that table for just those fields?
    (how do i select the new table, for just those fields?)

    would it be possible to just have the days print to the page, then just hide them?

    but i have rows of records, so it'd have to sum up day1-31 for each id.

    make sense?

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by 1337hovie View Post
    ill have to check it @ school, im on my laptop, but it looks good from here.

    so i just make another table on my database and use those values?

    how would i update the data on my 'update' page to post to that table for just those fields?
    (how do i select the new table, for just those fields?)

    would it be possible to just have the days print to the page, then just hide them?

    but i have rows of records, so it'd have to sum up day1-31 for each id.

    make sense?
    Don't alter any tables without first doing a normalization study to see what it will take. A second table takes 2x the insertions to perform; sometimes normalization is not beneficial (normally it is). It will take code alterations.
    Technically you can just query for all, and display what you'll need. If you choose this route, you'll need to construct you're rows with the fields that you desire - you will no longer be able to use a foreach to generate the records unless you first establish which records should be skipped - both approaches are feasible, personally I would take the filter approach to determine what should be shown for reusability if I were to query all data.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    New Coder
    Join Date
    Feb 2010
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    everything seems to work! EXCEPT the totalling.

    i have the (day1 + day2....) AS Generated, that works fine.

    but i need 'togoal' to be (allocation - generated)

    i have
    PHP Code:
    $sQry "SELECT cid,tier,source,school,startdate,enddate,status,enrgoal,enrrate, allocation, (day1 + day2 + day3 + day4 + day5 + day6 + day7 + day8 + day9 + day10 + day11 + day12 + day13 + 
    day14 + day15 + day16 + day17 + day18 + day19 + day20 + day21 + day22 + day23 + day24 + day25 + 
    day26 + day27 + day28 + day29 + day30 + day31) AS generated, (allocation - generated) AS togoal
    FROM schoolinfo
    ORDER BY tier, cid"

    and that isnt working. am i doing something wrong? is just posts the allocation number, not the subtracted math.

    Thanks

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You can't refer to a previous alias in another calculation. And to do a nested query for it will take a toll.
    You'll need to add it into you're php code itself. Same idea as you originally had with the echo "<th>Generated</th><th>Allocation - Total</th>"; approach, but you'll need it to be toGoal and add the result after the second foreach.

    Edit:
    Y'know, you might actually be able to wrap this up a little nicer, with a virtual logic to it:
    Code:
    $sQryWrapper = "SELECT *, (allocation - generated) AS toGoal FROM ($sQry)";
    Last edited by Fou-Lu; 02-04-2010 at 02:47 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

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