0

I have this code and I need to pass a variable in a IN clause in order to catch all the corresponding records from database. The problem is that it returns only the first record while there are more than one.. the problem is in the second query:

// FIRST QUERY
$query0 = "SELECT articoli.id
            FROM articoli
            WHERE articoli.visibile = ?
            AND articoli.genere2 = ?
            ORDER BY articoli.id ASC";

$stmt0 = mysqli_stmt_init($con);
mysqli_stmt_prepare($stmt0,$query0);
mysqli_stmt_bind_param($stmt0,'ii',$visibile,$id);
mysqli_stmt_bind_result($stmt0,$rows0['id']);
mysqli_stmt_execute($stmt0);

while (mysqli_stmt_fetch($stmt0)){
    $array_id[] = $rows0['id'];
}

$array_id = implode(',', $array_id);

print_r($array_id);

echo gettype($array_id)."<br />";

$immagini = array();

// SECOND QUERY:
$query1 = "SELECT articoli.id AS id_articoli,
          articoli.titolo,
          articoli.descrizione,
          galleria.id AS id_galleria,
          group_concat(galleria.foto) as immagini
          FROM articoli
          LEFT JOIN galleria
          ON articoli.id = galleria.rif_id
          WHERE FIND_IN_SET(articoli.id,?) // => Initially it was 'WHERE articoli.id IN ?'
          AND articoli.visibile = ?
          GROUP BY articoli.id
          ORDER BY articoli.id";

$stmt1 = mysqli_stmt_init($con);
mysqli_stmt_prepare($stmt1,$query1);
mysqli_stmt_bind_param($stmt1,'ii',$array_id,$visibile);
// INIZIALIZZO LA CONNESSION
$stmt1 = mysqli_stmt_init($con);

// PREPARARE QUERY
mysqli_stmt_prepare($stmt1,$query1);

// LEGO I PARAMETRI
mysqli_stmt_bind_param($stmt1,'ii',$array_id,$visibile);

mysqli_stmt_bind_result($stmt1,$rows1['id_articoli'],
                        $rows1['titolo'],
                        $rows1['descrizione'],
                        $rows1['id_galleria'],
                        $rows1['immagini']);
// ESEGUO LA QUERY
mysqli_stmt_execute($stmt1);                                                            

while (mysqli_stmt_fetch($stmt1)){
    $html .= "<p>$rows1[id_articoli]</p>";
    $html .= "<p>$rows1[titolo]</p>";
    $html .= "<p>$rows1[descrizione]</p>";
    $html .= "<p>$rows1[id_galleria]</p>";
    $html .= "<p>$rows1[immagini]</p>";
    //$immagini = explode(',', $rows1['immagini']);
    //$html .= "<p>$immagini[0]</p>";
}

return $html;

mysqli_stmt_close($stmt1); 

in the code above, with the first query I have a list of id that I put into an array and then i implode the array to have a string. Then, using print_r() i can see all the records. With echo gettype() it tells me that the variable is a string. Then i use the variable in the IN clause but it always returns me only the first record.. Then googling I discovered the FIND_IN_SET command and I changed the code and tried it but it doesn't change. It always return me only the first record.. How can I solve this problem? I have to use a string or I can pass an array to the query and it is better to use the IN clause or the FIND_IN_SET clause?

4
  • Why don't you just join the two queries? Commented Jul 12, 2017 at 20:56
  • Actually, why do you even need two queries? Just put WHERE articoli.visiblile = ? AND articoloi.genere2 = ? into the second query. Commented Jul 12, 2017 at 20:58
  • I didn't think about it... it was very simple... ok I try it and then i tell you :-) Commented Jul 12, 2017 at 21:01
  • Thanks! it was very simple and definitly I need some rest... thanks!, if you post it like a solution i vote it Commented Jul 12, 2017 at 21:09

1 Answer 1

1

Get rid of the first query, and just use those parameters in the second query, without needing FIND_IN_SET to match the IDs.

$query1 = "SELECT articoli.id AS id_articoli,
      articoli.titolo,
      articoli.descrizione,
      galleria.id AS id_galleria,
      group_concat(galleria.foto) as immagini
      FROM articoli
      LEFT JOIN galleria
      ON articoli.id = galleria.rif_id
      WHERE articoli.visibile = ?
        AND articoli.genere2 = ?
      GROUP BY articoli.id
      ORDER BY articoli.id";
$stmt1 = mysqli_stmt_init($con);
mysqli_stmt_prepare($stmt1,$query1);
mysqli_stmt_bind_param($stmt1,'ii',$visibile, $id);
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.