Grouping listed items

[phptryhard]

Hello

I am looking to make a program that will fetch info from a db and sort it for me as such


the DB has 1 table 5 fields
id, Fname, Lname, weight, Gender

I want to group by gender, and weight,

groups of 6, the weight difference between the heaviest and the lightest in the group of 6 can not be greater then 10%

in the event a group can not be built following these criteria a NULL value is to be inserted making groups of 4 or 5 with 1 or 2 NULL's



I'm pretty sure I'll be able to figure out how to display them in a round robin style format once I get help figure out how to do the queries, should I be storeing info in a temporary table? please any help would be great.

[fenway]

Making 6 groups is not a problem; neither is the NULL requirement. But how can you guarantee only a 10% difference? Please clarify.

[phptryhard]

[!--quoteo(post=338054:date=Jan 19 2006, 12:34 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 19 2006, 12:34 PM) [snapback]338054[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Making 6 groups is not a problem; neither is the NULL requirement. But how can you guarantee only a 10% difference? Please clarify.
[/quote]


Ok here is far more detail


The Program is going to be a leage meet generator for the Local Highschool wrestling program.

Each week the Wrestlers will be weighed and there info will be stored in the DB

id/Fname/Lname/weight/gender

after the tournement is over the db will be wiped and start fresh for the next week.

approx 100- 150 wrestlers weights between 38Kg and 150kg

the tournement is set up as a round robin, which is everyone will wrestle everyone in said weight class.

example of a 4 Person Round Robin

Round 1
A + B
C + D
Round 2
A + C
B+ D
Round 3
A + D
B + C

Since I have no Control over the exact weights I need to write a mathmatical equation to check to make sure that the heavest is not 10% > then the lightest in the group.

I figure the program will have to work like so for a 4 man round robin

Start from lightest weight.
> Select next lightest weight if < 10% difference add to RR if > 10% start new RR
> Select next lightest weight if < 10% difference from first guy in current RR add to RR, if > 10% add new RR
> Select next lighest weight if << 10% difference from first guy in current RR add to RR, if > 10% add new RR if current RR has 4 members start new RR


and so on. probably be a way to make it cleaner when I actually start thinking code rather then words.


I just need to figure out how to make the best SQL statements to minimize the transactions to the server with mySQL/PHP

[fenway]

There's nothing wrong with the logic you describe -- except that you can't possibly guarantee the same number of people in each group AND a 10% weight difference limition. What I don't understand is what this has to do with MySQL at this stage. Once you get back all the weights, you need to do this "grouping" in PHP, and then mark the players accordingly.

[phptryhard]

if you notice in the logic if the weight is beyond 10% a new group starts,

if I had 3 guys with in a round robin class of 4 because the 4th would be too heavy, that forth is now the lightest weight in the next class up from that and the group of 3 rounds would look like this


Round 1
A + Sitout
C + D
Round 2
A + C
D + Sitout
Round 3
A + D
C + sitout.


my mySQL experience has been limited to forum modifications, and dealing with pre existing applications. I was hopeing that mySQL had the logic to do these groupings.

I guess I have to use PHP to do them, and just use mySQL to store the data


how should I go about getting the groups?

select * from wrestlers Order by weight (what expression would limit return by not having a difference of 10% )

should I store this grouping as a variable? then check for the last ID used and search from next ID for next group not differing by 10% max 4 people? and continue on this path?

thanks for the help thus far by the way.

[fenway]

Understood. You cannot do this in MySQL -- you need to retrieve all the rows in PHP, keep track of the current min weight, and iterate though the rows until you hit min+10%. Then start a new RR group, and repeat until you're done. Once you have this data represented in PHP, you can store this as a new table in MySQL with a FK link back to the wrestlers. Then all you need in a MySQL query to generate the RR pairings.

[phptryhard]

ok

so I've had a crack at it for a bit and am lost

so I figured I would make the results generated from an SQL query an array then do mathematical operations on the weight field

so I tried this code
[code]
$sql_kg = "SELECT * FROM wrestlers ORDER BY weight DESC";
$query = mysqli_query($link, $sql_kg);
while ($r = mysqli_fetch_array($query, MYSQLI_BOTH) ) {
         $field1 = $r['Fname'];
         $field2 = $r['Lname'];
         $field3 = $r['weight'];
}
$w1 = array ($field1);
$w2 = array ($field2);
$w3 = array ($field3);


print "$w1[0].$w2[0].$w3[0] <br>";

[/code]

works fine there displays the lightest weight both name and weight

BUT if I add the line
[code]

print "$w1[1].$w2[1].$w3[1] <br>";
[/code]

beneath it I don't get the next lightest weight displayed.

got any advice? see any glaring holes in my idea?

thanks.

[fenway]

You need to create the array first, and keep pushing the field values on it, not the other way 'round.

[phptryhard]

ok comming back to this problem, been away from it for a while

this is my code

[code]
<?php

// attempting to figure out how to build with following logic

/*  Database is Populated with Wrestlers  of different weights
Starting from lightest weight  build tournement of round robins,
each round robin consisting of a Maximum of 4 people,  
that group of 4 can not differ in weight by more then 10%
*/

// connect to DB
include('unpw.php');
include('db.php');


// attepmted logic
// find and display  min weight

// $sql_kg = "SELECT * FROM wrestlers WHERE weight=(Select MIN(weight) FROM wrestlers) ORDER BY weight DESC";
$sql_kg = "SELECT * FROM wrestlers ORDER BY weight ASC";
$query = mysqli_query($link, $sql_kg);
echo "<table Border = 1>";
echo "<tr><td>First name</td><td>Last name</td><td> weight</td></tr>";
while($results = mysqli_fetch_array($query, MYSQLI_BOTH)) {

/*
reference for what each value is in the DB
$results[0] = ID
$results[1]  = Fname
$results[2] = Lname
$results[3] = Weight
$results[4] = gender
*/

// display contents of  db ordered by weight
printf("<tr><td>%s </td><td> %s</td><td>%s</td></tr>\n",
        $results[1], $results[2], $results[3]);

    
}
echo "</table>";

?>
[/code]



so this displays every ones weight great. correct order.

now how do I manipulate the results?

I want to compare the first $result[3] with the second $result[3] if they follow the criteria I want then I want to compare #3 result[3] with #1 result[3] if it follows the criteria then I want to compare #4 result[3] with #1 result[3] if it follows the criteria then I want to take #5result[3] and repeat what I just did from result 1 comparing the next 3 results.

the criteria is
#1 result[3] <= next result[3] + 10%

not sure how I want to store my groupings yet either in there own table in the DB or as a variable, probably there own DB.

thank you for the ongoing help,

[fenway]

Now that you've got the DB portion working, I suggest you post your "new" question in a more appropriate forum -- personally, I don't much experience on the PHP-side, and I'm sure the regular PHP experts around in other forums will be much more helpful. Good luck.

[wickning1]

I'm sorry I don't have much time to explain but here's a rough idea of how I would determine the groups. Maybe it'll be a useful starting point for you.

Note that I have my own quick version of doing a mysql query, using $db->get() isn't going to work for you, but it should be clear what it's supposed to do for me.

[code]
<?php
$i = 0;
$oldmw = 0;
$count = $db->get("SELECT COUNT(*) FROM wrestlers");
while (count($used) <= $count) {
    $oldmw = $minw;
    $minw = $db->get("SELECT MIN(weight) FROM wrestlers WHERE weight > $oldmw");
    $group = $db->getall("SELECT * FROM wrestlers WHERE weight < $minw * 1.10 AND weight > $oldmw");
    echo "Group $i";
    foreach ($group as $wrestler) {
        echo $wrestler['Fname'] . " " . $wrestler['Lname'] . "<br>\n";
    }
    $i++;
}
?>
[/code]

[phptryhard]

[!--quoteo(post=341624:date=Jan 31 2006, 05:03 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Jan 31 2006, 05:03 PM) [snapback]341624[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I'm sorry I don't have much time to explain but here's a rough idea of how I would determine the groups. Maybe it'll be a useful starting point for you.

Note that I have my own quick version of doing a mysql query, using $db->get() isn't going to work for you, but it should be clear what it's supposed to do for me.

[code]
<?php
$i = 0;
$oldmw = 0;
$count = $db->get("SELECT COUNT(*) FROM wrestlers");
while (count($used) <= $count) {
    $oldmw = $minw;
    $minw = $db->get("SELECT MIN(weight) FROM wrestlers WHERE weight > $oldmw");
    $group = $db->getall("SELECT * FROM wrestlers WHERE weight < $minw * 1.10 AND weight > $oldmw");
    echo "Group $i";
    foreach ($group as $wrestler) {
        echo $wrestler['Fname'] . " " . $wrestler['Lname'] . "<br>\n";
    }
    $i++;
}
?>
[/code]
[/quote]

thanks for the reply, you have the veriable $used in the while statement, but no place declares that variable. what is it?