SQL script to create intro image by parsing article text in Joomla 3.x

joomla-migrace-sql-scriptWhen I migrated one of my web site from Joomla 1.5 into Joomla 3.3 I found there is finally nice feature in Joomla 3.x called Images and Links, which is assigned image to article like thumbnail images in Wordpress. New themes know what to you in responsive templates and it works really nice. Well if you have assigned images. To not do it manully I created parsing SQL scripts, that will help you do it on one click.

 

 

 Script is for MySQL. Looks into content table, find first occurence of <img> tag and it's src value. This one saves into images field in proper state. It's sets as intro image and full text image. This way can template show it in category view and in article view as well.

Before use switch "#" to your table prefix in Joomla instance.

UPDATE `#_content` -- change # to correct Joomla table prefix
SET images = Concat('{"image_intro":"',
REPLACE( SUBSTR( `fulltext` , LOCATE( 'src="', `fulltext` , LOCATE( '<img', `fulltext` ) ) +5, LOCATE( '"', `fulltext` , LOCATE( 'src="', `fulltext` , LOCATE( '<img', `fulltext` ) ) +5 ) - LOCATE( 'src="', `fulltext` , LOCATE( '<img', `fulltext` ) ) -5 ) , '/', '\\/' ),
'","float_intro":"none","image_intro_alt":"","image_intro_caption":""',
',"image_fulltext":"',
REPLACE( SUBSTR( `fulltext` , LOCATE( 'src="', `fulltext` , LOCATE( '<img', `fulltext` ) ) +5, LOCATE( '"', `fulltext` , LOCATE( 'src="', `fulltext` , LOCATE( '<img', `fulltext` ) ) +5 ) - LOCATE( 'src="', `fulltext` , LOCATE( '<img', `fulltext` ) ) -5 ) , '/', '\\/' ),
'","float_fulltext":"none","image_fulltext_alt":"","image_fulltext_caption":""}')
WHERE images = '' AND LOCATE( '<img', `fulltext` ) > 0

It is simple SQL script based text string functions to find text pattern, it's character count in text and then used for substrings. As a first I tried to used some regular expressions, but this would be possible only with PHP combination, because MySQL can work with RegEx to compare pattern and return TRUE or FALSE, there is no option to return substring based on regular expression as far as I know. Any comments and tips how to do it better are welcome.

Author info
Author: Stanislav DubenWebsite: http://www.duben.org
About me
I am experienced database engineer with more than 12 years developing and optimization experience. I worked on many high level projects based on SQL servers. I am also photograper and owner of many internet projects.


Add comment

Security code
Refresh