P.Davide P.Davide - 5 months ago 21
MySQL Question

two while loop fetching data from two queries only show one result

I have this code that I use to fetch data from a database:

function show_volanti($data){

$con = $data; // PASSO CONNESSIONE
$id = 1; // 1 VOLANTE
$visibile = 1; // VARIABILE DI VISIBILITA'

$rows1 = array(); // PREPARO ARRAY 1 PER ID ARTICOLI VOLANTE
$rows2 = array(); // PREPARO ARRAY 2 PER LE FOTO VOLANTI

$id_articoli = ''; // RIFERIMENTO ARTICOLI PER SECONDA QUERY GALLERIA
$g = ''; // RIFERIMENTO ASSOCIAZIONE GALLERY VIEWER
//$rif_id = ''; // RIF_ID SE OK DA CANCELLARE


$query1 = "SELECT articoli.id AS id_articoli,
articoli.titolo,
articoli.descrizione
FROM articoli
WHERE articoli.genere1 = ?
AND articoli.visibile = ?";

$query2 = "SELECT galleria.id AS id_galleria,
galleria.foto,
galleria.rif_id
FROM galleria
WHERE galleria.rif_id = ?";

$stmt = mysqli_stmt_init($con); // INIZIALIZZO LA CONNESSIONE

mysqli_stmt_prepare($stmt,$query1);

mysqli_stmt_bind_param($stmt,'ii',$id,$visibile); // LEGO I PARAMETRI

mysqli_stmt_execute($stmt); // ESEGUO LA QUERY

mysqli_stmt_bind_result($stmt,
$rows1['id_articoli'],
$rows1['titolo'],
$rows1['descrizione']); // CREO RIFERIMENTO PER GALLERIA NEL VIEWER

$html = "";
$html .= "<div class='container'>";

while (mysqli_stmt_fetch($stmt)){

$id_articoli = $rows1['id_articoli'];


$html .= " <div class='row'>";
$html .= " <div class='col-sm-12'>";
$html .= " <div class='panel panel-default'>";
$html .= " <div class='panel-body'>";


$html .= " <div class='col'>";
$html .= " <div class='panel panel-default'>";
$html .= " <div class='panel-heading'><b>$rows1[titolo]</b></div>";
$html .= " <div class='panel-body'>";
$html .= " <div class='row'>";
$html .= " <div class='class_p'>$rows1[descrizione]</div>";
$html .= " <div> <!-- end first row -->";

$html .= " <div class='class_container clearfix'>";


mysqli_stmt_prepare($stmt,$query2);

mysqli_stmt_bind_param($stmt,'i',$id_articoli); // LEGO I PARAMETRI

mysqli_stmt_execute($stmt); // ESEGUO LA QUERY

mysqli_stmt_bind_result($stmt,
$rows2['id_galleria'],
$rows2['foto'],
$rows2['rif_id']);

while(mysqli_stmt_fetch($stmt)){

$g = '';
$g .= "g";
$g .= $rows2['rif_id'];

$html .= "<div class='thumbnail col-sm-2'>";
$html .= "<div class='class_img'>";
$html .= "<a href='$rows2[foto]' data-toggle='lightbox' data-gallery='$g' >";
$html .= "<img src='$rows2[foto]' class='img-fluid'>";
$html .= "</a>";
$html .= "</div> <!-- end class_img -->";
$html .= "</div> <!-- end thumbnail col-sm-2- -->";
}

$html .= "</div> <!-- end class_container -->";

$html .= "</div> <!-- end panel body -->";
$html .= "</div> <!-- end panel panel-default -->";
$html .= "</div> <!-- end col -->";
$html .= "</div> <!-- end panel-body -->";
$html .= "</div> <!-- end panel panel-default -->";
$html .= "</div> <!-- end col-sm-12 -->";
$html .= "</div> <!-- end row -->";
}

mysqli_stmt_close($stmt); // CHIUDO LO STATEMENT
mysqli_close($con); // CHIUDO CONNESSIONE
return $html;
}


The code partially works because it show the result but it only show one result and inside the database i have more results to show so it doesn't work as it should.. May you help me to find the error?

Answer Source

It seems you're reusing the same variables for both external and internal loop. For example, you're using $stmt for both of them. I'm not sure if that's why you're getting only one result (as it may depend on the amount of results you received from each of them), but I would look into that.

As a debug tip, I would remove all code relevant to the external loop and first print out the results of the external ones properly. Once you have that working, start adding the internal loop code and make sure it works. Do not reuse the same variables though.