CODEWITHSUNDEEP
phpmysqlsqldatabasephpmyadmin
aladin2222
aladin2222

Reputation: 127

Return unique ID

I need to get just the last ID from a table, based on others tables.

To be more clear, this is my actual work (test) http://pepeok.com/plugins/combined2.php

So this code work well, and return all data, but I need to get just the last ID in this way: title_id = UNIQUE & LAST

Of course, in this case, the title_id is the movie, or the tvShow, so I want to have just 1 link, at the last movie, or at the last episode for a TV show.

This is my actual code:

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

NOTES: This results are based on 2 tables - titles & links, The UNIQUE ID is on the table "titles" (id) and in the table links (title_id)

Table structure - links:

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 ;

Table structure titles:

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 ;

Many thanks for your help.

Upvotes: 0

Views: 114

Answers (2)

Rodney Salcedo
Rodney Salcedo

Reputation: 1254

Try

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

Upvotes: 1

Martyn Shutt
Martyn Shutt

Reputation: 1706

$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;

Should return the last distinct result from the query. Not entirely sure what the OFFSET is for in your query, so not sure if that's needed.

Upvotes: 0

Related Questions