i just dont get the sum.

dinno2

Help please,
been working on this one week too long, This is my complete script, sorry its kinda long, my problem is down at the bottom, i want to show total hours, but cant seam to get it right.
If you notice, the script is creating a table with alternating colors, i dont mind if my total hours at the bottom is all by itself, just help me show it.

<?PHP
$db = mysql_connect("localhost","xxxx","xxxx") or die("Problem connecting");
mysql_select_db("xxxxx") or die("Problem selecting database");
$query = "SELECT FROM pirep2 ORDER BY id";
if ($pilotid == "")
{$pilotid = '%';}
$result = mysql_query ("SELECT FROM pirep WHERE pilotid LIKE '$pilotid%'");
$numofrows = mysql_num_rows($result);
include ("header.php");
?>
<?PHP
print "<center><center>";
print "<TR>\n<TD>Your Logbook</TD>\n";
print "<hr>";
echo "<TABLE BORDER=\"1\">\n";
echo "<TR bgcolor=\"#FFFF9F\"><td align=\"center\">  Pilot ID  </TD><td

align=\"center\">  Departure  </TD><td align=\"center\">

Destination  </TD><td align=\"center\">  Hours  </TD><td align=\"center\"><font

face=Arial size=1>  Route  </TD><td align=\"center\">  Feul Used  </TD><td

align=\"center\">  Date  </TD><td align=\"center\">  Comments

</TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"lightblue\">\n";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"#D2FFE9\">\n";
}
echo "<td align=\"center\">".$row['pilotid']."</TD><td align=\"center\"><font face=Arial

size=1>".$row['departure']."</TD><td align=\"center\">".$row['destination']."</TD><td

align=\"center\">".$row['hours']."</TD><td align=\"center\"><font face=Arial

size=1>".$row['route']."</TD><td align=\"center\">".$row['feul']."</TD><td align=\"center\"><font

face=Arial size=1>".$row['date']."</TD><td align=\"center\">".$row['comments']."</TD>\n";
echo "</TR>\n";
}
/////////////////////////////////////////////////////////////PROBLEM////////
$sql2 = " SELECT SUM(hours) AS total,

FROM pirep

WHERE pilotid = '$pilotid'
";

$result2 = mysql_query($sql2);

        while ($row2 = mysql_fetch_array($result2)) {  
            $hours = $row2["hours"];

//////////////////////////////////////////////////////////////////////////////
include ("footer.php");
echo "</TABLE>\n";
?>

I know its not pretty, but after two weeks its all i can come up with.....?????
any help sure would be appreciated!

pnoeric

my friend, may I make two suggestions:

don't post entire scripts when the problem is in 4 lines at the bottom ;-) seriously, tho, it's a lot better if you cut out ALL extraneous code when you need help debugging something. if the code that hooks you up to the db is working right, you didn't need to post those first ten lines or so... etc.

ok, enough nagging.

why not just keep a running tally as you display each row? i.e. change one of those display lines to:

 ...align=\"center\"><font face=Arial size=1>".$row['hours'];
$hours = $hours + $row['hours'];  // add this row to total
echo "</TD><td align=\"center\"><font face=Arial  ...

that's one way to solve the problem.

btw you know that you can mix php and html in the same script right? you might consider taking all that html stuff out of the PHP echo statements; it will sure made the code easier for you to read and update, plus you won't have to escape every quote mark, etc.

best
eric

dinno2

Thans for the tips Eric, i relly do appreciate it.
As far as a running tally of hours, that wont work, as this is a pilots logbook. so there needs to be all of those seperate hours, and then at the bottom id like to just find a way to get a total sum of hours.
Thanks Again Eric for your suggestion!

pnoeric

ok - I may be miscommunicating - it's not really a running tally that you'd generate, I'm just saying, add the hours for each row to the variable $hours as you go along; at the end of the table, you'll have the total already computed in $hours.

by the way, re: your sql:

$sql2 = " SELECT SUM(hours) AS total, FROM pirep WHERE pilotid = '$pilotid' "; 
$result2 = mysql_query($sql2); 

while ($row2 = mysql_fetch_array($result2)) { 
$hours = $row2["hours"];

since you are selecting AS total -- wouldn't that last line be $hours = $row2["total"]?

best
eric

dinno2

Parse error: parse error, unexpected $ in /home/kunzair/public_html/searchform.php on line 38

I should have told you, But i fugured by my code you would know that i am a newbie.

}
echo "<td align=\"center\">".$row['pilotid']."</TD><td align=\"center\"><font face=Arial

size=1>".$row['departure']."</TD><td align=\"center\">".$row['destination']."</TD><td

align=\"center\">".$row['hours']."</TD><td align=\"center\"><font face=Arial

size=1>".$row['route']."</TD><td align=\"center\">".$row['feul']."</TD><td align=\"center\"><font

face=Arial size=1>".$row['date']."</TD><td align=\"center\">".$row['comments']."</TD>\n";
echo "</TR>\n";
$hours = $hours + $row['hours'];
$sql2 = " SELECT SUM(hours) AS total, FROM pirep WHERE pilotid = '$pilotid' ";
$result2 = mysql_query($sql2);

while ($row2 = mysql_fetch_array($result2)) {
$hours = $row2["total"];
}
include ("footer.php");

i just keep getting that parse error $ everytime i try to mess with this. been two weeks now, i did try almost exactly what you sent me before. what is my problem Eric?

Weedpacket

Well, I got rid of the parse error. The line it claimed the error was on was right at the bottom, but I realised that I was missing a } to finish the while loop down there. Between that and replacing the two big echo statements with pure HTML (I suspected a problem there), the parse error has disappeared. Of course, now I get a pile of other error messages, but that's because I don't have the database.

<?PHP
$db = mysql_connect("localhost","xxxx","xxxx") or die("Problem connecting");
mysql_select_db("xxxxx") or die("Problem selecting database");
$query = "SELECT * FROM pirep2 ORDER BY id";
if ($pilotid == "")
{$pilotid = '%';}
$result = mysql_query ("SELECT * FROM pirep WHERE pilotid LIKE '$pilotid%'");
$numofrows = mysql_num_rows($result);
include ("header.php");
?>
<center>
<TR>\n<TD><b>Your Logbook</b></TD>
<hr>
<TABLE BORDER="1">
<TR bgcolor="#FFFF9F"><td align="center"><font face="Arial" size="1"> Pilot ID </TD><td
align="center"><font face="Arial" size="1"> Departure </TD><td align="center"><font face="Arial" size="1"> 
Destination </TD><td align="center"><font face="Arial" size="1"> Hours </TD><td align="center"><font
face="Arial" size="1"> Route </TD><td align="center"><font face="Arial" size="1"> Feul Used </TD><td
align="center"><font face="Arial" size="1"> Date </TD><td align="center"><font face="Arial" size="1"> Comments
</TD></TR>
<?php
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"lightblue\">\n";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"#D2FFE9\">\n";
}
?>
<td align="center"><font face="Arial" size="1"><?php echo $row['pilotid']?></TD><td align="center"><font face="Arial"
size="1"><?php echo $row['departure']?></TD><td align="center"><font face="Arial" size="1"><?php echo $row['destination']?></TD><td
align="center"><font face="Arial" size="1"><?php echo $row['hours']?></TD><td align="center"><font face="Arial"
size="1"><?php echo $row['route']?></TD><td align="center"><font face="Arial" size="1"><?php echo $row['fuel']?></TD><td align="center"><font
face="Arial" size="1"><?php echo $row['date']?></TD><td align="center"><font face="Arial" size="1"><?php echo $row['comments']?></TD>
</TR>
<?php
}
/////////////////////////////////////////////////////////////PROBLEM////////
$sql2 = " SELECT SUM(hours) AS total,
FROM pirep
WHERE pilotid = '$pilotid'
";
$result2 = mysql_query($sql2);

while ($row2 = mysql_fetch_array($result2)) {
$hours = $row2["total"];
}
//////////////////////////////////////////////////////////////////////////////
include ("footer.php");
echo "</TABLE>\n";
?>

I also note that that last loop is no longer needed: there is always exactly one row returned by that query, so

$sql2 = " SELECT SUM(hours) AS total,
FROM pirep
WHERE pilotid = '$pilotid'
";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);
$hours = $row2["total"];

Would be sufficient.

dinno2

That doesnt work either.
Isnt this crazy, who knew this would be so difficult.
I sure do appreciate your time.
I have looked over the mysql manual, and am able to get the querry to show proprely with myphp admin, however i cant find correct, up to date documentation that seams to work on SUM values with PHP, not even the php manual covers this the way i need to use it.
any ideas on where eles to look, i hate to see you wasting your time with this any further.
BTW i have read, every tutorial on php, and mysql from phpbuilder, devshed, phphelpdesk, you name it.
Thanks again for your trouble.
Michael

pnoeric

Michael, don't be frustrated, we're on the right track... let's take this one step at a time.

First-- did you use Weedpacket's version of your code? it's much cleaner and easier to follow. Before you do anything else, look at his code and be sure you understand how he simplified it for you. Seriously.

Second-- so the ONLY problem you're seeing is that the sum isn't being returned at the bottom of the page? Here's your SQL:

SELECT SUM(hours) AS total, FROM pirep WHERE pilotid = '$pilotid'

Now, here is how you debug a problem like tihs... so you have to verify that you have followed every step:

Have you tried this SQL manually in PHPMyAdmin with a valid $pilotID and made sure that it returns exactly what it should? To test this, just put 'SELECT SUM(hours) AS total FROM pirep WHERE pilotid=1' (or whatever) into PHPMyAdmin and be sure you get results.
Are the results coming back in a column named 'total'?
Have you made you that the SQL statement is running in your script? To test this, try a different SQL statement, one you know is working, like 'SELECT * FORM pirep' and see if you get any results.
Are you doing anything with $hours after you get it? I'm noticing that you script never actually echos $hours to the screen or anything... do you print it in footer.php?
If you print it in footer.php, are you sure that footer is receiving the variable okay? To test that, set the variable manually ('$hours=9999') right before you call footer.php and see that you get the result.

Do you see the line of thinking here? the real value in this message is not fixing your current problem-- it's teaching you how to break down a PHP code problem so you can debug them easily. learn the technique ;-)

Follow the steps above and let us know what happens!! we'll get this workin' yet...

best
Eric

dinno2

ok
1)
in myphp admin, in order to show the results as TOTAL...i used this querry with the results showing below the word Total

SELECT SUM(hours)as Total FROM pirep WHERE pilotid='KVA101'

yes they do appear under the word Total
as total hours

3)
I dont know how to input this into my script, to show results, thats where my problem lies.

4)
<td align=\"center\">".$row['hours']."</TD>
that is in the script, showing the hours for that row.

5)
footer is just a generic footer for all my pages, I am trying to get the sum hours to print on the same page as the script.

the address to the page so maybe you can see what im trying to do is as follows.
http://www.kunzair.com/searchform.php?pilotid=KVA101

Thats about it. I guess my main problem is, 1: I have a querry on the script already at the top, works fine, the querry i need to run to get the sum hours results is where i run into errors.

Thanks for sticking with me on this 🙂

pnoeric

ok, so at the end of your script you get the total into $hours ... and then you never display it :-) try adding:

echo 'Total hours: '.$hours;

right?

-E

dinno2

auurrghhh!

ok here is what i have :
blah blah....................................................................
<?php echo $row['date']?></TD><td align="center"><?php echo $row['comments']?></TD>
</TR>
<?php
}
.......................................THE FUN STUFF.......................................
$sql2 = " SELECT SUM(hours) AS total, FROM pirep WHERE pilotid = '$pilotid'";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);<<<<------PROBLEM
$hours = $row2["total"];
echo 'Total hours: '.$hours;
include ("footer.php");
echo "</TABLE>\n";
?>

That line gives me the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/kunzair/public_html/searchform.php on line 40

pnoeric

sounds like you're not getting any results from the MySQL ...try echoing the $sql2 statement to the screen isntead of running it on mysql, and make sure that the SQL is correct.

p.s. you sound frustrated. don't be :-) this is standard debugging practice; the only thing is that it's taking a while to go back and forth because you don't know the tricks yet. soon this will be second nature to you. repeat after me: simplify, test, verify... as in, when you have a bug, SIMPLIFY your code [cut anything that you know works or isn't important], TEST what is there that you think works, and VERIFY that variables are what they should be [often by just echoing them to the screen]...

best
Eric

pnoeric

p.s. am I the only one not getting any word wrap on these entries? my screen is like a zillion pixels wide, horizontal scrolling sucks. is there a setting or something for this?

Aeiri

Yeah, that no word wrapping is very annoying, and I'm in 1152x864!

I just have one very little thing that would make your code a little more efficient (maybe 1 millisecond at most, but it's just bugging me).

Change:

while ($row2 = mysql_fetch_array($result2)) {
$hours = $row2["total"];
}

To:

while ($row2 = mysql_fetch_array($result2)) $hours = $row2["total"];

It bugs me when people put braces around one line statements...

dinno2

ok you are not going to believe this, got it to work...the problem was a comma here:
$sql2 = " SELECT SUM(hours) AS total, FROM pirep WHERE pilotid = '$pilotid'";
changed to
$sql2 = " SELECT SUM(hours) AS total FROM pirep WHERE pilotid = '$pilotid'";
and it works, wahla...cant beleive, all that for a comma <G>
thanks everyone for your time i sure do appreciate it!!!!

pnoeric

lol, don't you hate it when it's that kind of stuff?

see, this is an argument for running the SQL statementr in PHPMyAdmin first... you said you did but clearly, you were re-typing it, not copying-and-pasting it from the source code... God is in the details!! as is bug-free code! ;-)

glad to hear it's workin' for ya

best
Eric