SQL Query to remove Full Article Image

Need help with the Administration of your Joomla! 3.x site? This is the spot for you.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Post Reply
DJBenz
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 156
Joined: Tue Jan 20, 2009 8:34 am
Contact:

SQL Query to remove Full Article Image

Post by DJBenz » Fri Apr 20, 2018 11:58 am

I have a large number of articles where I want to remove the Full Article Image. In the database, the table column that stores both the Intro Image and the Full Article Image reads thus:

Code: Select all

{"image_intro":"[INTRO-IMAGE-URL]","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"[FULL-ARTICLE-IMAGE-URL]","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""}
EDIT: What SQL query do I need to run in PHPMyAdmin to search the images column and remove [FULL-ARTICLE-IMAGE-URL] where it exists?
Last edited by DJBenz on Fri Apr 20, 2018 1:02 pm, edited 1 time in total.

SharkyKZ
Joomla! Ace
Joomla! Ace
Posts: 1267
Joined: Fri Jul 05, 2013 10:35 am
Location: Unknown

Re: SQL Query to remove Full Article Image

Post by SharkyKZ » Fri Apr 20, 2018 12:57 pm

Image data is stored as JSON. You need to decode it, set image to an empty string and then encoded it again:
$db = JFactory::getDbo();
$query = $db->getQuery(true)
->select(array('id','images'))
->from($db->qn('#__content'));
$db->setQuery($query);
$articles = $db->loadObjectList();

foreach($articles as $article)
{
$images = json_decode($article->images);
$images->image_fulltext = '';
$article->images = json_encode($images);
$db->updateObject('#__content', $article, 'id');
}

DJBenz
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 156
Joined: Tue Jan 20, 2009 8:34 am
Contact:

Re: SQL Query to remove Full Article Image

Post by DJBenz » Fri Apr 20, 2018 1:01 pm

SharkyKZ wrote:Image data is stored as JSON. You need to decode it, set image to an empty string and then encoded it again
Apologies, this is way beyond my skillset. I'm looking for an SQL query I can run in PHPMyAdmin to find and remove the Full Article Image (I didn't make this clear in my OP, sorry).

DJBenz
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 156
Joined: Tue Jan 20, 2009 8:34 am
Contact:

Re: SQL Query to remove Full Article Image

Post by DJBenz » Fri Apr 27, 2018 9:54 am

Anyone? It's getting really tedious manually removing each one. :(

SharkyKZ
Joomla! Ace
Joomla! Ace
Posts: 1267
Joined: Fri Jul 05, 2013 10:35 am
Location: Unknown

Re: SQL Query to remove Full Article Image

Post by SharkyKZ » Fri May 04, 2018 1:23 pm

Create a PHP file in your Joomla directory and paste this code:

Code: Select all

<?php
if (!defined('_JEXEC')) {
	define('_JEXEC', 1);
	define('JPATH_BASE', realpath(__DIR__));
	require_once JPATH_BASE .'/includes/defines.php';
	require_once JPATH_BASE .'/includes/framework.php';
}
After this paste the code from my previous comment.

Open the PHP file in your browser and the code will be executed. This will set value to empty string in every instance of image_fulltext.

DJBenz
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 156
Joined: Tue Jan 20, 2009 8:34 am
Contact:

Re: SQL Query to remove Full Article Image

Post by DJBenz » Fri May 04, 2018 1:33 pm

SharkyKZ wrote:Create a PHP file in your Joomla directory and paste this code:

<snip>
After this paste the code from my previous comment.

Open the PHP file in your browser and the code will be executed. This will set value to empty string in every instance of image_fulltext.
Thank you so much, I'll give it a shot later on.


Post Reply

Return to “Administration Joomla! 3.x”