Update Alias of Several Articles in Joomla 3.x through database

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.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
User avatar
syhussaini
Joomla! Explorer
Joomla! Explorer
Posts: 451
Joined: Sat Jan 03, 2009 2:20 pm
Location: Karimnagar-TS-India
Contact:

Update Alias of Several Articles in Joomla 3.x through database

Post by syhussaini » Sat May 23, 2020 11:18 pm

Hello Team,

I am working on a site https://dailypuzzleanswers.com where alias of 500+ articles went blank for unknown reasons. The same articles happen to lose the author as well and was not showing any Author (Author was set to blank). However, I was able to fix it as most of them were showing an Author with ID 43. I created a new user account and set the ID to 43 through phpMyAdmin, which took care of that problem.

However, I am still sitting with the blank aliases of 500+ articles. Can someone please help me with a quick way of handling this?

I don't want to hurt other articles instead set the title as the article alias wherever the alias is blank.

Any helpful advice will be much appreciated.
Best Regards | Syed H
https://jobwalkins.in - My Job Portal Built on Joomla
https://myknowledgeportal.com - My Knowledge Blog

 
User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38461
Joined: Sat Apr 05, 2008 9:58 pm

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Webdongle » Sun May 24, 2020 1:44 am

More importantly would be to find out what went wrong, would it not?

Probably quicker to ask the host to roll back the server
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

User avatar
syhussaini
Joomla! Explorer
Joomla! Explorer
Posts: 451
Joined: Sat Jan 03, 2009 2:20 pm
Location: Karimnagar-TS-India
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by syhussaini » Sun May 24, 2020 3:04 am

Thank you for your helpful response. It would be a nightmare to rollback as the site is a heavy traffic daily content-based site. Hence, I am trying to fix the issue and then probably remove any and all unwanted plugins to keep it light and safe.

Please advice.
Best Regards | Syed H
https://jobwalkins.in - My Job Portal Built on Joomla
https://myknowledgeportal.com - My Knowledge Blog

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38461
Joined: Sat Apr 05, 2008 9:58 pm

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Webdongle » Sun May 24, 2020 8:55 am

The quickest way is to reinstate a backup. Other than that, open up each Article and save it. To populate the Author field you could use sql.

If you run the fpa and post the results here we could help locate any problems.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

User avatar
sozzled
Joomla! Exemplar
Joomla! Exemplar
Posts: 9039
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by sozzled » Sun May 24, 2020 9:31 am

The quickest way to fix this is to write a SQL statement (to take the article title, convert it to lowercase, strip out the spaces/convert them to hypens, etc, and use the to as the new alias). This "quickest" way may take you some time to test but it should work. The guys at Stackoverflow could probably do something like this for you in a couple of hours. In fact, I could probably do something like this myself in a couple of hours but I'm not interested in trying.

What I'm about to write isn't—strictly speaking—SQL but the statement would probably look like something like this:

Code: Select all

UPDATE xyz_content WHERE alias IS NULL
SET alias=replace(replace(lower (title),' ','-'),"'",'');
These things (like loss of alias and wrong author) usually occur if a previous J! migration was badly run or didn't run to completion. The FPA report will probably not tell us anything important and would be, in my opinion a wasteful distraction. 8)
https://www.kuneze.com/blog
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?” :)

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 26835
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Per Yngve Berg » Sun May 24, 2020 2:28 pm

The upgrade logs are in /administrator/logs/joomla_update.php.

User avatar
syhussaini
Joomla! Explorer
Joomla! Explorer
Posts: 451
Joined: Sat Jan 03, 2009 2:20 pm
Location: Karimnagar-TS-India
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by syhussaini » Sun May 24, 2020 2:32 pm

Dear Sozzled,

You made a strong point, let me take a neat backup and put it on a local build and try, and maybe I can get lucky. I will certainly share my findings once I am done.

Thank you again
Best Regards | Syed H
https://jobwalkins.in - My Job Portal Built on Joomla
https://myknowledgeportal.com - My Knowledge Blog

User avatar
leolam
Joomla! Master
Joomla! Master
Posts: 20079
Joined: Mon Aug 29, 2005 10:17 am
Location: Netherlands/ UK/ S'pore/Jakarta/ North America
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by leolam » Mon May 25, 2020 3:28 pm

You are hosting with gws-host.com and you could ask them or the Joomla scripting support division gws-desk.com to write as small script to do this for you. It would not take more than 2 hours I am pretty sure

Mods: This is not self-promotion!

Leo 8)
Joomla's #1 Professional Services Provider:
#Joomla Professional Support: https://gws-desk.com -
#Joomla Specialized Hosting Solutions: https://gws-host.com -
#Joomla Webmaster Services: gws-webmaster.services
STAY SAFE!

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38461
Joined: Sat Apr 05, 2008 9:58 pm

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Webdongle » Mon May 25, 2020 7:31 pm

@sozzled's fix sounds feasible ... if it is then it would take less than 20 mins.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

User avatar
sozzled
Joomla! Exemplar
Joomla! Exemplar
Posts: 9039
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by sozzled » Mon May 25, 2020 8:10 pm

The fix is feasible but the example I've given is incomplete, is untested, and the syntax is not 100% correct.

Basically, an article's alias value is (by default) based on the article title but with a few restrictions.

Aliases cannot include spaces (which is why the example converts spaces to hyphens but, perhaps worse, my example would convert multiple spaces as multiple hyphens).

It's preferable to have the alias all in one case (lowercase).

Some special characters should be stripped (e.g. apostrophes, underscores, quotes, accented characters, currency symbols, etc.). For example, if you had an article with the following title

Code: Select all

"I'm in love with Joomla!", said George.
then a good alias for this would be

Code: Select all

im-in-love-with-joomla-said-george
In the example I gave earlier, I showed one method that strips apostrophes.

If someone was really interested in going the extra yards, you could investigate https://stackoverflow.com/questions/310 ... ter-in-sql.

Therefore, after running some "batch" process, some alias names may need extra refactoring. It's not perfect but I suspect people would only need to use this technique once in a website's lifetime. I haven't looked at the Database Repair tool to see checks for NULL aliases, whether it flags these as errors, or whether it's capable of repairing anything that's "broken" like that.
https://www.kuneze.com/blog
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?” :)

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 11919
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by brian » Tue May 26, 2020 11:38 am

Just one problem with that script. It doesnt check to see if the alias already exists and therefore can create duplicate alias. And no I dont know a way to resolve that as I need it too
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 26835
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Per Yngve Berg » Tue May 26, 2020 1:15 pm

A unique index on column will take care of it.

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 11919
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by brian » Tue May 26, 2020 8:18 pm

No, surely that will just prevent duplicate alias being created. It doesn't solve the problem of creating the unique alias
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38461
Joined: Sat Apr 05, 2008 9:58 pm

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Webdongle » Tue May 26, 2020 11:00 pm

You could run an if command? Check if it exists after creating the alias but before updating the field. The code should be in Joomla core somewhere ... after all it checks to see if an alias is already in place on saving an Article.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 11919
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by brian » Wed May 27, 2020 8:36 am

That is my point. The only way to do this correctly is with php. You cant just write a quick sql script
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38461
Joined: Sat Apr 05, 2008 9:58 pm

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Webdongle » Wed May 27, 2020 10:28 am

mysql has IF statement https://www.w3schools.com/sql/func_mysql_if.asp
It is also possible to check using mysql COUNT to determine if string exists https://www.w3schools.com/sql/func_mysql_count.asp

IF string = 0 UPDATE field
The commands will need nesting but it can be done using mysql.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 11919
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by brian » Wed May 27, 2020 11:43 am

If you think that will work - then great. I know it wont
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38461
Joined: Sat Apr 05, 2008 9:58 pm

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Webdongle » Wed May 27, 2020 1:02 pm

I know it will work because I did similar a while ago (if you dispute that then you are calling me a liar). Are you calling me a liar? **edit viewtopic.php?t=895124#p3331446 end edit** The problem for me is that it took days to research the correct syntax. I used similar to locate the ID of a new user then alter the User group field of the new user. As it was done to search and change the contents of a field then adding a mysql IF condition is just a matter of knowing the syntax.

The logic is
WHERE alias field is NULL
LOWER(str) of title field REPLACE spaces with underscores and SET @variable_name
NOT EXISTS @variable_name UPDATE alias field with @variable_name

The question is whether or not writing the mysql would be quicker than opening each Article and saving? Wonder how many seconds it would take to open an Article and click save .. perhaps about 2 seconds (empty alias filed auto populates on save). That would be 30 a minute. 300 in 10 minutes.

If the Created by needed to be edited then around 120 - 150 c. Articles could be changed in 10 mins.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 11919
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by brian » Wed May 27, 2020 3:00 pm

Thats all well and good but it will not address the situation I described of multiple items with the exact same name
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38461
Joined: Sat Apr 05, 2008 9:58 pm

Re: Update Alias of Several Articles in Joomla 3.x through database

Post by Webdongle » Wed May 27, 2020 5:57 pm

Oh sorry @brian thought that was obvious
REPEAT
{...statements...}
UNTIL condition
END REPEAT

Not sure if the condition can be WHEN the ID field = the number of records in the Table or if you would need to create a variable that incremented by 1 each loop. But methinks it will still be quicker to open the Article for edit then save than it would be to write the query.
http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein

 

Post Reply

Return to “Administration Joomla! 3.x”