How to output part of feild using mysql

[tonyck]

Hi all. i got a huge list of mp3 that they are in diffrent folders. I put all the songs infor in mysql database in a table called files. I want a query db that output foldernames for me .The folder name is part of feiled called filename in files table. The value of filename is like this stored in db :

http://localhost/wimpy5recursive/mp3s/album1/song1.mp3
http://localhost/wimpy5recursive/mp3s/album2/song4.mp3
where the bold part is the foldername. i want to print folder name.Note:mp3s is the root folder for mp3s.The whole point is to list all folders name and link to songs inside those folders.

Furthermore, i want a query all feilds info for songs in each given folder.What i mean is that for each folder it lists all songs info iside that folder.I want to be able to tell the query what folder i want to list its song.Thanks

 

[Cache-man]

For extracting the foldername from the FILENAME field in the FILES table, you could try a string function like this

SELECT MID(filename, 39)
FROM files

I'm fairly new to SQL myself, but have just completed a module studying it at university, so the query may or may not work.
The above query shouldl cut of the first 38 characters of the filename, and start displaying the filename from the 39th character, not sure how to cut out the filename after the folder though, although if you use this query:

 SELECT MID(filename, 39, 8)
 FROM files

then this should return the first 8 characters of the folder, after the 39th character. Of course this is fine if all your folder names are exactly 8 characters in length, but I presume you folder names are of many varying lengths.
You can obviously change the '39' and '8' to whatever you choose.

For the last query you mention, I think I can help you more on this query if you let me know exactly all the fields you want to display.

 

[omrsafetyo]

cache-man - using that method, its not very dynamic anyway - if he were to have a folder called "album10" it would cut out the 0.

tony - I'm not entirely sure what you are looking for here..

Are you saying you want to query the DB for the album name, and then list all of the files in it?
Or do you want to query the DB and list all the album (folder) names?
Or something entirely different?