php - 如何在 PHP/MYSQL 中获取 DISTINCT ID 值？

Question

我有 PHP 代码从数据库中获取 2 个表“tbl_products”和“tbl_productphotos”数据。我想获取 DISTINCT 数据。我的查询和代码是:

<?php
$sql = "select DISTINCT t1.*,t2.photo 
        from tbl_products t1, tbl_productphotos t2 
        where t1.ProductID = t2.ProductID 
        and t1.tilename = 'Glass Tile' 
        and t1.ProductID = t2.ProductID 
        ORDER BY ProductID DESC";
$qex=mysql_query($sql); 
while($row=mysql_fetch_array($qex))
{
?> 

    <input type="hidden" value="<?php echo $row['ProductID'];?>">
    <li class="col-md-3 col-sm-6 col-xs-12 isotope-item websites" style="float: left">
    <div class="portfolio-item">
        <span class="thumb-info thumb-info-lighten thumb-info-bottom-info thumb-info-centered-icons">
        <span class="thumb-info-wrapper">

        <img src="images/products/thumbs/<?php echo $row['photo']; ?>"  class="img-responsive" alt="" height="200px" width="200px">
        <span class="thumb-info-title">
        <span class="thumb-info-inner"><?php echo $row['Title'];?></span>
        <span class="thumb-info-type"><?php echo substr($row['Description'],0 ,37);?></span>
        </span>
        <span class="thumb-info-action">
        <a href="glass-tile-details.php?ProductID=<?php echo base64_encode($row['ProductID']); ?> &quot;&amp;&lt;&nbsp; &Atilde;&Chi;!&upsih;&upsilon; &quot;&amp;&gt;&euro; &nbsp;&Aacute;&Acirc;&quot;&amp; &acute;&AElig;&copy;&quot;&amp;&amp; &quot;&Eacute;&piv;&Pi; 1&quot;&amp;&thetasym;&Theta; &quot;&amp;"><span class="thumb-info-action-icon thumb-info-action-icon-primary"><i class="fa fa-link"></i></span>
        </a> 

        <a href="images/products/thumbs/<?php echo $row['photo']; ?>" style="width: 500px; height: 500px;" class="lightbox-portfolio">
        <span class="thumb-info-action-icon thumb-info-action-icon-light"><i class="fa fa-search-plus"></i></span>
        </a>
        </span>
        </span>
        </span>
    </div>
    </li>
<?php
}
?>

表是:tbl_products

ProductID      Title     Description     titlename
35             ABC       tile           Glass Tile
36             XYZ       tile 2          Glass Tile

第二个表:“tbl_productphotos”

id      ProductID          photo        Each Image store in 3 folders
5         35            image.jpeg   (in folder big,medium,thumbs)
6         35            image.jpeg   (in folder big,medium,thumbs)
7         35            image.jpeg   (in folder big,medium,thumbs)
8         36            image.jpeg   (in folder big,medium,thumbs)
9         36            image.jpeg   (in folder big,medium,thumbs)
10        36            image.jpeg   (in folder big,medium,thumbs)

我们可以选择多个图像，我选择 3 个图像，并且所有 3 个图像都存储在所有三个文件夹中。所以我只想获取一张带有一个 ProductID 的详细信息的图像，但我在网页上显示了所有 3 张图像。

请检查并回复并帮助我。感谢和问候安基特

Answer 1

尝试避免选择 * 。(并且您有两次条件 t1.ProductID = t2.ProductID )

$sql = "select DISTINCT 
            t1.ProductID, 
            t1.Title,
            t1.titlename,
            t1.Description,
            t2.photo 
        from tbl_products t1
        INNER JOIN  tbl_productphotos t2 on t1.ProductID = t2.ProductID 
        and t1.tilename = 'Glass Tile' 
        ORDER BY t1.ProductID DESC";