PHP、MySQL。包含子项目的列表

Question

我有一个数据库，保存有关我们提供的类(class)的数据。所以我有 2 个表，一个用于保存类(class)详细信息，两个用于保存参与者详细信息，在每条记录中我保存他们所属的类(class)。

表 1(类(class)信息):ID、类(class)名称、类(class)开始日等

表 2(用户信息):ID、FirstName 等、CourseID

现在，我希望在我的页面上显示所有可用类(class)，并在子列表中显示该特定类(class)的所有参与者。

像这样:

• 类(class) 1
  • 约翰
  • jack
  • 史黛西
  • 丽贝卡

• 类(class) 2
  • 亚当
  • 莉亚
  • 萨拉

我的代码:

<?php require_once('Connections/JPT.php'); ?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "1";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && true) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "index.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0) 
  $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_UserDetails = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_UserDetails = $_SESSION['MM_Username'];
}
mysql_select_db($database_JPT, $JPT);
$query_UserDetails = sprintf("SELECT * FROM UserInfo WHERE Username = %s", GetSQLValueString($colname_UserDetails, "text"));
$UserDetails = mysql_query($query_UserDetails, $JPT) or die(mysql_error());
$row_UserDetails = mysql_fetch_assoc($UserDetails);
$totalRows_UserDetails = mysql_num_rows($UserDetails);

mysql_select_db($database_JPT, $JPT);
$query_GroupList = "SELECT * FROM GroupList WHERE Status = 'A' ORDER BY ID DESC";
$GroupList = mysql_query($query_GroupList, $JPT) or die(mysql_error());
$row_GroupList = mysql_fetch_assoc($GroupList);
$totalRows_GroupList = mysql_num_rows($GroupList);

//  $group_id = $row_GroupList['ID'];
//mysql_select_db($database_JPT, $JPT);
//$query_PartcipantsList = sprintf("SELECT FirstName, Surname, Username, EmailAddress, HouseNumber, Address1, Address2, City, PostCode, Country, Phone, Mobile, ReferedBy, PaymentMethod, ChargeAmount, PaidAmount, GroupID, Status, UserType FROM UserInfo WHERE Status='A' AND UserType=2 AND GroupID=%s", GetSQLValueString($group_id, "int"));
//$PartcipantsList = mysql_query($query_PartcipantsList, $JPT) or die(mysql_error());
//$row_PartcipantsList = mysql_fetch_assoc($PartcipantsList);
//$totalRows_PartcipantsList = mysql_num_rows($PartcipantsList);
//$particpant_FirstName = $row_PartcipantsList['FirstName'];

$colname_UserType = "-1";
if (isset($row_UserDetails['UserType'])) {
$colname_UserType = $row_UserDetails['UserType'];
}
    if ($colname_UserType != 1) {
        header('location: linklist.php');
  exit;
    }



?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>List of Partcipants</title>
</head>

<body>
  <?php do { 

  ?>
<table border="1">
  <tr>
    <td>Group ID</td>
    <td>Group Name</td>
    <td>Start Date</td>
    <td>Notes</td>
  </tr>
    <tr>
      <td><?php echo $row_GroupList['ID']; ?></td>
      <td><?php echo $row_GroupList['GroupName']; ?></td>
      <td><?php echo $row_GroupList['StartDate']; ?></td>
      <td><?php echo $row_GroupList['Notes']; ?></td>
    </tr>

</table>    
<p/>

<table border="1">
  <tr>
    <td width="175px">Name</td>
    <td>Email Address</td>
    <td>Address</td>
    <td>Country</td>
    <td>Phone</td>
    <td>Refered By</td>
    <td>Payment Method</td>
    <td>Charge Amount</td>
    <td width="50px">Paid Amount</td>
    <td width="50px">Group</td>
  </tr>
  <?php do { 

  $group_id_new = $row_GroupList['ID'];
mysql_select_db($database_JPT, $JPT);
$query_PartcipantsList = sprintf("SELECT FirstName, Surname, Username, EmailAddress, HouseNumber, Address1, Address2, City, PostCode, Country, Phone, Mobile, ReferedBy, PaymentMethod, ChargeAmount, PaidAmount, GroupID, Status, UserType FROM UserInfo WHERE Status='A' AND UserType=2 AND GroupID=%s", GetSQLValueString($group_id_new, "int"));
$PartcipantsList = mysql_query($query_PartcipantsList, $JPT) or die(mysql_error());
$row_PartcipantsList = mysql_fetch_assoc($PartcipantsList);
$totalRows_PartcipantsList = mysql_num_rows($PartcipantsList);


  //take in all fields in a variable with a short name
  $pt_Add1 = $row_PartcipantsList['HouseNumber'];
  $pt_Add2 = $row_PartcipantsList['Address1'];
  $pt_Add3 = $row_PartcipantsList['Address2'];
  $pt_Add4 = $row_PartcipantsList['City'];
  $pt_Add5 = $row_PartcipantsList['PostCode'];

  //create variables with commas to concatenate the address
  $pt_result_Add1 = ($pt_Add1 != "") ? $pt_Add1 . " " : "";
  $pt_result_Add2 = ($pt_Add2 != "") ? $pt_Add2 . ", " : "";
  $pt_result_Add3 = ($pt_Add3 != "") ? $pt_Add3 . ", " : "";
  $pt_result_Add4 = ($pt_Add4 != "") ? $pt_Add4 . ", " : "";
  $pt_result_Add5 = ($pt_Add5 != "") ? $pt_Add5 : "";

  //concatenate the address
  $pt_Add_Conc = $pt_result_Add1 . $pt_result_Add2 . $pt_result_Add3 . $pt_result_Add4. $pt_result_Add5 ;

  ?>
    <tr>
      <td><?php echo $row_PartcipantsList['FirstName'] . " " . $row_PartcipantsList['Surname']; ?></td>
      <td><?php echo $row_PartcipantsList['EmailAddress']; ?></td>
      <td><?php echo $pt_Add_Conc; ?></td>
      <td><?php echo $row_PartcipantsList['Country']; ?></td>
      <td><?php echo $row_PartcipantsList['Phone']; ?></td>
      <td><?php echo $row_PartcipantsList['ReferedBy']; ?></td>
      <td><?php echo $row_PartcipantsList['PaymentMethod']; ?></td>
      <td><?php echo $row_PartcipantsList['ChargeAmount']; ?></td>
      <td><?php echo $row_PartcipantsList['PaidAmount']; ?></td>
      <td><?php echo $row_PartcipantsList['GroupID']; ?></td>
    </tr>

    <?php } while ($row_PartcipantsList = mysql_fetch_assoc($PartcipantsList)); ?>
  <?php } while ($row_GroupList = mysql_fetch_assoc($GroupList)); ?>

</table>



</body>
</html>
<?php
mysql_free_result($UserDetails);

mysql_free_result($GroupList);

mysql_free_result($PartcipantsList);
?>

Answer 1

根据您使用的是 mysqli 还是 mysql，有几种方法可以处理这个问题

foreach(mysqli)

$query = $db->query("SELECT * FROM.... WHERE ....");

$data = $db->get($query);

foreach($data as $key => $value) {

     echo '<li>'.$value["courseid"].'</li><br/><ul>';

     $courseid = $value["courseid"];

     $query2 = $db->query("SELECT * FROM `userinfo` WHERE `courseid` = '$courseid'");

     $data2 = $db->get($query2);

     if($data2 != null) {

          foreach($data2 as $key => $value) {

               echo '<li>'.$value["username"].'</li>';
          }
          echo '</ul>';
     }
}