Closing a connection in a loop

Question

I have the following code:

/* Get the 10 latest posts from users you're following */
$stmt = $cxn->prepare('SELECT * FROM posts WHERE user_id IN (SELECT following_id FROM follows WHERE user_id = ?) ORDER BY datetime DESC LIMIT 15');
$stmt->bind_param('i', $user_id);
$stmt->execute();
$result = $stmt->get_result();

/* If a result exists, continue. */
if ($result->num_rows) {
    while ($row = $result->fetch_assoc()) {

        /* Get the user's username from their id */
        $stmt = $cxn->prepare('SELECT username FROM users WHERE user_id = ?');
        $stmt->bind_param('i', $row['user_id']);
        $stmt->execute();
        $stmt->bind_result($username);
        $stmt->fetch();
        $stmt->close(); // this is where I'm closing the connection
    }
}

In the third last line, you'll notice that I'm closing the connection in the while loop. The issue is that, if I remove that line, I get the error on that line.

Fatal error: Call to a member function bind_param() on a non-object

I'm guessing that closing the connection and then re-opening it again for the next element in the loop is not a good thing. So, how can I fix this? Why do I get this error when I remove the close connection line?

In the second iteration of the loop it causes the problem you mentioned as obvious. Close the statement only after you finished with it. — Lion, Nov 03 '13 at 23:02
Wouldn't it make sense to have a better query for this? Using joins perhaps? — KTastrophy, Nov 04 '13 at 00:00

Deepak · Answer 1 · 2013-11-03T23:57:28.967

You could try something like below:

/* Get the 10 latest posts from users you're following */
$stmt = $cxn->prepare('SELECT * FROM posts WHERE user_id IN (SELECT following_id FROM follows WHERE user_id = ?) ORDER BY datetime DESC LIMIT 15');
$stmt->bind_param('i', $user_id);
$stmt->execute();
$result = $stmt->get_result();

/* If a result exists, continue. */
if ($result->num_rows) {
  while ($row = $result->fetch_assoc()) {

    /* Get the user's username from their id */
    $stmt = $cxn->prepare('SELECT username FROM users WHERE user_id = ?');
    $userid = (int)$row['user_id'];
    $stmt->bind_param('i', $userid); // Forcing $row['user_id'] to int
    $stmt->execute();
    $stmt->bind_result($username);
    $stmt->fetch();
    //$stmt->close();  Don't close it here
  }
}
$stmt->close();

Another approach but little more time consuming is:

 /* Get the 10 latest posts from users you're following */
$stmt = $cxn->prepare('SELECT * FROM posts WHERE user_id IN (SELECT following_id FROM follows WHERE user_id = ?) ORDER BY datetime DESC LIMIT 15');
$stmt->bind_param('i', $user_id);
$stmt->execute();
$result = $stmt->get_result();

/* If a result exists, continue. */
if ($result->num_rows) {
  while ($row = $result->fetch_assoc()) {

    /* Get the user's username from their id */
    $stmt_loop = $cxn->prepare('SELECT username FROM users WHERE user_id = ?');
    $userid = (int)$row['user_id'];
    $stmt_loop->bind_param('i', $userid); // Forcing $row['user_id'] to int
    $stmt_loop->execute();
    $stmt_loop->bind_result($username);
    $stmt_loop->fetch();
    $stmt_loop->close();  //Close the local stmt here
  }
}
$stmt->close();

I've tried the first one you suggested, I still get the error. :/ — Burrows, Nov 03 '13 at 23:21
`Fatal error: Call to a member function bind_param() on a non-object on line $stmt->bind_param('i', $row['user_id']);` — Burrows, Nov 03 '13 at 23:34
Have a read on this http://stackoverflow.com/questions/6659882/call-to-a-member-function-bind-param-on-a-non-object-unable-to-solve-despite — Deepak, Nov 03 '13 at 23:48
I now get this error: `Fatal error: Cannot pass parameter 2 by reference` on that same line. — Burrows, Nov 03 '13 at 23:55
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40475/discussion-between-deepak-and-burrows) — Deepak, Nov 03 '13 at 23:57

score 0 · Answer 2 · answered Nov 03 '13 at 23:08

This is an example i've got on one of my Github repositories which tackles this issue:

$Query = $Database->prepare("FIRST QUERY");
$Query->execute();
$Query->bind_result($ID,$Password);
$Query->store_result();
   while ($Query->fetch()){

       $Secondary_Query = $Database->prepare("SECOND QUERY");
       $Secondary_Query->bind_param();
       $Secondary_Query->execute();
       $Secondary_Query->close();
   }
$Query->close();

Modify this to suit your working scenario, and you will have a successful running query

Closing a connection in a loop

2 Answers2

Related