用PHP填充表格

Question

I have a table in my database like this:

IdUser | Name | Surname | 

And another table with the following strucutre

IdPN | IdUser | PhoneNumber

The PK of this table is IdPN, so each User can have more than one Phone number.

Now I'm getting the data from the first table for pupulating a table in my HTML page. I want something like this:

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

So the third colume can have more than one numbers.

The code php that I was using when I had just one number for each user, is the following:

<?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();
?>

How I have to change my code for showing multiple number for each row of the table (user)?

Answer 1

You need to prepare your recordset before being output to the front end.

<?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();
}

You can now echo out like this:

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