WSN Knowledge Base

Running Arbitrary SQL or PHP

Sat, 22 Mar 2014 08:08:00 GMT

Sometimes you may need to run some SQL queries or PHP code on your site. Admin Panel -> Miscellaneous -> Advanced Options enables this. Of course, you must know SQL/PHP or be copying from a source that knows in order to do this.

Running SQL Queries

In the "Run SQL on Database" box you can type/paste any queries you need to run. These queries will run in WSN's database as the mysql user specified during setup. You can use {PREFIX} as a stand-in for WSN's database tables prefix for the current installation, in case you don't remember what prefix you're using. For example, to run a query to reset all views counters to zero for articles added in the last 24 hours you would do
UPDATE {PREFIX}links SET hits=0 WHERE time > UNIX_TIMESTAMP() - 60*60*24;

When running multiple queries, be sure to put one query per line with a semicolon terminating each query. Note that SELECT queries will be useless -- they'll run but nothing will be output. To retrieve data you'd need to run PHP which does the query and then displays the results.

You also have the option to upload a .sql file containing a series of queries. Uploading large SQL files is not recommended as they may timeout or hit memory limits. It's better to use PHPMyAdmin for uploading SQL files, as PHPMyAdmin has better tools for loading them in parts.

Running PHP

In the "Execute PHP Code" box you can type/paste any PHP code you need to run. The opening <?php and closing ?> are optional. The code executes inside a special function, so in order to get access to any WSN variables you'll need to globalize them. For example, if you want to manipulate $settings, write global $settings; before you reference $settings. If you're doing any filesystem operations, keep in mind that the script is running from the admin panel so you need a ../ to go up to WSN's base directory.

You can also upload a .php file, but keep in mind the file will have to be written to run from the admin panel directory path.

Automating Tasks

Thu, 25 Sep 2003 14:41:54 GMT

Note: This is an advanced topic for programmers.

At Admin Panel -> Miscellaneous -> Crons, you have an option to run a script every x hours. This allows you to run PHP cron jobs within WSN. Do not alter the three preexisting crons, they're necessary to WSN's operation.

Any cron script you add will be included into the middle of WSN and so will have access to all values set in start.php -- $thismember, $settings, and of course the ability to use the WSN classes. There are various custom tasks you might wish to perform at intervals, the ideas are up to you. This option is clearly intended for those who can do PHP coding on their own, so everyone else should ignore it.

Note that these tasks are not true cron jobs in the operating system, they must be executed as an actual visitor visits a page. If there's no traffic on the site, it won't run. Do not run anything which will take longer than a few seconds, because there will be a user waiting for their page to load. A semi-intensive task is better done here where it only affects on person every x hours than if it were done on every page load, to be sure, but if you have a truly intensive task you should run it as a true cron job using your web host's facilities if they offer this feature to you.

Mass Alterations

Thu, 25 Sep 2003 14:40:50 GMT

Sometimes you want to change a lot of data, and you want to do it all at once. Here's how.

Warning: These are powerful tools, and a simple mistake could wipe your data. Be sure to make a database backup at Admin Panel -> Maintenance -> Backups first before performing mass alterations.

To perform mass alterations, go to Admin Panel -> Miscellaneous -> Advanced Options -> "Perform Bulk Data Alterations".

The first set of options lets you replace one value with another in a chosen field. Note this will only replace exact matches, it won't match if the value is only part of the content of the field for the record.

The second set of options lets you set a new value based on a condition in a different field. For example, you could set all articles which have more than 200 hits to be the article type 'popular'.

For users with some knowledge of SQL, the "Run SQL on database" box on that same Advanced Options page provides the ultimate flexibility.