[mythtv-users] [PATCH] Mythweb -- Mythmusic playlist functionality AND Request for SQL help

[Christopher Flynn]

On Wed, 27 Oct 2004 11:55:36 -0400, Ryan Steffes <rbsteffes at gmail.com> wrote:
> http://www.afferentsys.com/~rbsteffes/patch-mythmusic.bz2
> 
> This patch adds to the functionality I've been working on to add
> playlist manipulation to mythweb.  Previously added was the ability to
> create/rename/add to/remove from a playlist.  I've added reordering
> playlists.

Sounds interesting, I'll have to check it out.
 
> That brings me to the second part of the request.  If anyone out there
> is particularly good at SQL I'm looking for a way to sort a select
> statement against a known string.  

SELECT * FROM <tablename> where field='known string'
ie: select intid, artist from musicmetadata where artist='Led Zeppelin'; 
you can of course replace "Led Zeppelin" with your known text.
Programatically you can use placeholders (typically a question mark ?)
and then execute the statement. Unfortunately I'm not that familiar
with php.

>The reason for this is because the
> way mythweb gets track names is efficient from a query standpoint, it
> gets them in the order of the intid key for the tracks in the db not
> the order they are listed in the playlist.  It's because it retrieves
> as where intid is in ( " playlist ") essentially.  Is there a way I
> can have it sort against the playlist before returning the rows?
> That'd make playlist reordering look a lot better.

Well I'm not a SQL expert but I looked at the music database tables
today and personally I would have done things a little differently.
Instead of songlist I would have used something like songid and made
that correspond to musicmetadata.intid. Then you could join the two
tables and sort it using SQL statements.
ie. something like:
 select musicmetadata.artist, musicmetadata.filename,
musicmetadata.intid, musicplaylist.playlistid from musicmetadata,
musicplaylist where musicmetadata.intid=musicplaylist.songlist AND
playlistid='1' order by artist;

you could then order the playlist however you want and let mysql take
care of that for you. There may be a way to go through each value in
the songlist field but I don't know how to do that.

> Right now, while it works, it doesn't appear to because the tracks
> show up in database order, not playlist order.  If there isn't a good
> way, there are work around fixes but they'd require more active
> sorting and could get ugly fast.

I think you may want to try and unload a little more of your code onto
the database instead of trying to code it yourself... the database is
great at storing and manipulating data, no need to reinvent the wheel.
Unless I missed what you are trying to do in the first place in that
case just ignore me.

-- 
You must be the change you wish to see in the world. - Gandhi