通过

Question

来显示上传的图片，而不是从mysql群组中的第一张图片我上传的图片按照项目名称分组。

在第一页上，我显示了一个来自该组的一个图像的项目名称。

它显示从该组图像上传的第一张图像，并且我正在尝试显示上传的最后一张图像。

我的代码是：

$dbhostname="localhost"; 
$dbusername="root"; 
$dbpassword=""; 
$db = "main_name"; 
$dbh = new PDO("mysql:host=$dbhostname;dbname=$db", $dbusername, $dbpassword); 

foreach($dbh->query("SELECT *,COUNT(*) 
FROM images 
GROUP BY project_name ORDER BY img_timestamp DESC") as $row) { 
echo "Project Name: " . $row['project_name']; 
echo "<br>"; 
echo $row['imagelink']; 
} 

供您参考我的MySQL表为：

Table name: images 

id  project_name  imagelink  img_timestamp 
1  Travier    image1   (timestamp of upload) 
2  Travier    image2   (timestamp of upload) 
3  Travier    image3   (timestamp of upload) 
4  Travier    image4   (timestamp of upload) 
5  Collosion   image1   (timestamp of upload) 
6  Collosion   image2   (timestamp of upload) 
7  Collosion   image3   (timestamp of upload) 

帮助将不胜感激:)

Answer 1

您可以使用，一种在SUBQUERY和MAX：

SELECT id, project_name, COUNT(*), 
MAX(img_timestamp) AS img_timestamp, 
(SELECT imagelink FROM images img WHERE img.project_name = images.project_name ORDER BY img_timestamp DESC LIMIT 1) AS imagelink 
FROM images GROUP BY project_name; 

请记住，使用*将导致名称为img_timestamp & imagelink的两列会以另一种方式为时间戳和图像链接使用不同的别名。

Answer 2

尝试以下查询两个查询都工作正常。

SELECT * 
FROM images 
WHERE id IN (
    SELECT Max(id) 
    FROM images 
    GROUP BY project_name 
) 

OR 

SELECT * 
FROM images 
WHERE timestemp IN (
    SELECT Max(timestemp) 
    FROM images 
    GROUP BY project_name 
)