loop through database mysql php with multiple phonenumbers and email

I am trying to loop trough a data base and get the correct data to each user. But I have multiple phone and mails per user. When I try to loop, I can only get one (correct data/user) number for each user, When I try to subloop, I only get the two first numbers from the database. So the first user have correct phone and mail data the rest of users have phone and mail data from first user. I have no idea what to do from here ☹.

<?php
/**
 * @author Johan Fuchs
 * @copyright 2020
 */
//LEDEN.PHP
//login.php========================================================
  require_once 'login.php';
  $conn = new mysqli($hostname, $username, $password, $database);
  if ($conn->connect_error) die("Fatal Error");
//begin html gedeelte==============================================  
  echo <<<_END
<html>
  <head>
  <title>display test</title>
  <link href="" rel="stylesheet" type="text/css">
  </head>
<body>
 <h2>display test</h2>
</body>
</html>
_END;
//display leden===========================================================================================================================================================================
      
      $query  = "SELECT * FROM lid";
      $result = $conn->query($query);
      if (!$result) die ("Database access failed");

      $rows = $result->num_rows;

      for ($j = 0 ; $j < $rows ; ++$j)
      {
      $row = $result->fetch_array(MYSQLI_NUM);

      $rs0 = htmlspecialchars($row[0]);
      $rs1 = htmlspecialchars($row[1]);
      $rs2 = htmlspecialchars($row[2]);
      $rs3 = htmlspecialchars($row[3]);
      $rs4 = htmlspecialchars($row[4]);
         
//get info telefoonnummer lid======================================
     
      $subquery  = "SELECT * FROM telefoonnummer WHERE lidnummer='$row[0]'";
      $subresult = $conn->query($subquery);
      if (!$subresult) die ("Database access failed");
    
      $subrows = $subresult->num_rows;
        
      $t = 0;
    
      while ($t < $subrows)
      {
      $subrow = $subresult->fetch_array(MYSQLI_NUM);
    
      $rs7 = htmlspecialchars($subrow[1]);
    
      $tels[] = $rs7;
      $t++;    
      }
    
      $tel1s = $tels[0];
      $tel2s = $tels[1];
      
      
//get info email lid================================================
  
      $subquery  = "SELECT * FROM email WHERE lidnummer='$row[0]'";
      $subresult = $conn->query($subquery);
      if (!$subresult) die ("Database access failed");
    
      $subrows = $subresult->num_rows;
    
      $m = 0;
     
      while ($m < $subrows)
    
     {
     $subrow = $subresult->fetch_array(MYSQLI_NUM);
    
     $rs8 = htmlspecialchars($subrow[1]);
    
     $mails[] = $rs8;
     $m++;
     }
    
     $mail1s = $mails[0];
     $mail2s = $mails[1]; 
     
//get info  postcode lid============================================
     $subquery  = "SELECT * FROM postcode WHERE postcode='$row[4]'";
     $subresult = $conn->query($subquery);
     if (!$subresult) die ("Database access failed");
    
     $subrow = $subresult->fetch_array(MYSQLI_NUM);
    
     $ps4 = htmlspecialchars($subrow[0]);
     $rs6 = htmlspecialchars($subrow[1]);
     $rs5 = htmlspecialchars($subrow[2]);
     
//display data lid=================================================
    
  echo <<<_END
  =================================================================
  <pre>
  Lidnummer         :$rs0
  Voornaam          :$rs2
  Achternaam        :$rs1 
  Adres             :$rs5
  Huisnummer        :$rs3
  Postcode          :$rs4
  Woonplaats        :$rs6
  Telefoonnummer    :$rs7 :$tel1s :$tel2s
  Email             :$rs8 :$mail1s :$mail2s 
  </pre>
_END;
} 
?>

The result of the user data

display test
================================================================= 
  Lidnummer         :1
  Voornaam          :firstname01
  Achternaam        :surname01 
  Adres             :street01
  Huisnummer        :01
  Postcode          :1111AA
  Woonplaats        :city01
  Telefoonnummer    :0611111111 :0601010101 :0611111111
  Email             :test@mail11.com :test@mail01.com :test@mail11.com 
  
================================================================= 
  Lidnummer         :2
  Voornaam          :firstname02
  Achternaam        :surname02 
  Adres             :street02
  Huisnummer        :02
  Postcode          :2222bb
  Woonplaats        :city02
  Telefoonnummer    :0622222222 :0601010101 :0611111111
  Email             :test@mail22 :test@mail01.com :test@mail11.com 
  
================================================================= 
  Lidnummer         :3
  Voornaam          :firstname03
  Achternaam        :surename03 
  Adres             :street03
  Huisnummer        :03
  Postcode          :3333cc
  Woonplaats        :city03
  Telefoonnummer    :0633333333 :0601010101 :0611111111
  Email

Any clues how to fix this?

thanks in advance 🙂

Answer

You need to use unset() function on each iteration to unset the array keys for $tels and $mails arrays. If you do not, This is what going to happen:

1st Iteration

$tels[0] will have First User Mobile
$tels[1] will have First User Mobile

2nd Iteration

$tels[0] will have First User Mobile
$tels[1] will have First User Mobile
$tels[2] will have Second User Mobile
$tels[3] will have Second User Mobile

and so on and same for mails too

In the code, you are assigning only first 2 key values which would always contain details for the first user.

  $tel1s = $tels[0];
  $tel2s = $tels[1];

So the solution is to add unset() function at the bottom of sub-query for telephones and email.

while ($t < $subrows)
  {
  $subrow = $subresult->fetch_array(MYSQLI_NUM);

  $rs7 = htmlspecialchars($subrow[1]);

  $tels[] = $rs7;
  $t++;    
  }

  $tel1s = $tels[0];
  $tel2s = $tels[1]; 
  unset($tels);  /* This will unset the array $tels for next iteration */

This will make sure at each iteration, the $tels[0] and $tels[1] will be reassigned and you will get the required information.

Similarly, unset the $mails[] array too after assigning the values to variables $mail1s and $mail2s. This should solve your problem.

Advertisement

Answer