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:

$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):

//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.

Alter you're query, only fetch what you need:

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.


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.

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 $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

Are these like the sum of all entries? I can't quite visualize what you have here.


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 >.<)

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?

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.


// 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.

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?

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.

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

$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

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.


Y'know, you might actually be able to wrap this up a little nicer, with a virtual logic to it:

$sQryWrapper = "SELECT *, (allocation - generated) AS toGoal FROM ($sQry)";