返回唯一ID

Question

我需要从一个表中获取最后一个ID，基于其他表。

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

因此，这段代码运行良好，并返回所有数据，但我需要以这样的方式获得最后一个ID : title_id = UNIQUE & last

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

这是我的实际代码：

// 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();

注:此结果基于两个表-标题和链接，唯一的ID在表“title”(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在查询中的用途，所以不确定是否需要它。