How to get SQL data to PHP array

Question

I kinda messed up the title, but i will try to explain my problem. i have a html page called leden.html and have a PHP script on it which gets data from my database and creates a table on the html page. Now the part where i get stuck is showing if a member is online and if someone is online the $sql1= "ja" else $sql1= "nee", but i messed up somewhere because when two people are online, the last person who came online shows online and the first dude goes back to "nee". Here is the code, i think something goes wrong at the array part.

    <?php
    $conn = mysqli_connect("******", "******", "******", "******");
     // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    $sql = "SELECT id, username, email FROM register";
    $sessie_username = "SELECT username FROM sessie";   
    $result = $conn->query($sql);
    $result1 = $conn->query($sessie_username);
    $row1 = $result1->fetch_assoc();
    $nameninsessie = array($row1["username"]);

    if ($result->num_rows > 0) {
       // output data of each row
       while($row = $result->fetch_assoc()) {
          if (in_array($row["username"], $nameninsessie)) {
             $sql1 = "Ja";
          } else {
             $sql1 = "Nee";
          }
          echo "<tr><td>" . $row["id"]. "</td><td>" . $row["username"] . "</td> 
                <td>". $row["email"]. "</td><td>" . $sql1 . "</td></tr>";
       }
       echo "</table>";

    } else { echo "0 resultaten"; }

    $conn->close();
    ?>

do I understand correctly that you keep all online users in table 'sessie'? — Jeff
– Jeff, Commented Sep 13, 2018 at 19:38
I suppose you want that: while( $row1 = $result1->fetch_assoc()) { $nameninsessie[] = $row1["username"]; } — Jeff
– Jeff, Commented Sep 13, 2018 at 19:39
Yes all online users are in table 'sessie' and do i replace this code while($row = $result->fetch_assoc()) { if (in_array($row["username"], $nameninsessie)) with hile( $row1 = $result1->fetch_assoc()) { $nameninsessie[] = $row1["username"]; } ? — Gerrit
– Gerrit, Commented Sep 13, 2018 at 19:44

RiggsFolly · Accepted Answer · 2018-09-13 20:15:09Z

1

You are only getting ONE of the logged in users from the sessie_username query. And also building the array of logged in users incorrectly. See below

<?php
    $conn = mysqli_connect("******", "******", "******", "******");
     // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    $sql = "SELECT id, username, email FROM register";
    $sessie_username = "SELECT username FROM sessie";   
    $result = $conn->query($sql);
    $result1 = $conn->query($sessie_username);

    // initialise the array 
    $nameninsessie = array();

    // loop over all logged in users
    while ( $row1 = $result1->fetch_assoc() ) {
        // add thir names to an array
        $nameninsessie[] = $row1["username"];
    }

    if ($result->num_rows > 0) {
       // output data of each row
       while($row = $result->fetch_assoc()) {
          if (in_array($row["username"], $nameninsessie)) {
             $sql1 = "Ja";
          } else {
             $sql1 = "Nee";
          }
          echo "<tr><td>" . $row["id"]. "</td><td>" . $row["username"] . "</td> 
                <td>". $row["email"]. "</td><td>" . $sql1 . "</td></tr>";
       }
       echo "</table>";

    } else { echo "0 resultaten"; }

    $conn->close();
    ?>

edited Sep 13, 2018 at 20:15

answered Sep 13, 2018 at 19:45

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Justin Pearce Over a year ago

The reason being that fetch_assoc() only fetches ONE row at a time. Alternatively, fetch_all() would bring back all records at once.

RiggsFolly Over a year ago

@JustinPearce Yes that would also be an option, I had not instantly thought of

Gerrit Over a year ago

i love you it works and i understand the problem now, but now when theres nobody logged in and i go to the leden.html there are 7 of these errors : "Warning: in_array() expects parameter 2 to be array, null given on line 98""once i login they are gone. EDIT $nameninsessie = array(); this fixed it

billa-code Over a year ago

You have to initialize the array. If not that error will be there.

RiggsFolly Over a year ago

As @Credoz says, init the array first before starting the loop. I changed the answer accordingly

billa-code · Accepted Answer · 2018-09-13 20:02:52Z

There is a problem in your data fetching. See the returning array using print_rcommand. You only get the first value of the sql query. So try this. Furthermore you have to initialize the array if not when there is 0 results for the mysql query it will give an error.

<?php
$conn = mysqli_connect("localhost", "root", "*****", "*****");
 // Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$sql = "SELECT id, username, email FROM register";
$sessie_username = "SELECT username FROM sessie";   
$result = $conn->query($sql);
$result1 = $conn->query($sessie_username);
$nameninsessie = array();
$i=0;
while($row1 = $result1->fetch_assoc()) {
  $nameninsessie[$i] = $row1["username"];
  $i++;
}
print_r($nameninsessie); //thi is to chek the array when problem solved please comment this line
if ($result->num_rows > 0) {
   // output data of each row
   while($row = $result->fetch_assoc()) {
      if (in_array($row["username"], $nameninsessie)) {
         $sql1 = "Ja";
      } else {
         $sql1 = "Nee";
      }
      echo "<tr><td>" . $row["id"]. "</td><td> " . $row["username"] . "</td> 
            <td>". $row["email"]. "</td><td> " . $sql1 . "<br></td></tr>";
   }
   echo "</table>";

} else { echo "0 resultaten"; }

$conn->close();
?>

Collectives™ on Stack Overflow

How to get SQL data to PHP array

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related