WHERE clause with PHP-MYSQL array

Question

I have the following code, but it’s not working and I can’t find the error. I need to display the 3 lines (123), which you find in $fetch['list_carac'].

<?php
include 'conect.php';

$sql = "SELECT list_carac FROM select_carac WHERE id_produto = 1";
$exec = $con->query( $sql ) or exit( $con->error );
$fetch = mysqli_fetch_assoc($exec); 
$fetch = str_replace( ",", "", $fetch);

echo($fetch['list_carac']);//aqui a saída é 123

foreach($fetch as $value){

$sql =  "SELECT nome_carac FROM carac WHERE id_carac = $value";

$exec = $con->query( $sql ) or exit( $con->error );

$row = mysqli_fetch_assoc($exec);

echo $row["nome_carac"];
}
?>

If I take the foreach and add the id manually it returns me the record that is in the field nome_carac. Otherwise no error , but only shows the 1st outputecho($fetch['list_carac']);//aqui a saída é 123.

Answer 1

If you are sure that in DB is always comma, just this:

$sql = 'SELECT nome_carac FROM carac WHERE id_carac IN ('.$lista.')';

Applied to your code:

<?php
    include 'conect.php';

    $sql = 'SELECT list_carac FROM select_carac WHERE id_produto = 1';
    $exec = $con->query( $sql ) or die( $con->error );
    $fetch = mysqli_fetch_assoc($exec); 

    $lista = mysqli_real_escape_string( $con, $fetch['list_carac'] );
    $sql = 'SELECT nome_carac FROM carac WHERE id_carac IN ( '.$lista.')';
    $exec = $con->query( $sql ) or exit( $con->error );

    while( $row = mysqli_fetch_assoc($exec) ){ 
       echo $row["nome_carac"];
    }
?>

Eventually, if the list is not only numerical, you need a small sanitization (with quotation marks):

    $caracteres = explode( ',', $fetch['list_carac'] );
    foreach( $caracteres as $caractere ) {
        $lista .= "'".mysqli_real_escape_string( $con, $caractere )."',";
    }
    $lista = rtrim( $lista, "," );