php - 在 PHP 中填充表格

Question

我的数据库中有这样一个表:

IdUser | Name | Surname | 

还有另一个具有以下结构的表

IdPN | IdUser | PhoneNumber

这张表的PK是IdPN，所以每个User可以有多个Phone number。

现在我从第一个表中获取数据以在我的 HTML 页面中填充表。我想要这样的东西:

name | Surname| numbers  |
_____|________|__________|
John | Doe    | 21212121 |
     |        | 23424324 |
_____|________|__________|
Frank|Johnson | 23222111 |
     |        | 23747347 |
     |        | 36363636 |
_____|________|__________|

所以第三列可以有多个数字。

当每个用户只有一个号码时，我使用的代码 php 如下:

<?php
      $connectiondb->set_charset("utf8");
      $query= "SELECT name, surname, phonenumber
      FROM users";
        if ($stmt = $connectiondb->prepare($query)) {
          $stmt->execute();

      $stmt-bind_result($nome,$cognome,$phonenumber);
            while($stmt->fetch()){
      echo '<tr>';
      echo '<td>'.$nome.'</td>';
      echo '<td>'.$cognome.'</td>';
      echo '<td>'.$phonenumber.'</td>';
      echo '</tr>';

}
$stmt->close();
?>

我必须如何更改我的代码才能为表格(用户)的每一行显示多个数字？

Answer 1

您需要在输出到前端之前准备您的记录集。

<?php
$connectiondb->set_charset("utf8");
$query= "SELECT users.IdUser, users.Name, users.Surname, phones.PhoneNumber
         FROM users
         LEFT JOIN phones ON (users.IdUser = phones.IdUser);";
if ($stmt = $connectiondb->prepare($query)) {
    $stmt->execute();

    $stmt->bind_result($id, $nome, $cognome, $phonenumber);

    /*
      Recordset will be 
      |1|Jim|Bowen|07235876583
      |1|Jim|Bowen|07734952651
      |2|Jim|Davidson|07275493085
      |2|Jim|Davidson|07197649387
      |2|Jim|Davidson|07030190594       

     */
    $result = array();
    while($stmt->fetch())
    {
        if(!isset($result[$id]))
        {
            // save any previous person in $result
            if(is_array($temp)) $result[$id] = $temp; 

            // start a fresh person
            $temp = array();

            // first time to come accross this person
            $temp['id'] = $id;
            $temp['name'] = $nome;
            $temp['surname'] = $cognome;
            $temp['phonenumbers'] = array($phonenumber);
        }
        else
        {
            // if we've already come across this person, only store his/her next phonenumber in the array
            $temp['phonenumbers'][] = $phonenumber
        }
    }

    /* the $result array will now look thus
    array (size=2)
      0 => 
        array (size=4)
          0 => string '1' (length=1)
          1 => string 'Jim' (length=3)
          2 => string 'Bowen' (length=5)
          3 => 
            array (size=2)
              0 => string '07235876583' (length=11)
              1 => string '07734952651' (length=11)
      1 => 
        array (size=4)
          0 => string '2' (length=1)
          1 => string 'Jim' (length=3)
          2 => string 'Davidson' (length=8)
          3 => 
            array (size=3)
              0 => string '07275493085' (length=11)
              1 => string '07197649387' (length=11)
              2 => string '07030190594' (length=11)
    */
$stmt->close();
}

你现在可以像这样回显:

foreach($result as $row)
    echo '<tr><td>'.$row['name'].'</td><td>'.$row['surname'].'</td><td>'.implode(', ', $row['phonenumber'].'</td></tr>';