Duplicate results

Question

I have two tables

• product:{
  pid
  namesake
  value
  category
  stock
  Eston
  stocking
  }

• pictures{
  fid
  name
  product
  }

And I’m trying to make a query in the bank where I need to take everything that is in the product table all products, for this I’m using a function in PHP, but the problem is that each product has several photos, then at the time of displaying the products it displays the same products with each photo, but I wanted to display the product only once with only one of the photos, below the function code along with the search in the database

public static function website_produtos_home(){
        $pdo = db::pdo();
        $stmt = $pdo->prepare("SELECT p.*, f.fnome FROM produto AS p JOIN fotos AS f ON p.pid = f.produto");
        $stmt->execute();
        $total = $stmt->rowCount();
        if($total > 0){
            while ($dados = $stmt-> fetch(PDO::FETCH_ASSOC)){
                echo 
                "<div class='col-3'>
                    <ul class='grid'>
                        <li>
                            <a href='produto/{$dados['pid']}'>
                                <img src='{$dados['fnome']}' alt=''>
                            </a>
                        </li>
                        <li class='grid-descricao'>
                            <ul>
                                <a href=''><li class='grid-nome'>{$dados['pnome']}</li></a>
                                <li class='grid-preço'>Preço: R$ {$dados['valor']}</li>
                                <li class='grid-quantidade'>Tamanho P: {$dados['estoqueP']} unidades</li>
                                <li class='grid-quantidade'>Tamanho M: {$dados['estoqueM']} unidades</li>
                                <li class='grid-quantidade'>Tamanho G: {$dados['estoqueG']} unidades</li>

                            </ul>
                        </li>
                        <a href='produto/{$dados['pid']}'><li class='grid-detalhes'>Detalhes</li></a>
                    </ul>
                </div>";
            }
        }
    }

Answer 1

I created another field in the table "photos" called "main" with default value to 0, went to the record of the photos I wanted to mark and changed to 1, then changed the database search to:

SELECT p.*, f.fnome FROM produto AS p JOIN fotos AS f ON p.pid = f.produto WHERE f.principal = 1

Now it is displaying all products only once