在php中从同一个表运行两个查询

I want to display a list of team members and their information in one table at the top of a page and then break them out into teams based on values inside one cell on their respective row.

我想在页面顶部的一个表格中显示团队成员及其信息的列表,然后根据各自行中一个单元格内的值将它们分成小组。

Here's what the "members" table looks like this:

以下是“成员”表的内容:

+------+------------------------+------------------+
| name | interests              | email            |
+------+------------------------+------------------+
| John | Golf, Hockey, Baseball | John@example.com |
| Fred | Hockey                 | Fred@example.com |
+------+------------------------+------------------+

Here's a simplified version of my code:

这是我的代码的简化版本:

$result = mysql_query("SELECT * FROM members");
while($row = mysql_fetch_array($result)){ 
echo "$row['name'] . $row['interests'] . $row['email']"
}

while($row = mysql_fetch_array($result)){
$sport = array($row['interests']);
if (in_array("Golf", $sport)) {
echo "$row['name'] . $row['email']"
}
if (in_array("Hockey", $sport)) {
echo "$row['name'] . $row['email']"
}
if (in_array("Baseball", $sport)) {
echo "$row['name'] . $row['email']"
}

How do I get the page to look like this?:

如何让页面看起来像这样?:

All Members

Name: John
Interests: Golf, Hockey, Baseball
Email: john@example.com

姓名:John兴趣:高尔夫,曲棍球,棒球电子邮件:john@example.com

Name: Fred
Interests: Hockey
Email: fred@example.com

姓名:Fred兴趣:曲棍球电子邮件:fred@example.com

Teams

Golfers:
John - john@example.com

高尔夫球手:John - john@example.com

Hockey Players:
John - john@example.com
Fred - fred@example.com

曲棍球运动员:John - john@example.com Fred - fred@example.com

Baseball Players:
John - john@example.com

棒球运动员:约翰 - john@example.com

4 个解决方案

#1

This code Show All Members and Team

此代码显示所有成员和团队

 $result = mysql_query("SELECT * FROM members");
 $members=array();
 while($row = mysql_fetch_array($result)){ 
     $members[]=$row;
 }
 echo "All Members:<br>\n";
 foreach($members as $row){
    echo "Name: ".$row['name'] ."<br>Interests: ". $row['interests']."<br>Email:". $row['email']."<br>\n";
 }
 echo "Teams:<br>\n";
 echo "Hockey Players:<br>\n";
 foreach($members as $row){
    if($row['interests']=="Hockey"){
        echo $row['name'] ."-". $row['email']."<br>\n";        
    }
 }
 echo "Golfers Players:<br>\n";
 foreach($members as $row){
    if($row['interests']=="Golfers"){
        echo $row['name'] ."-". $row['email']."<br>\n";        
    }
 }
 echo "Baseball Players:<br>\n";
 foreach($members as $row){
    if($row['interests']=="Baseball"){
        echo $row['name'] ."-". $row['email']."<br>\n";        
    }
 }

#2

This is untested but you could try doing something like querying all members to get there interests and then use a foreach loop to go through each member with that interest.

这是未经测试的,但您可以尝试执行某些操作,例如查询所有成员以获得兴趣,然后使用foreach循环来检查具有该兴趣的每个成员。

$result = mysql_query("SELECT * FROM members");
while($row = mysql_fetch_array($result)){ 

$interests =  $row['interests'];

foreach($interests as $interest){
     echo $interest." Players";


      $result = mysql_query("SELECT * FROM members WHERE interests='$interest'");

       while($row = mysql_fetch_array($result)){ 
         $sport = array($row['interests']);
          if (in_array($interest, $sport)) {
             echo "$row['name'] . $row['email']"
          }
       }


   }
}

#3

// Fetch data
$db = new mysqli( 'localhost', 'root', '', 'test' );
$query = "SELECT * FROM members";
$result = $db->query( $query );

// Pre-create sorted arrays
$members = array();
$teams = array();
while ( $row = mysqli_fetch_row( $result ) ) {
    $members[] = $row;
    $tm = explode( ',', $row[1] );
    foreach( $tm as $game ) {
        $teams[trim($game)][] = $row[0] . ' - ' . $row[2];
    }
};

// Echo the result
echo '<h2>All members</h2>';
foreach( $members as $member ) {
    echo 'Name: '.$member[0].'<br/>';
    echo 'Interests: '.$member[1].'<br/>';
    echo 'E-mail: '.$member[2].'<br/><br/>';
}

echo '<h2>Teams</h2>';
foreach( $teams as $sport => $team ) {
    echo '<b>'.$sport.'</b></br>';
    foreach( $team as $player ) {
        echo $player . '</br>';
    }
    echo '<br />';
}

And the result:

结果如下:

All members
Name: John
Interests: Golf, Hockey, Baseball
E-mail: John@example.com

Name: Fred
Interests: Hockey
E-mail: Fred@example.com

Teams
Golf
John - John@example.com

Hockey
John - John@example.com
Fred - Fred@example.com

Baseball
John - John@example.com

#4

<?php

$result = mysql_query("SELECT * FROM members");
while($row = mysql_fetch_array($result)){
    $members[] = $row; //bulid members array
    $member_teams = explode(',',$row['interests']); //get array of interests for row
    foreach ($member_teams as $team) { //cast name and emial to team list
       $teams[$team]['title'] = $team;
       $teams[$team]['members'][] = array('name'=>$row['name'],'email'=>$row['email']);
    }    
}



//bulid members list
if (!empty($members)) {
    echo 'All Members<br />';

    foreach ($members as $member) {
        echo 'Name: '. $member['name'];
        echo '<br /> Interests:'. $member['interests'];
        echo '<br /> Email: '. $member['email'];
    }

    echo '<hr>';
    echo '<br />Teams<br />';

    foreach ($teams as $team) {
        echo '<br />'.$team['title'];
        foreach ($team['members'] as $member) { //bulid list of team members
            echo '<br />'.$member['name']. ' - ' . $member['email'];
        }
    }

    // these are lists so i preffer <ul><li>
    // but <br /> is no problem for me


?>

#1