返回唯一ID

Question

我需要根据其他表获取表中最后一个ID。

更清楚，这是我的实际工作（测试）http://pepeok.com/plugins/combined2.php

所以这段代码工作做好，并返回所有的数据，但我需要以这种方式得到的只是最后的ID： 的title_id = UNIQUE & LAST

当然，在这种情况下，title_id是电影或tvShow，所以我想在最后一部电影中只有1个链接，或者在电视剧的最后一集中。

这是我的实际代码：

// Create connection 
$conn = new mysqli($servername, $username, $password, $dbname); 
// Check connection 
if ($conn->connect_error) { 
    die("Connection failed: " . $conn->connect_error); 
} 

$sql = "SELECT l.id, l.label, l.title_id, t.title, t.poster, l.season, l.episode, l.approved FROM links l JOIN titles t ON l.title_id = t.id WHERE approved = 1 order by id desc LIMIT 30 OFFSET 1"; 
$result = $last_id = $conn->query($sql); 

if ($result->num_rows > 0) 
{ 

    // output data of each row 
    while($row = $result->fetch_assoc()) { 
//THE CONTENT GO HERE ---- 
} else { 
    echo "0 results"; 
} 
$conn->close(); 

注： 这个结果是基于2个表 - 标题&链接，独有的ID是在桌子上“标题”（ID）和表链接（title_id的）

表结构 - 链接：

CREATE TABLE IF NOT EXISTS `links` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
    `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'embed', 
    `label` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `title_id` bigint(20) unsigned DEFAULT NULL, 
    `season` int(10) unsigned DEFAULT NULL, 
    `episode` int(10) unsigned DEFAULT NULL, 
    `reports` int(10) unsigned NOT NULL DEFAULT '0', 
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
    `temp_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `approved` tinyint(1) NOT NULL DEFAULT '1', 
    `positive_votes` int(11) NOT NULL DEFAULT '0', 
    `negative_votes` int(11) NOT NULL DEFAULT '0', 
    `quality` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SD', 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `links_url_unique` (`url`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=53281 ; 

---

个表结构标题：

CREATE TABLE IF NOT EXISTS `titles` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
    `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `type` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'movie', 
    `imdb_rating` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `tmdb_rating` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `mc_user_score` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `mc_critic_score` smallint(5) unsigned DEFAULT NULL, 
    `mc_num_of_votes` int(10) unsigned DEFAULT NULL, 
    `imdb_votes_num` bigint(20) unsigned DEFAULT NULL, 
    `release_date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `year` smallint(5) unsigned DEFAULT NULL, 
    `plot` text COLLATE utf8_unicode_ci, 
    `genre` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `tagline` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `poster` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `background` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `awards` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `runtime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `trailer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `budget` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `revenue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `views` bigint(20) NOT NULL DEFAULT '1', 
    `tmdb_popularity` float(50,2) unsigned DEFAULT NULL, 
    `imdb_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `tmdb_id` bigint(20) unsigned DEFAULT NULL, 
    `season_number` tinyint(3) unsigned DEFAULT NULL, 
    `fully_scraped` tinyint(3) unsigned NOT NULL DEFAULT '0', 
    `allow_update` tinyint(3) unsigned NOT NULL DEFAULT '1', 
    `featured` tinyint(3) unsigned NOT NULL DEFAULT '0', 
    `now_playing` tinyint(3) unsigned NOT NULL DEFAULT '0', 
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
    `temp_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `original_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `affiliate_link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
    `custom_field` text COLLATE utf8_unicode_ci, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `titles_imdb_id_unique` (`imdb_id`), 
    UNIQUE KEY `titles_tmdb_id_type_unique` (`tmdb_id`,`type`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2106587 ; 

---

非常感谢您的帮助。

Answer 1

尝试

SELECT 
l.id, l.label, l.title_id, t.title, t.poster, l.season, l.episode, l.approved 
FROM 
links l , titles t , (SELECT max(l.id) as 'thelastid' FROM links l) t2 
WHERE 
approved = 1 AND l.title_id = t.id AND t2.thelastid=l.id 

Answer 2

$sql = <<<'sql' 
    SELECT 
     DISTINCT l.id, 
     l.label, l.title_id, t.title, t.poster, l.season, 
     l.episode, l.approved 
    FROM 
     links l 
    JOIN titles t 
     ON l.title_id = t.id 
    WHERE approved = 1 
    order by 
     id DESC 
    LIMIT 1 
    OFFSET 1 
sql; 

应返回查询的最后一个不同的结果。不完全确定OFFSET适用于您的查询，因此不确定是否需要。