fetch() is closing the mysqli connection

Question

I have a problem in my php that I need to include all the time the config.php (mysqli php connection script)

<?php

$root = $_SERVER['DOCUMENT_ROOT']; 
include("$root/config.php"); // INCLUDE CONFIG

$stmt = $mysqli_link->prepare("SELECT id, categoria FROM categoria order by rand() limit 10");
$stmt->execute();
$stmt->bind_result($id1, $categoria);


include("$root/config.php"); // NEED TO INCLUDE CONFIG AGAIN TO AVOID - Fatal error: Call to a member function bind_param()


while($stmt->fetch()) {

$stmt2 = $mysqli_link->prepare("SELECT id, categoria, cover, fotos, titulo, descricao, data FROM posts where categoria = ? order by id desc limit 1");
$stmt2->bind_param('i', $id1);
$stmt2->execute();
$stmt2->bind_result($id, $categoria, $cover, $pictures, $titulo, $descricao, $data);
$stmt2->fetch();

    echo"<li class=liside><img src=/$picture class=row><a href=/category/$categorialink>$categoria</a></li>";
}

?>

What am I doing wrong? I need first the $stmt to select categories, after that $stmt2 inside while to get things from that category on post table.

config.php

$hostname="localhost";
$titulo="config";
$user="root";
$pass="";
$bd="site";

$mysqli_link = new mysqli($hostname, $user, $pass, $bd);
$mysqli_link->set_charset("utf8");
ini_set('default_charset','utf8');

Why do you think you're getting Fatal when trying to call bind_param? Because I think that second include call have something to do with it. — al'ein, Aug 28 '15 at 17:38
maybe some function is closing $mysqli_link... I need to add config.php before while to avoid that error. — renata costa, Aug 28 '15 at 17:41
Can you show config.php? I was think on something [like this](http://stackoverflow.com/questions/21232784/is-it-possible-use-include-twice-in-a-php-page)... — al'ein, Aug 28 '15 at 17:42
What's the error you see if you remove the 2nd `include("$root/config.php")` call? Because the `$mysqli_link` is not being closed, there's probably just a SQL error. — gen_Eric, Aug 28 '15 at 17:42
@renatacosta: Do you mean "call to *undefined* member function"? Can you paste **exact** error? That message is missing a few words. — gen_Eric, Aug 28 '15 at 17:44
... of a non-object? It means your `$stmt2` isn't receving return from your prepare. — al'ein, Aug 28 '15 at 17:44
@renatacosta: it's unrelated to the question, but it would be way better if you make a single query, joining categoria and posts, instead of looping. — Marcovecchio, Aug 28 '15 at 17:44
Fatal error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\php-include\side.php on line 35 — renata costa, Aug 28 '15 at 17:45
P.S. You don't need to call `$stmt2 = $mysqli_link->prepare("SELECT ...");` in the loop. You can prepare it once outside the loop along with `$stmt2->bind_param` and `$stmt2->bind_result()`. Then in the loop, all you need is `execute` and `fetch`. — gen_Eric, Aug 28 '15 at 17:46
@renatacosta: That means `->prepare()` returned `false`. What does `var_dump($mysqli_link->error)` show you after declaring `$stmt2`? — gen_Eric, Aug 28 '15 at 17:47
var_dump show me: string(52) "Commands out of sync; you can't run this command now" is it right? — renata costa, Aug 28 '15 at 17:49
@renatacosta: Is what right? That's the ***actual*** error/issue here. — gen_Eric, Aug 28 '15 at 17:49
[There is a question talking about it.](http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now) — al'ein, Aug 28 '15 at 17:50
And seems to be related what @RocketHazmat told, you should loop over result and not execution. — al'ein, Aug 28 '15 at 17:51
@AedixRhinedale: What I said isn't really the solution, just a suggestion to optimize this. — gen_Eric, Aug 28 '15 at 17:51
@RocketHazmat as far as I understood, the subsequent exec() callings inside while loop is just what's causing this — al'ein, Aug 28 '15 at 17:52
As @AedixRhinedale said, I saw the other post about this and the solution appears to be to add $stmt->store_result() instead of the second config.php... — renata costa, Aug 28 '15 at 17:55
Well then @renatacosta you try it and tell us what you've got :) — al'ein, Aug 28 '15 at 17:56
@renatacosta: Yes, that should be the solution. Re-`including` your config.php "fixed" it by opening a 2nd MySQL connection for the 2nd query. No reason to need 2 MySQL connections. — gen_Eric, Aug 28 '15 at 17:57
@RocketHazmat thank you! and thank you all friends. the answer solution works nice! — renata costa, Aug 28 '15 at 18:04

score 1 · Accepted Answer · edited May 23 '17 at 10:26

The problem is that you have 2 queries running at the same time and MySQLi doesn't like that. Your include("$root/config.php"); "fixed" it because it opened a 2nd MySQL connection for the 2nd query to run.

This is not suggested, and the solution is that you need to "buffer" the results of the 1st query so that MySQL can run the other(s). The store_result() should fix this.

See this question for more info: Commands out of sync; you can't run this command now

Also, as a suggestion, you only need to prepare() a query once. You can re-use it.

So, try this:

<?php

$root = $_SERVER['DOCUMENT_ROOT']; 
include("$root/config.php"); // INCLUDE CONFIG

$stmt = $mysqli_link->prepare("SELECT id, categoria FROM categoria order by rand() limit 10");
$stmt->execute();

$stmt->bind_result($id1, $categoria);
$stmt->store_result();  // Added to buffer result set

// You can re-use the same prepared statment in a loop
$stmt2 = $mysqli_link->prepare("SELECT id, categoria, cover, fotos, titulo, descricao, data FROM posts where categoria = ? order by id desc limit 1");
$stmt2->bind_param('i', $id1);
$stmt2->bind_result($id, $categoria, $cover, $pictures, $titulo, $descricao, $data);

while($stmt->fetch()){
    $stmt2->execute();
    $stmt2->fetch();

    echo"<li class=liside><img src=/$picture class=row><a href=/category/$categorialink>$categoria</a></li>";
}

// You should close your statements when you're done with them
$stmt->free_result();
$stmt->close();
$stmt2->close();

?>

P.S. There's no real need to run 2 (or more) queries here. You can get what you want with just one. You just need to JOIN the tables.

Note: This might not work 100%, but it's just a suggestion of how to clean up this code and run less queries.

<?php

$root = $_SERVER['DOCUMENT_ROOT']; 
include("$root/config.php"); // INCLUDE CONFIG

$stmt = $mysqli_link->prepare("
    SELECT categoria.id, categoria.categoria,
        posts.id, posts.categoria, posts.cover, posts.fotos, posts.titulo, posts.descricao, posts.data
    FROM categoria
    JOIN posts ON posts.categoria = categoria.id
    GROUP BY categoria.id
    ORDER BY rand()
    LIMIT 10
");

$stmt->execute();
$stmt->bind_result($id1, $categoria, $id, $posts_categoria, $cover, $pictures, $titulo, $descricao, $data);

while($stmt->fetch()){
    echo"<li class=liside><img src=/$picture class=row><a href=/category/$categorialink>$categoria</a></li>";
}


$stmt->close();
?>

oh, this works nice! the only problem is the select is getting the same category many times... — renata costa, Aug 28 '15 at 18:08
i think group by posts.categoria solve this! thank you friend — renata costa, Aug 28 '15 at 18:09
@renatacosta: Yeah, I wasn't too sure about the `JOIN`ed query. That was more of a suggestion than a correct answer. Glad you got it working :-) — gen_Eric, Aug 28 '15 at 18:37

fetch() is closing the mysqli connection

1 Answers1