Any mySQL experts?

[Jayy]

I have a slight problem that I'm unable to solve, as I don't know the query I would need to run in mySQL. Basically, I have moved a Joomla site over to a testing server whilst I upgrade the site and it has the HWDvideoshare plugin running. The problem is, on the old server, the site was installed to /joomla and now I've moved it to my testing server, it's in the root directory.

All the video thumbnails are looking for /joomla/hwdvideos/thumbs/ when I need them to be in /hwdvideos/thumbs/

You might think, why don't I just create that folder? If I do that, all the thumbs show up but it causes problems when uploading new videos.

I was thinking all I need to do is a run a mySQL query in phpmyadmin to change all the paths? I changed the first two (ID 12,13) manually and it's all working fine but there's 450 of them...

I've attached a screen shot of the database, does anyone know what query I can run to change every path in the 'thumbnail' table to reflect the right path without the /joomla/ ?

[angryjonny]

Dunno about MySQL but (assuming you need the "/" up front) in Oracle it would be:

update jos_hwdvidscategories set thumbnail = substr(thumbnail, 8)
where substr(thumbnail, 1, 7) = '/joomla'

That'll leave the ones that don't start with /joomla alone.

But it's Oracle. Not MySQL. So the answer to your question is "no", I guess.

Whatever you do, run a select first to see what the effect would be.

[elky]

I've not got any access to mySQL at the minute, but it should be something like:

UPDATE jos_hwdvidcategories
SET thumbnail = SUBSTR(thumbnail,11,100);

So effectively just deleting the first 11 characters of the thumbnail (100 is just a big number to ensure you capture the full file name)

Do a select first though to make sure you have the sub string set correctly.

SELECT SUBSTR(thumbnail,11,100) FROM jos_hwdvidcategories;

[supZ]

unfortunately i use mssql not mysql but you will be able to get that done by using a replace command and updating the record.

MS SQL:

[mjn51]

As with the other replies
SUBSTRING(str FROM pos FOR len)


this may help :-
https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring

[Jayy]

What's the difference between supZ and the other methods?

[angryjonny]

[Jayy]

Good stuff... I'm such a retard, I thought, "I'll backup the database now and run some of these queries". Started clicking on things in the database and nothing was working, 5 minutes later, I was clicking the screenshot I uploaded in this topic

[Jayy]

supZ, worked a treat! Saved me about 4 hours of manual labour there! Same to the others, thanks for the help, much appreciated.

[supZ]

np

yeh the other examples basically removed the beginning of the string, getting rid of the extra bit you didnt need (the /joomlia bit) whereas my example replaced the text within the string.

my example will work even if you want to replace/remove some text in the middle of the string. substring can work the same way but you need to define the start and end of the portion of the string you're changing rather than looking for an occurance of the part of the string you're looking for

and LMAO @ the screenshot
____________________
CBR954RR - Daily toy
CBR600RR - Trackbike

[Kickstart]

Hi

Would probably have been easiest to use substring_index:-

[Jayy]

supZ method worked perfectly, will bare these posts in mind next time I need to do this. I have a good knowledge of mySQL, been developing sites with content management and e-commerce for years but sometimes it goes over my head.

[Jayy]

I posted the same thing on the Joomla forums, it had 160 views in about half an hour and not one person replied.

BCF ftw