SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Percentage in mysql query

    Okay, so I am a newbie to php and mysql. I will try to explain what I am trying to accomplish as best I can. I have a table with the following fields: wireGauge, time, fracture and other fields not used in this query. The fracture field stores the fracture point each time a record is added. There are 3 types of fracture points: anchor bend, break back and weld. What I am trying to do is figure out the percentage for each fracture point for a given wireGauge (such as 3, there are 4 different wireGauges: 2, 3, 4 and 4.5). I also want the user to be able to select the range of time from which to generate this report. So if there are 20 records I want to write a query that figures out the percentage of anchor bends, the percentage of break backs and the percentage of welds between a chosen time period for a given wireGauge such as 3. Once I have written the query I also need to figure out how to display the percentages in a table using php.

    I have attached a screenshot of my table.

    So far I have written the following:
    PHP Code:
    $sql="SELECT fracture, COUNT(*)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    GROUP BY fracture"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_assoc($result)) 

        
    $bbcount0 $row['fracture'];
        
    $bbcount1 $row['fracture'];
        
    $bbcount2 $row['fracture'];

    To print the percentages later on the page I have the following inside a table:
    HTML Code:
        <table>
            <tr>
                <td>
                     <?php echo "$bbcount1"; ?>
                </td>
                <td>
                     <?php echo "$bbcount0"; ?>
                </td>
                <td>
                     <?php echo "$bbcount2"; ?>
                </td>
            </tr>
        </table>
    Thanks for any help to understand and solve this.
    Attached Images Attached Images

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You don't need to change your query, you have all the data you need. Add up the count from all the rows, and divide each row's individual count by that to get its percentage.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the quick response. I actually just figured it out and got it working. I did a count of the total rows like you said and then divided my first query by the total rows.

    I changed my query and variables slightly so they make more sense for the project and now it looks like this:

    PHP Code:
    $sql="SELECT COUNT(fracture)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    AND fracture = 'Anchor Bend'
    AND endType = 'Loop;"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $abcount3 $row['COUNT(fracture)'];
        } 
    Here is my query for the total rows:
    PHP Code:
    $sql="SELECT COUNT(fracture)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    AND endType = 'Loop'"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $totalFracture3 $row['COUNT(fracture)'];
        } 
    Here is how I am printing it out and getting the percentage:
    HTML Code:
        <table>
            <tr>
                <td>
                     <?php echo round(($abcount3/$totalFracture3) * 100) . "%"; ?>
                </td>
                <td>
                     <?php echo round(($bbcount3/$totalFracture3) * 100) . "%"; ?>
                </td>
                <td>
                     <?php echo round(($bbcount3/$totalFracture3) * 100) . "%"; ?>
                </td>
            </tr>
        </table>
    Thanks for your help! I am starting to get better at this.

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The second query is unnecessary, you already have the data (the row count is the same as the sum of all the fractures' counts you already retrieved). Eventually stuff like that catches up to you when you can't afford to be wasteful with expensive queries.

    Also, you don't need a while loop when you know you only have one row

    PHP Code:
    while($row mysql_fetch_assoc($result)) 
    could just be
    PHP Code:
    $row mysql_fetch_assoc($result); 
    Or get rid of all 4 lines with
    PHP Code:
    $totalFracture3 mysql_result($result0); 
    ~~

    Also, right now, you're making no distinction between the fracture types as you loop over the rows. You're displaying the same number 3 times instead of the 3 percentages.

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not completely understanding how to print out that data that you say I have already retrieved, but am eager to learn how and understand this better. I am going to show you my complete code for the queries I am using because now I am sure that I am probably making more work out of it than I need to. Here is what I have for queries:

    PHP Code:

    //query of % that are anchor bend and wire gauge 3 with endType Loop
    $conn mysql_connect("localhost","user","pass") or trigger_error("SQL"E_USER_ERROR);
    $db mysql_select_db("database"$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $sql="SELECT COUNT(fracture)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    AND fracture = 'Anchor Bend'
    AND endType = 'Loop;"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $abcount3 $row['COUNT(fracture)'];
        }
                                    
    //query of % that are break back and wire gauge 3 with endType Loop
    $conn mysql_connect("localhost","user","pass") or trigger_error("SQL"E_USER_ERROR);
    $db mysql_select_db("database"$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $sql="SELECT COUNT(fracture)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    AND fracture = 'Break Back'
    AND endType = 'Loop'"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $bbcount3 $row['COUNT(fracture)'];
        }
                                    
    //query of % that are weld and wire gauge 3 with endType Loop
    $conn mysql_connect("localhost","user","pass") or trigger_error("SQL"E_USER_ERROR);
    $db mysql_select_db("database"$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $sql="SELECT COUNT(fracture)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    AND fracture = 'Weld'
    AND endType = 'Loop'"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $weldcount3 $row['COUNT(fracture)'];
        }
                                    
    //query of total # rows that are wire gauge 3 with endType Loop
    $conn mysql_connect("localhost","user","pass") or trigger_error("SQL"E_USER_ERROR);
    $db mysql_select_db("database"$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $sql="SELECT COUNT(fracture)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    AND endType = 'Loop'"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $totalFracture3 $row['COUNT(fracture)'];
        } 
    I am trying to get the percentage of the following:
    Anchor Bend, Wire Gauge 3, Loop End /(divided by) Total # rows that are Wire Gauge 3, Loop End

    Break Back, Wire Gauge 3, Loop End /(divided by) Total # rows that are Wire Gauge 3, Loop End

    Weld, Wire Gauge 3, Loop End /(divided by) Total # rows that are Wire Gauge 3, Loop End

    I thought I had to query each one separately and then divide it by the total rows that are Wire Gauge 3 with a Loop End. You mentioned not needing a while loop and that I am making no distinction between fracture types. How can I do this in a cleaner and easier way?

    Thanks so much for your help.

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You can get all that information in one query:

    PHP Code:
    $conn mysql_connect("localhost","user","pass") or trigger_error("SQL"E_USER_ERROR); 
    $db mysql_select_db("database"$conn) or trigger_error("SQL"E_USER_ERROR); 

    $sql="SELECT fracture, COUNT(*) AS `count`
    FROM welded_wire_ties 
    WHERE time between '
    $time1' and '$time2
    AND wireGauge = '3' 
    AND endType = 'Loop'
    GROUP BY fracture"
    ;

    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR); 

    $total 0;
    while (
    $row mysql_fetch_array($result)) {
      
    $fractures[$row['fracture']] = $row['count'];
      
    $total += $row['count'];

    Now to display the percentages:
    PHP Code:
        <table>
            <tr>
                <td>
                     <?php echo round(($fractures['Anchor Bend'] / $total) * 100) . "%"?>
                </td>
                <td>
                     <?php echo round(($fractures['Break Back'] / $total) * 100) . "%"?>
                </td>
                <td>
                     <?php echo round(($fractures['Weld'] / $total) * 100) . "%"?>
                </td>
            </tr>
        </table>
    Let me know if any of that doesn't work, I didn't test it

    Also good to note that you don't need to connect to the database every time you issue a query, it doesn't close during execution unless you explicitly close it. Not a big deal, just saves some unnecessary typing; open once at the top and you're good.

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for your help!! That worked great! I have another query to find the MIN, MAX and AVG of the field 'beamPull' when the wire gauge is 3 and the endType is Loop. I also have one for when the wire gauge is 4 and the endType is Loop. Could I use the same principle for the one you helped me with and just group by "wireGauge"?

    Thanks!

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops! I forgot to show you the queries I mentioned in the post above.

    Here they are:
    PHP Code:
    //query of min, max and avg for wire gauge 3 with endType Loop
                                    

                                    
    $sql="SELECT MIN(beamPull), MAX(beamPull), AVG(beamPull)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '3'
    AND endType = 'Loop'"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
            
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $min3 $row['MIN(beamPull)'];
            
    $max3 $row['MAX(beamPull)'];
            
    $avg3 $row['AVG(beamPull)'];
        }
                                    
    //query of min, max and avg for wire gauge 4 with endType Loop                                
    $sql="SELECT MIN(beamPull), MAX(beamPull), AVG(beamPull)
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND wireGauge = '4'
    AND endType = 'Loop'"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
            
    while(
    $row mysql_fetch_assoc($result)) 
        { 
            
    $min4 $row['MIN(beamPull)'];
            
    $max4 $row['MAX(beamPull)'];
            
    $avg4 $row['AVG(beamPull)'];
        } 

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mccawphoto View Post
    Could I use the same principle for the one you helped me with and just group by "wireGauge"?
    Yep

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I tried applying the first query to this second one for the min, max and avg and the result came back as '0' for the MIN value that I tested. I don't think I quite understand how to make this one work yet.

    Here is what I came up with:
    PHP Code:
    //query of min, max and avg for wire gauge 2, 3, 4 and 4.5 with endType Loop
    $sql="SELECT beamPull, COUNT(*) AS 'minmaxavg'
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND endType = 'Loop'
    GROUP BY wireGauge"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $total 0;
    while(
    $row mysql_fetch_array($result)) {
                                        
    $beamPull[$row['wireGauge']] = $row['minmaxavg'];
    $total += $row['minmaxavg'];

    Here is how I am outputting the value for MIN:
    HTML Code:
    <td>
        <?php echo $beamPull['MIN(minmaxavg)']; ?>
    </td>
    What am I missing?
    Thanks.

  11. #11
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You still need to select the same things you were selecting before (min/max/avg), you just add the beamPull column and group by it to get one row for each value in that column.

  12. #12
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I have the basic idea but am not quite understanding how this all works yet. I changed my query but still am not getting any results. Here is what I have now:
    PHP Code:
    //query of min, max and avg for wire gauge 2, 3, 4 and 4.5 with endType Loop
    $sql="SELECT beamPull, MIN(beamPull), MAX(beamPull), AVG(beamPull) AS 'minmaxavg'
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND endType = 'Loop'
    GROUP BY wireGauge"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $total 0;
    while(
    $row mysql_fetch_array($result)) {
    $beamPull[$row['wireGauge']] = $row['minmaxavg'];
    $total += $row['minmaxavg'];

    I am not sure if I need the "AS 'minmaxavg'" When you use "AS 'minmaxavg'" is that creating a "holder" to hold the values from the SELECT?
    I am also not completely understanding what is inside the while loop.
    Can you explain so that I can hopefully understand and fix this?
    Thanks!

  13. #13
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You need to select the wireGauge column so you know what gauge the min/max/avg beam pull in each row correspond to. Selecting beamPull alone will give you nondeterministic results.

    When you wrote "AS 'minmaxavg'", you're only creating an alias for that one column it follows, the average. You would also have had to use `backticks`, not 'single quotes', to create that alias. Right now your query is probably failing.

    If you remove that alias, in your PHP code, there are 3 keys in each $row array: MIN(beamPull), MAX(beamPull) and AVG(beamPull).

    You probably want to do something with those values. If you want to do the same thing you did with the totals before, then create 3 arrays, one for the minimums keyed by gauge, one for the maximums keyed by gauge, and one for the averages keyed by gauge. Or, you can create one big multidimensional array.

    ~

    Some general advice... if you're coding something that you don't completely understand, do it one piece at a time. Don't try to write a bunch of code at once and see if it works, because you won't know which parts do and which parts don't.

    Start with the SQL query here. Run it against your database, outside of PHP. That'll make sure you've written a valid query and you'll know what the results look like. THEN you'll have a better idea of how to write code to do something with those results.

  14. #14
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the direction and advice. It took some time but I got it working and down to one query. If you are curious what I ended up with here is my code:
    PHP Code:

    //query of min, max and avg for wire gauge 2, 3, 4 and 4.5 with endType Loop
    $sql="SELECT wireGauge, MIN(beamPull) AS `min`, MAX(beamPull)AS `max`, AVG(beamPull) AS `avg`
    FROM welded_wire_ties
    WHERE time between '
    $time1' and '$time2'
    AND endType = 'Loop'
    GROUP BY wireGauge"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    while(
    $row mysql_fetch_array($result)) {
    $beamPullmin[$row['wireGauge']] = $row['min'];
    $beamPullmax[$row['wireGauge']] = $row['max'];
    $beamPullavg[$row['wireGauge']] = $row['avg'];

    To output the values I did this:
    HTML Code:
    <td>
        <?php echo $beamPullmin['3.0']; ?>
    </td>
    <td>
        <?php echo $beamPullmax['3.0']; ?>
    </td>
    <td>
        <?php echo round($beamPullavg['3.0']); ?>
    </td>
    Thanks again for all of your help!

  15. #15
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quick Question. Back to the 1st query you helped me with.
    PHP Code:
    //query of % that are anchor bend, break back or weld and wire gauge 3 with endType Loop                                
    $sql="SELECT fracture, COUNT(*) AS `count`
    FROM welded_wire_ties 
    WHERE time between '
    $time1' and '$time2
    AND wireGauge = '3' 
    AND endType = 'Loop'
    GROUP BY fracture"
    ;
                                    
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $total 0;
    while(
    $row mysql_fetch_array($result)) {
    $fractures[$row['fracture']] = $row['count'];
    $total += $row['count'];

    Can I make it so that it works for all 4 wire gauges? So basically right now it finds the percentage for anchor bend, break back and weld fractures for wire guage 3. I also want to do this for wire gauge 2, 4 and 4.5. I know I can create a query for each one separately but I am thinking there should be a way to create one query to do it for all wire gauges. Can you group by more than one field? Would that solve it?

    Thanks

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mccawphoto View Post
    Can you group by more than one field?
    as a matter of fact, yes you can
    Code:
    SELECT wireGauge
         , fracture
         , COUNT(*) AS `count` 
      FROM welded_wire_ties 
     WHERE time BETWEEN '$time1' AND '$time2' 
       AND endType = 'Loop' 
    GROUP 
        BY wireGauge
         , fracture
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so that makes sense, but the part I am still confused on is how to write my while loop and how to output the values for each gauge/fracture point.
    Currently my while loop looks like:
    PHP Code:
    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $total 0;
    while(
    $row mysql_fetch_array($result)) {
    $fractures[$row['fracture']] = $row['count'];
    $total += $row['count'];

    And my output of values looks like:
    HTML Code:
    <td>
        <?php echo round(($fractures['Anchor Bend']/$total) * 100) . "%"; ?>
    </td>
    <td>
       <?php echo round(($fractures['Break Back']/$total) * 100) . "%"; ?>
    </td>
    <td>
        <?php echo round(($fractures['Weld']/$total) * 100) . "%"; ?>
    </td>
    How do I tell it the wire gauge along with the fracture point?
    Thanks!

  18. #18
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could someone help me understand what is going on in the while loop? I have tested my query and it is pulling in data correctly. I am just not understanding how to take the results and get the data out correctly. I am wanting to figure out the percentage of Anchor Bend Fractures that are wireGauge 3. So basically I am wanting to know what percentage of Wire Gauge 3 fractures are Anchor Bend.
    In my original query I did this:
    PHP Code:
    $sql="SELECT fracture, COUNT(*) AS `count`
    FROM welded_wire_ties 
    WHERE time between '
    $time1' and '$time2
    AND wireGauge = '3' 
    AND endType = 'Loop'
    GROUP BY fracture"
    ;

    $result mysql_query($sql$conn) or trigger_error("SQL"E_USER_ERROR);
                                    
    $total 0;
    while(
    $row mysql_fetch_array($result)) {
        
    $fractures[$row['fracture']] = $row['count'];
        
    $total += $row['count'];

    To output the percentage I did this:
    PHP Code:
    <?php echo round(($fractures['Anchor Bend']/$total) * 100) . "%"?>
    Since I have 4 different wire Gauges I want to write the query in such a way as to only have one query instead of one for each wire Gauge. So I wrote this query:
    PHP Code:
    $sql="SELECT wireGauge, fracture, COUNT(*) AS `count`
    FROM welded_wire_ties 
    WHERE time BETWEEN '
    $time1' and '$time2
    AND endType = 'Loop'
    GROUP BY wireGauge, fracture"

    I tested this query and ran it against the database and it works. The problem I am having is understanding how to pull the information out and show the percentages of each type of fracture that is a certain wire gauge.


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
  •