Populating a table in 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

Change your SQL to include the IdUser column as follows:

SELECT users.IdUser, users.Name, users.Surname, phones.PhoneNumber
FROM users
LEFT JOIN phones ON (users.IdUser = phones.IdUser);

Then you can use the PHP to sort the phones into structured arrays and go from there:

$aUsers = array();
$aUserPhones = array();

while ($row = mysql_fetch_assoc($result)) {
    if (!isset($aUsers)) {
        $aUsers['UserId'] = array(
            'nom'       =>  $nom,
            'cognom'    =>  $cognome,
            'phones'    =>  array($phone),
        );
    }
    else {
        $aUsers[$userId]['phones'][] = $phone;
    }

}

foreach ($aUsers as $aUser) {
    echo '<tr>';
    echo '<td>'.$aUser['nome'].'</td>'
    echo '<td>'.$aUser['cognome'].'</td>';
    echo '<td>';
    echo implode('<br>', $aUser['phones']);
    echo '</td>';
    echo '</tr>';
}

Answer 2

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>';

Answer 3

I suggest using an ID instead of a Name.

Your data will come back like this:

name | Surname| numbers  |
_____|________|__________|
John | Doe    | 21212121 |
John | Doe    | 23424324 |
John | Doe    | ######## |
-------------------------- 

Edit: re-read the question, noticed your table structure

query will turn into:

            SELECT name, surname, phonenumber, phone_ct

            FROM   users as u 

                   join phone_numbers as ph
                      on ph.IdUser = u.IdUser 

                   join phone_numbers as ph
                      on ph.IdUser = u.IdUser 

                   join (select IdUser, 
                                count(IdPN) as phone_ct 
                         from phone_numbers
                         group by IdUser ) as ph_count   
                      on ph_count.IdUser = u.IdUser 

now data will turn into:

name | Surname| numbers  | phone_ct
_____|________|__________|___________
John | Doe    | 21212121 |        3 |
John | Doe    | 23424324 |        3 | 
John | Doe    | ######## |        3 | 
-------------------------------------

edit: updated code to maybe actually work (lol... sorry)

code will turn into:

<?php
      $connectiondb->set_charset("utf8");
      $query = "
            SELECT name, surname, phonenumber, phone_ct

            FROM   users as u 

                   join phone_numbers as ph
                      on ph.IdUser = u.IdUser 

                   join phone_numbers as ph
                      on ph.IdUser = u.IdUser 

                   join (select IdUser, 
                                count(IdPN) as phone_ct 
                         from phone_numbers
                         group by IdUser ) as ph_count   
                      on ph_count.IdUser = u.IdUser ";

      if ($stmt = $connectiondb->prepare($query)) 
      {
         $stmt->execute();  
         $stmt->bind_result($nome, $cognome, $phonenumber, $phone_ct);

         $row_ct = 0; 

         while($stmt->fetch())
         { 
            $row_ct++ 

            // do every row for every section
            echo '<tr>';

            // first row of this section 
            if ($row_ct==1)                    
            { 
               echo "<td rowspan=$phone_ct>".$nome.'</td>';
               echo "<td rowspan=$phone_ct>".$cognome.'</td>';
            }

            // do every row for every section
            echo '<td>'.$phonenumber.'</td>';    
            echo '</tr>';

            // end of this section 
            if ($row_ct==$phone_ct) {           
              $row_ct = 0; 
            }   
        }

        $stmt->close();

     }  // end if $stmt 

?>

Answer 4

Execute query like

SELECT A.name, A.surname,GROUP_CONCAT(phonenumbers) as phone_number
FROM TABLE1 A 
JOIN TABLE2 B ON A.iduser = B.iduser
GROUP BY A.iduser

From this query you will get phone number comma separated

Example : (1123132132,123123133)

Use explode to split

$get_number = explode(" ", $number);

Now you will get numbers in an array $get_number. Get the count of number by

$row_span = count($get_number); 

Now you will have the row span. Using foreach you can print the numbers in table. So Your td will be like

<td rowspan="<?php echo $row_span; ?"> 

I hope this will help you. If need any assistance, please let me know

Answer 5

If you have two tables;

IdUser | Name | Surname  
IdPN | IdUser | PhoneNumber

What I would do it use the first table as your "primary" table, and the second as a secondary. IdUser is going to be your PK in your primary, but IdUser in your secondary will be a foreign key to IdUser in the primary table (since these two pieces of information are the same and you really shouldn't be adding objects to the phone number table without a corresponding entry in the primary table.) You should be able to perform a query off of both tables to present the information you need (like, maybe, a full join). As an example ;

select a.IdUser, a.Name, b.Surname, b.IdPN, b.PhoneNumber from <primary table> as a, <secondary table> as b where a.IdUser=b.IdUser;

This is an example, so don't copy and paste it in, but the point is that if the relationship between the two tables is consistent, you should be able to perform a single query to get everything you need.

Answer 6

$sql = "SELECT PhoneNumber FROM PhoneNumbers WHERE IdUser = $user_id";
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
    echo $row['PhoneNumber'] . "<br />\n";
}

That block of code can be used to display a list of phone numbers for $user_id separated by line breaks. Placed inside the <td> tag of your phone number area will do the trick, as long as $user_id is defined.