Joomla Templates Club 406,261 posts

[arjwad Friend arjwadJoin date: June 2008Posts: 24Downloads:0Uploads:2 Thanks: 10 Thanked: 1 times in 1 posts]

#145522

Hi,

I need to insert a <Read More> tag in 2400 articles on my site – basically break up all articles into `introtext` and `fulltext` at the 100th character mark (non code).

Can someone please help with suggestions on how to do this using SQL. I am guessing a mass update from the phpmyadmin will be a lot quicker than manually doing it in all articles.

A couple of thoughts I have so far

A)

1. Transfer all data from `introtext` to `fulltext`
2. Move first 100 characters from `fulltext` to `introtext`

B) – Transfer data from `introtext` to `fulltext` from the first 100 characters onwards

Would appreciate any help with cracking the SQL. Haven’t played with the language since 2001 🙂

Context – am upgrading from Teline II to Teline III, and teline II auto broke content in section and category display. Will look through the backend files of teline II to review how they did it as well, however any hints in the right direction would be appreciated.

Kind regards

Arjun

[arjwad Friend arjwadJoin date: June 2008Posts: 24Downloads:0Uploads:2 Thanks: 10 Thanked: 1 times in 1 posts]

#321716

This is what I have so far

UPDATE `jos_content` SET `fulltext`= `introtext` WHERE `id`= 1943;

UPDATE `jos_content` SET `introtext`= CONCAT( LEFT( `introtext` , 100 ) ) WHERE `id` =1943

Any suggestions how I can now remove the first 100 characters from the `fulltext` leaving the rest intact…

Anyone with cleaner, easier methods please also share…this is the first approach I thought of experimenting with.

[gray Friend grayJoin date: October 2009Posts: 957Downloads:0Uploads:17 Thanks: 28 Thanked: 292 times in 230 posts]

#321720

I can propose only with PHP
[php]
$intro = SUBSTR($article, 0, 100);
$end = STRRPOS($intro, ‘</p>’);
$article = SUBSTR($article, 0, $end) . ‘<hr id=”system-readmore” />’ . SUBSTR($article, $end);
[/php]

1 user says Thank You to gray for this useful post

1. arjwad

[arjwad Friend arjwadJoin date: June 2008Posts: 24Downloads:0Uploads:2 Thanks: 10 Thanked: 1 times in 1 posts]

#321724

Many thanks for your response, but I do not have access to the server to run php commands, they reside with the host – godaddy.com

Also, interestingly there is another concern, what if an image tag or any other piece of code comes within the first 100 characters…something I hadn’t anticipated earlier.

[gray Friend grayJoin date: October 2009Posts: 957Downloads:0Uploads:17 Thanks: 28 Thanked: 292 times in 230 posts]

#321728

If you have Joomla installed you can manage to run PHP code through a component, plugin etc.

As regards image tags, you are right. It’s one more proof that only PHP could help in this case.

strip_tags could be used for temp “clearning” of the code from <a> tags to count first 100 characters of the text.

1 user says Thank You to gray for this useful post

1. arjwad

[arjwad Friend arjwadJoin date: June 2008Posts: 24Downloads:0Uploads:2 Thanks: 10 Thanked: 1 times in 1 posts]

#321799

Hi,

Thanks again, I went through the extensions gallery on joomla.org, and couldn’t locate anything that was rated very high by users.

Could I trouble you once again to recommend something.

Thanks

Arjun

[axiauk Friend axiaukJoin date: October 2009Posts: 19Downloads:0Uploads:0 Thanks: 3 Thanked: 5 times in 1 posts]

#321806

Hi Arjun,
If you are looking pure MySQL query I just tried this to import my all old articles from 1.0. I am posting 2 different options which I considered.

1)

UPDATE jos_content SET
introtext = ((SELECT SUBSTRING_INDEX(jos_content.fulltext,'<p>’,2)),
jos_content.fulltext = ((SELECT REPLACE(jos_content.fulltext,introtext,”));

2)

UPDATE jos_content SET
introtext = ((SELECT SUBSTRING(jos_content.fulltext,1,200)),
jos_content.fulltext = ((SELECT REPLACE(jos_content.fulltext,introtext,”));

The first will find 2nd <p> tag in article and copy data upto that tag into intro text and replace that same text with ”(empty) in fulltext to prevent repeat.

The second query will fetch first 200 characters and do the same for fulltext.

I hope that will help.

1 user says Thank You to axiauk for this useful post

1. arjwad

[arjwad Friend arjwadJoin date: June 2008Posts: 24Downloads:0Uploads:2 Thanks: 10 Thanked: 1 times in 1 posts]

#323142

For others who wish to do this in future. Pls note the steps:

UPDATE `jos_content` SET `fulltext`= `introtext`;

UPDATE jos_content SET introtext = (SELECT SUBSTRING_INDEX(jos_content.fulltext,'<p>’,2));

UPDATE jos_content SET jos_content.fulltext = (SELECT REPLACE(jos_content.fulltext,jos_content.introtext,”));

[Author]

Posts