how to update part of table feild usng mysql?

[keith23]

How to build a query that it goes changes part of value of a perticuler field in db with some thing else like :

http://localhost/

wimpy5recursive/mp3s/songname.mp3.
All song path initinally are stored as c:/wamp/www/wimpy5recursive/mp3s/songname.mp3

I have many mp3 info in db and all are saved like above and i want to change them all with

http://localhost/

wimpy5recursive/mp3s/songname.mp3 . The field name is filename i be happy if an expert show me how to do it .Thanks

 

[r937]

update yourtable
   set fieldname
     = replace(fieldname
              ,'c:/wamp/[URL unfurl="true"]www/'[/URL]
              ,'[URL unfurl="true"]http://localhost/')[/URL]
 where fieldname 
          like 'c:/wamp/www/%'

r937.com | rudy.ca

 

[keith23]

Many thanks to u . How about if i want to delte all records that one of the colums such as filename columd does not end with .mp3.The reason i want this becasue there many othe non mp3 related fiile infor and i want to delete them.Thanks

 

[r937]

delete
  from yourtable
 where right(filename,4) <> '.mp3'

r937.com | rudy.ca

 

[keith23]

Many thanks to u reply . Before executing this could u tell me what is 4 ? do i need to change it accordengly?

 

[r937]

4 = four

take a value like this --

c:/wamp/

http://www/wimpy5recursive/mp3s/songname.mp3

what are the four characters on the right?

r937.com | rudy.ca

 

[keith23]

r937 .The 4 char in the right are .mp3 !! It worked without chaning it. One more thing since folder names are part of filename for example the bold part is foler name:

http://localhost/wimpy5recursive/mp3s/

[/url]album1/song1.mp3

how i can wirte a query that outputs folder names for me .I have a huge list of mp3 in my db and i wrote a php page that have to query mysql to output all folder names with a link to pages that contain those songs.But i do not know how to query the db since foldr name is not part of filesname feild not a seperate feild. I be happy if u help me here.Thanks

 

[r937]

the folder name would be the second substring counting from right to left using the slash as the delimiter

correct?

select url
     , substring_index(
       substring_index(url,'/',-2)
                          ,'/',1) as folder
  from yourtable

r937.com | rudy.ca

 

[keith23]

r937 Many thanks to u . It worked but how to use distinct ? i put distinct infront of select but again it give me all urls in left and folder name for each songs on the right. Is there a way to to list the folders once? How i can list songs that are in root not in folders(/mp3/song.mp3) ?

I already have a query that list songs by album .I have huge list of mp3 in diffrent folders some of them are collections from diffrent artis and albums and some does not have good tags so i want create seperate page using a query that list folders for me and then link that page to another page that lists songs inside that folder. so how i i can write a query that lists all songs inside the perticulr folder name ?Thanks

 

[keith23]

oppss i fixed that distint problem by removing first url.

But now how i can use result of this query in another query that recives foldername value so it goes and list all those songs that same foldername is found in filesname?
for example there is a link name of Andy and once user click on that another query runs and lists all those songs that have andy az their filename

 

[keith23]

I fixed most of the problem .The only problem is that the query :
$query = "select DISTINCT substring_index(substring_index(filename,'/',-2) ,'/',1) as folder from tablename";

outputs the list of current folder that holds all wanted folder . In another word how to remove mp3s/ from result set?Furthermore,how i can write a query that lists mp3/songs only exculding the folders? Thanks