CODEWITHSUNDEEP
phpmysql
Programmer
Programmer

Reputation: 301

Store query results in a different table in PHP/MySql

I'm using PHP/MySql and I'm trying to team users into pairs based on a skill that they have. I have an existing table for users, and an existing table for the teams.

For example, I executed a query which returned 6 members which needs to be paired up into a team.

SELECT * FROM users WHERE skill = 'Office'

users
id/name/skill
1/Bob/Office
2/Ted/Office
3/Tim/Office
4/Bill/Office
5/Shawn/Office
6/Gab/Office

These results must be then paired up, and the expected output should be:

teams
name/member
Office1/Bob
Office1/Ted
Office2/Tim
Office2/Bill
Office3/Shawn
Office3/Gab

Once 2 members are placed in a team, the team name should increment by one.

Any help will be greatly appreciated Thanks.

Edit: I tried this:

$results = mysql_query("SELECT * FROM users WHERE skill = 'Office'"); 
$numrows = mysql_num_rows($results); $name =""; 
if($numrows!=0) { 
    while($row = mysql_fetch_assoc($results)) { 
        $name = $row['userName']; 
    } 
} 

//For incrementing the team name 
$namectr=0; 
for($ctr=0;$ctr<$results_num;$ctr++) { 
    if($ctr%2==0) { 
        $query = mysql_query("INSERT INTO teams VALUES ('Office$namectr','$name')");
        $ctr++; if($ctr%2==1) { 
            $query = mysql_query("INSERT INTO teams VALUES (Office$namectr','$name')");
            $namectr++;
        }
    }
}

Upvotes: 1

Views: 321

Answers (2)

CKKiller
CKKiller

Reputation: 1422

why not try:

$result = mysql_query("SELECT * FROM users WHERE users.skill = 'office'");
$count = 0;
$sqlcount = 0;
$offcount = 1;
while ($source = mysql_fetch_array($result)) {
  if ($count < 1) {
    $office = $source['skill'] . $offcount;
    $name = $source['name'];
    $result[$sqlcount] = mysql_query("INSERT INTO teams ('name', 'member') VALUES ('$office', '$name')");
    $sqlcount++;
  } else if ($count >= 1) {
    $offcount++;
    $count = 0;
    $office = $source['skill'] . $offcount;
    $name = $source['name'];
    $result[$sqlcount] = mysql_query("INSERT INTO teams ('name', 'member') VALUES ('$office', '$name')");
    $sqlcount++;
  } else {
    echo "ERROR" . mysql_error();
  }
}//end while
$sqlcount = 0;
while ($result[$sqlcount] != "") {
  if ($source = $result) {
  } else {
    echo "ERROR! " . mysql_error();
  }
}//end while

Upvotes: 1

MrJ
MrJ

Reputation: 1938

Couldn't you do your database query, then do something like the below:

$count=0;
$team=1;
$teams = array();
foreach($result as $output){
    if($count % 2 == 0){
        // if even number, reset count and increment position
        $count=0;
        $team++;
    }
    $teams[] = $output['skill']." ".$team." - ".$output['member'];
    $count++;
}

Something like the above, but it is untested, but should work in theory.

Upvotes: 0

Related Questions