Video Resume Point Explaination
#1
Hi,

Been using kodi for a few years and i sync all my devices via a MySQL database hosted on a NAS.
I was wondering how does Kodi track and record the resume point of a Video?

Reason i am asking is, i would like to create a web page that i can pull all my files up and where the watch point is up to.
Reply
#2
The video database table 'bookmark' will contain two fields for each paused video file:
timeInSeconds and totalTimeInSeconds.
Reply
#3
(2020-04-24, 23:04)Klojum Wrote: The video database table 'bookmark' will contain two fields for each paused video file:
timeInSeconds and totalTimeInSeconds.
Thanks for your reply.

Are you able to explain to me, which columns speak to what tables to link it to the file name of the video i am playing ... if that makes seance.
Reply
#4
(2020-04-24, 23:11)Fallen Soull Wrote: which columns speak to what tables to link it to the file name of the video i am playing

Not filename, but the file's id.
Image
Reply
#5
(2020-04-24, 23:17)Klojum Wrote:
(2020-04-24, 23:11)Fallen Soull Wrote: which columns speak to what tables to link it to the file name of the video i am playing

Not filename, but the file's id.
Image 
Thank you once again. This is good and all makes sense now.

Appreciate your time as well Smile
Reply
#6
Just another question you maybe able to assist me on.
When i run the following query it will only list information that has a bookmark time:
sql:
SELECT fl.strFilename AS "Video", fl.playCount AS "Play Count", fl.lastPlayed AS "Last Played", bm.timeInSeconds /60 AS "Current Play Time"
FROM Kodi116.files fl
JOIN Kodi116.bookmark bm ON fl.idFile = bm.idFile

How can i get this to display the information of the videos that do not have a book mark and return the value of either NULL or N\A[/font]
Reply
#7
(2020-04-25, 00:25)Fallen Soull Wrote: Reason i am asking is, i would like to create a web page that i can pull all my files up and where the watch point is up to.
..
How can i get this to display the information of the videos that do not have a book mark and return the value of either NULL or N\A

Do you want ALL videos in a list, including the bookmarked ones, or just the bookmarks?
All of them means you'll need an OUTER JOIN.
Reply
#8
(2020-04-25, 05:59)Klojum Wrote:
(2020-04-25, 00:25)Fallen Soull Wrote: Reason i am asking is, i would like to create a web page that i can pull all my files up and where the watch point is up to.
..
How can i get this to display the information of the videos that do not have a book mark and return the value of either NULL or N\A

Do you want ALL videos in a list, including the bookmarked ones, or just the bookmarks?
All of them means you'll need an OUTER JOIN.  
Thanks again for your reply. Sorry i didn't see it earlier, as i didn't get the email.

Either way, i worked it out. OUTER JOIN doesn't seem to work with my server and i used LEFT JOIN which does the same thing.

Just encase some one else is interested. I will past my code here:

sql:
SELECT fl.strFilename, IFNULL(fl.playCount,'-') AS playCount, fl.lastPlayed, ROUND(IFNULL(bm.timeInSeconds, 0) /60, 2 ) AS "progress"
FROM files fl
LEFT JOIN bookmark bm ON fl.idFile = bm.idFile
WHERE fl.strFilename NOT LIKE "episode%"
ORDER BY fl.strFilename

Which results in this view:
Image

And to make it more appealing, i created a website that reads this information and comes out like this.

Image

Unfortunately photo bucket now watermarks stuff -_- shows how old i am Tongue
Reply
#9
LEFT..RIGHT... I always mix those two up. In SQL queries, that is. Smile

If you want to share pics/images/photos here, use the i-button in the editor menu.
It will auto-upload the pics to imgur.com and create a link. Plain and simple.

For diverse types of code, use the </> syntax-tag in the editor menu bar.
Then enter the type of syntax if you want color coding.

And I removed your Kodi116 database name, it's not the default name.
Reply
#10
(2020-04-25, 12:10)Klojum Wrote: LEFT..RIGHT... I always mix those two up. In SQL queries, that is. Smile

If you want to share pics/images/photos here, use the i-button in the editor menu.
It will auto-upload the pics to imgur.com and create a link. Plain and simple.

For diverse types of code, use the </> syntax-tag in the editor menu bar.
Then enter the type of syntax if you want color coding.

And I removed your Kodi116 database name, it's not the default name.
Thanks. I will keep this all in mind for any other posts Smile
Reply
#11
Right now, you will get both movies and tvshows mixed in that listing.
You'll have to smarten up your query if you want only movies or tvshows.
Reply
#12
(2020-04-25, 13:36)Klojum Wrote: Right now, you will get both movies and tvshows mixed in that listing.
You'll have to smarten up your query if you want only movies or tvshows.

Due to how I name my shows episodes. I am able to eliminate them from populating my movies list by using the where clause.
sql:
WHERE strFilename NOT LIKE "episode%"

However, this will make it most likely more complicated when i go to do the list up for shows.
Image
Reply

Logout Mark Read Team Forum Stats Members Help
Video Resume Point Explaination0