I hope you got some good usage out of my 1st installment of "Time-saving nopCommerce SQL query snippets you wish you knew earlier" series. In case you missed it, you can find the "Part -1" of this series HERE.
- If you have missed Part-1: Click Here
Like I mention in "Part-1" of this series, nopCommerce is driven by Microsoft SQL database and this is something that most of the active nopCommerce users would know. The out of the box nopCommerce administration section is quite user-friendly and allows any store owner (or admin) to manage or edit anything with ease.
The main aim of this series is to provide a quick solution to some of the common tasks that can be performed directly from the SQL database. Situations in which you can't (or don't want to) login to administration section, working directly on the SQL database can be a real life / time - saver.
Note: Always backup your nopCommerce database before making any kind of mass changes!
In this article, we will go over 2 more time-saving nopCommerce SQL query snippets.
1) Configuring / updating store settings
By default nopcommerce installation has only one store created which needs to be configured / updated by the store administrator. nopCommerce does provide a great way to configure / update the store settings from the administration section by going to the following location:
Administration > Configuration > Stores
But, sometimes a few developers / users face some difficultly in accessing the administration section (or store settings) locally if they have SSL certificate installed on the site, production site is hosting somewhere else (with any hosting company) and store is configured to use HTTPS.
A workaround to access the site locally is to turn OFF the store's SSL setting for the time being directly in the database.
Firstly, run the following SQL script (if you have SSL enabled for all pages):
where name = 'SecuritySettings.ForceSslForAllPages'
Secondly, run this SQL script to turn off the SSL setting:
set SslEnabled = 0
Note: You may have to restart your application.
TIP: You can also add a new store information via SQL script. Simply run the following script:
INSERT INTO Store
In the screenshot below, we can see the newly added store (via SQL script):
2) Deleting multiple blog comments
Managing a website is not easy and it requires a lot of work to keep the content / data clean. The job of managing a website becomes a lot tougher when you offer CMS features on your store site such as discussion forums, blog and news board etc.
CMS is certainly important for your online business because it helps in improving online brand as well as increase your search engine ranking by keeping the relevant content fresh on your store site. But, this also opens a door for "spam" reviews, comments, messages etc and as a website administrator it is your job to keep the data clean.
nopCommerce offers a variety of CMS features including blog and a very user-friendly administration area that allow store owners to manage blog posts as well as blog comments. But, as a store owner when you are dealing with hundreds of spam comments that you want to delete, deleting the comments directly from the database is a quick way to get rid of spam comments.
For an instance, let's say you want to delete multiple blog comments in one step and the only information you have is the date when those comments were posted.
In that case, you can use the following SQL script:
DELETE FROM BlogComment
WHERE CreatedOnUtc between '2016/09/09' and '2016/10/10'
NOTE: Replace the dates in your query (as per your requirements)
When you run this SQL query on your nopCommerce database, all the comments between that dates that you mention in the query will be deleted from the BlogComment table.
I hope many nopCommerce users / developers will find this series (time-saving nopCommerce SQL query snippets you wish you knew earlier ) useful.
Please feel free to post any question(s) or suggestion(s) that you may have for more SQL scripts regarding nopCommerce.
- Part 1: Click Here
- Part 2: Click Here