loop through database mysql php with multiple phonenumbers and email

Question

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 :)

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 07 '20 at 12:31
Thanks ,yea i know, but this is solely to get me to know how CRUD works I am already busy with : $stmt = $connection->prepare('INSERT INTO lid VALUES(?,?,?,?,?)'); $stmt->bind_param('nssss', null, $achternaam, $voornaam, $huisnummer, $postcode); So it wil be my next thing to do — Johan, Aug 07 '20 at 12:49

my_workbench · Accepted Answer · 2020-08-07T13:14:03.000

0

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.

edited Aug 07 '20 at 13:14

answered Aug 07 '20 at 11:16

my_workbench

300
3
8

Sorry :s ,its marked now. I am still learning (full stack developer) and I assumed it would unset and set itself again with each iteration in a loop but it’s getting a static or constant value instead? – Johan Aug 07 '20 at 11:48
can you please explain further – my_workbench Aug 07 '20 at 12:02
Sorry English is not my native language. What I meant is when I set the variable $tells in the subloop, I thought it would set itself with the new value with the next iteration(and loosing or unsetting its old value like $sr7), but instead it is keeping its value like a static or constant variable? The puzzling for me is, why $sr7 getting its new value but $tels is not and have to be unset afterwards? – Johan Aug 07 '20 at 12:33
I got you. Unlike variables, the array has keys like 0,1,2 and so on. In each iteration, the array index key will keep on adding within the array. E.g. On 1st iteration, $tels[0] and $tel[1] will have values for first user. On 2nd iteration,$tels[0] and $tel[1] will remain set with first user telephones and next two keys $tels[2] and $tels[3] will be set with second user telephones and so on. Do you got my point or should I edit my answer with an example ? – my_workbench Aug 07 '20 at 12:48
Wow, thanks if you could make a example i am getting it but not quite. – Johan Aug 07 '20 at 13:00
@Johan I have edited my answer with an explanation. Hope, it will make your understanding better. – my_workbench Aug 07 '20 at 13:15

loop through database mysql php with multiple phonenumbers and email

1 Answers1