PHP SQL While loop not working

Question

I have got a code that should get all of the user's Favourites from favourites and then it should use that information to get the info from menus to display them as pictures.

All it should do is display the user's Favourites but at the moment it will only display one picture when there are many in their Favourites.

<?php
$con=mysqli_connect("localhost","UN","PW","DB");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$id=$_SESSION['user']['id'];
$result = mysqli_query($con,"SELECT * FROM favourites WHERE user='$id'");

while($row = mysqli_fetch_array($result)) {
    $code=$row['gamecode'];
    $con=mysqli_connect("localhost","UN","PW","DB");
    // Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $result = mysqli_query($con,"SELECT * FROM menus WHERE code='$code'");

    while($row = mysqli_fetch_array($result)) {
?>
<a href="<?php echo $row['link']; ?>">
<img src="<?php echo $row['picture']; ?>" alt="<?php echo $row['game']; ?>"      height="120" width="150" class="fade"></a>
<?php
    }
    mysqli_close($con);
}

mysqli_close($con);

?>

Answer 1

You're killing your query by reconnecting to the DB inside your loop

$con = mysqli_connect(...) // connection #1
$result = mysqli_query(...);
while($row = mysqli_fetch($result)) {
   $con = mysqli_connect(...); // connection #2

When you connect again, you kill the original connection, which kills your query.

Unless you need to connect twice with different credentials, there is NO need for a second connection. One single connection can handle multiple queries.

Incidentally, if you'd used a different connection handle variable, e.g.

$con = mysqli_connect(...);
    $othercon = mysqli_connect(...);

you wouldn't have had the problem. You CAN have multiple connections, but not using the same single variable.

Answer 2

You have two MySQL connections with the same variable names of $con as well as $result & $row. So I just change the variable names on the inside loop so they don’t conflict & all should work; $con_inside, $result_inside & $row_inside.

I also added or die(mysqli_error()); to your mysqli_query lines so errors can be returned if your query dies.

<?php
$con = mysqli_connect("localhost","UN","PW","DB");

// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$id = $_SESSION['user']['id'];

$result = mysqli_query($con, "SELECT * FROM favourites WHERE user='$id'") or die(mysqli_error());

while ($row = mysqli_fetch_array($result)) {

  $code = $row['gamecode'];

  $con_inside = mysqli_connect("localhost","UN","PW","DB");

  // Check connection
  if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

  $result_inside = mysqli_query($con_inside, "SELECT * FROM menus WHERE code='$code'") or die(mysqli_error());

  while($row_inside = mysqli_fetch_array($result_inside)) {
?>

  <a href="<?php echo $row_inside['link']; ?>">
  <img src="<?php echo $row_inside['picture']; ?>" alt="<?php echo $row_inside['game']; ?>" height="120" width="150" class="fade"></a>

<?php
  }

  mysqli_close($con_inside);

}

mysqli_close($con);

Also, here is a slightly reworked version of your code that should work better. I removed the inside DB connection from the loop & set it at the top of the script. The connection does not have to be reset on each loop. Also, I added lines using mysqli_stmt_bind_param which is a preferred way of using mysqli_* queries instead of setting strings. Also using mysqli_free_result to free up query memory on each loop. These are small things but they add up to better code.

<?php

// Main DB connection.
$con = mysqli_connect("localhost","UN","PW","DB") or die(mysqli_connect_error());

// Inside DB connection.
$con_inside = mysqli_connect("localhost","UN","PW","DB") or die(mysqli_connect_error());

// Set the $id variable.
$id = $_SESSION['user']['id'];

// Set the query string.
$query = "SELECT * FROM favourites WHERE user='$id'";

// Bind the values to the query.
mysqli_stmt_bind_param($query, 's', $id);

// Get the result.
$result = mysqli_query($con, $query) or die(mysqli_error());

// Roll through the results.
while ($row = mysqli_fetch_array($result)) {

  // Set the $code variable.
  $code = $row['gamecode'];

  // Set the query string.
  $query_inside = "SELECT * FROM menus WHERE code='$code'";

  // Bind the values to the query.
  mysqli_stmt_bind_param($query_inside, 's', $code);

  // Get the result.
  $result_inside = mysqli_query($con_inside, $query_inside) or die(mysqli_error());

  // Roll through the results.
  while($row_inside = mysqli_fetch_array($result_inside)) {
?>

  <a href="<?php echo $row_inside['link']; ?>">
  <img src="<?php echo $row_inside['picture']; ?>" alt="<?php echo $row_inside['game']; ?>" height="120" width="150" class="fade"></a>

<?php
  }

  // Free the result set.
  mysqli_free_result($result_inside);

  // Close the connection.
  mysqli_close($con_inside);

}

// Free the result set.
mysqli_free_result($result);

// Close the connection.
mysqli_close($con);

Answer 3

See if it works using different result variables and only one DB connection.

<?php
$con=mysqli_connect("localhost","UN","PW","DB");
// Check connection
if (mysqli_connect_errno()) {  echo "Failed to connect to MySQL: " . mysqli_connect_error(); }
$id = $_SESSION['user']['id'];
$result1 = mysqli_query($con,"SELECT * FROM favourites WHERE user='$id'");
while($row = mysqli_fetch_array($result1)) {
    $result2 = mysqli_query($con,"SELECT * FROM menus WHERE code='".$row['gamecode']."");
    while($row2 = mysqli_fetch_array($result2)) { ?>
<a href="<?php echo $row2['link']; ?>">
<img src="<?php echo $row2['picture']; ?>" alt="<?php echo $row2['game']; ?>"      height="120" width="150" class="fade"></a>
<?php }
}
mysqli_close($con);
?>