The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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";

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($result, 0);

~~

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.

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:

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?

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.

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"?

//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'";

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";

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";

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!

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.

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";

//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";

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?

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";

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.