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.
Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25

Thread: Sorting a SUM

  1. #16
    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
    Heheh, yes PHP is probably the most complicated language I've ever used. The, um, quirks I'll call them (ie: inconsistencies ) are quite nonsensical. I will give it that its extremely easy to learn basic PHP, but if its your first language than it will teach really bad habits.

    So things like mysql_fetch_* returning false is perfectly fine for the basic PHP level. Null = false = 0 = '0' = '' = array(), so with the weak datatypes it actually works alright (MySQLi fixes this btw, and returns null when no more records are present). Problem is more into the OO approach for things where testing for null is common place. Unlike the procedural PHP where null is rarely used for any type of condition.
    It is frustrating though. Only in PHP do you actually need to use === to see if a 'string' contains 's'. Lol. Even perl returns -1 on no match.

    Oh and for the for loop, yeah there is nothing wrong with that. You could also use a while to do both: while (++$i && $row = mysql_fetch_assoc($qry)). Note that if you start $i at 0 (which is what I'd do if I wanted 1 as the first record), and you don't provide a condition for the increment like above, using $i++ won't work of course since its evaluated as 0, which would terminate the loop.
    Ahh, good fun.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  2. #17
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Wow, that's perfect!

    I was wondering one last (maybe?) thing. Since this query is looking at just one team that I've specified, is it possible to set up something where depending on the .php url it could automatically look up the team's results.

    Say I name this "games.php", is there a way to set it up so that if you go to "games.php?Team_ID=ATL" it would give you ATL and ..?Team_ID=NYY would give you the Yankees and so on? Or would this be too difficult for a rook?

  3. #18
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    One more thing

    After playing around with what you guys have helped me with I decided to get bold and try a division function to calculate career AVG (hits divided by at bats). When I run the following query in my SQL I get what I want, but when I do this with the PHP, I get the correct order and players, but the AVG column returns all zeros.

    PHP Code:
    echo '<td valign="top" align="left" width="250">';

    $sql 'SELECT Pos, FName, LName, ROUND((SUM(H)/SUM(AB)),3) AS AVG '
            
    ' FROM retired_batters '
            
    ' WHERE Team = \'ATL\' '
            
    ' GROUP BY Pos, FName, LName'
            
    ' ORDER BY AVG DESC '
            
    ' LIMIT 10'
         
    $result mysql_query($sql) or die(mysql_error());


    echo 
    "<table border='0' cellpadding='2' cellspacing='1' width='250'>";
    echo 
    "<tr class='header'> <td width='15'>Rk</td> <td width='210'>Player</td> <td width='25' align='right'>AVG</td></tr>";

    $i 0;

    while(
    $row mysql_fetch_assoc$result ))  
    {
        if (
    == ($i++ & 1))
        {
            
    // this is odd
            
    $sClass 'odd';
        }
        else
        {
            
    $sClass 'even';
        }
        
    printf('<tr class="%s">
            <td align="right">%d.</td>
            <td>%s %s</td>
            <td align="right">%d</td>
            </tr>' 
    PHP_EOL$sClass$i$row['FName'], $row['LName'], $row['AVG']);


    echo 
    "</table>";

    echo 
    '</td>'

  4. #19
    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 have two questions here.
    The first is by fetching values by get. Yes that would be trivial to do; you simply need to check if your provided value is available, and if so, you then go ahead and fetch it. That's simply a matter of structure like so:
    PHP Code:
    if (isse($_GET['Team_ID']))
    {
        
    // All of this code you have now goes here.
        
    if (get_magic_quotes_gpc())
        {
            
    $_GET['Team_ID'] = stripslashes($_GET['Team_ID']);
        }
        
    $team_id mysql_real_escape_string($_GET['Team_ID']);
        
    $sql 'SELECT Pos, FName, LName, ROUND((SUM(H)/SUM(AB)),3) AS AVG '
            
    ' FROM retired_batters '
            
    ' WHERE Team = \'' $team_id '\' '
            
    ' GROUP BY Pos, FName, LName'
            
    ' ORDER BY AVG DESC '
            
    ' LIMIT 10';
        
    // continue with the rest of your code


    I don't know what kind of results that AVG would be, but what you describe to what is currently in the code would indicate that AVG would be a fractional value: AVG=0.885 for example.
    In the printf code:
    PHP Code:
        printf('<tr class="%s">
            <td align="right">%d.</td>
            <td>%s %s</td>
            <td align="right">%d</td>'
    , ...); 
    Those replacements are %s = string, and %d = integer. If you have 0.885 for example, casting that to an integer would result in 0. What you do is use %f to indicate that it is a double value instead, and you can autoround it with the modifiers. So instead of %d you can use %0.3f which would show three significant digits and the 0.
    That is a guess as to what the issue is since it sounds like the AVG pulls the correct results, its simply display as int.
    This is one reason why printf is often not liked by people, but I personally feel that the ability to modify the output structure and perform argument location swapping trumps that of accidentally providing the incorrect datatype.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  5. Users who have thanked Fou-Lu for this post:

    bayken37 (01-18-2013)

  6. #20
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The second part is perfect, however the first part I'm getting the following: "Fatal error: Call to undefined function isse()"

  7. #21
    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
    Typo on my part, the function is isset with a 't' at the end.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  8. #22
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Woohoo!

    What if I wanted to only show those players who had GREATER THAN a certain number of the SUM(AB), say 100. I've been messing around with that in vain on MySQL but can't quite choose the right thing.

    Sorry to keep bombarding you with questions but I really appreciate it!

  9. #23
    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
    That requires an aggregate HAVING clause:
    PHP Code:
        $sql 'SELECT Pos, FName, LName, ROUND((SUM(H)/SUM(AB)),3) AS AVG '
            
    ' FROM retired_batters '
            
    ' WHERE Team = \'' $team_id '\' '
            
    ' GROUP BY Pos, FName, LName'
            
    ' HAVING SUM(AB) > 100'
            
    ' ORDER BY AVG DESC '
            
    ' LIMIT 10'
    If you use any aggregation (ie: group by on a property and looking for something like a COUNT, SUM, etc), then you always compare it using the HAVING clause. The structure is always: SELECT fields FROM location WHERE where GROUP BY groupby HAVING having ORDER BY order, so you stick having between the group by and order by.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  10. #24
    New Coder
    Join Date
    Jan 2013
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    using the isset to get the Team_ID what do I need to do to get the tag for something like an img

    like:

    PHP Code:
    <img src="\'' . $team_id . '\'.png"
    What I put in was obviously wrong, but what do I need to change that to to get it to say ATL.png for Team_ID=ATL?

  11. #25
    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 don't wrap it in quotes like that as if that would parse it would be <img src="'ALT'.png"/> which isn't correct. You simply use: <?php echo '<img src="' . $team_id . '.png" alt=""/>';. It must be echoed through PHP if you want to parse a variable.
    If you do this though, I'd suggest not using the $team_id and using a second variable, or not running $team_id through mysql_real_escape_string until its used within the query. Then you should use urlencode on the $team_id within the img tag in case it contains characters that need to be interpreted differently such as a space.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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