Useful Resources for sNusers

Print This Page
Installing sNews:  Main  Install 1.4  Install 1.6 or 1.5  
Server Installation:  Install XAMPP-XP  
Databases:  For sNews  SQL Files  Multiple Installations  
SQL Dump Files

 

Making an SQL File SQL File Icon

Updated: September 01, 2008

Introduction

Your sNews installation package (1.5, 1.6 or 1.7) contains a readme.htm file which contains the script required to create the tables in your new sNews database. The file instructs you to create an SQL query in phpMyAdmin, then copy and paste the script into the query textarea to create your tables. This tutorial will show you how to take that script and create an SQL file that can be used to create your tables using the Import tab in phpMyAdmin.

Your next question might be... "Why do we need an SQL file?" Well, if you are only going to use the sNews once or twice, you don't need to do this. But, if you're like me... as you get more familiar with and modify your sNews projects... you will end up making several installs and some will (inevitably) have modifications to the database tables. It is easier to clone a default (un-modified) SQL file, give it a new, easy-to-identify name, and make your changes as required. In the long run, you will find working with .sql files to be more convenient.

Here are just two reasons I find .sql files more convenient. The first is... they are easier to locate on your WinXP computer by the SQL icon when your browsing for files in Windows Explorer. (as shown in the page title above). The second is... easier file management. When I create my first sNews project (any version), I start with a set of master files for it within its own folder. Within that folder, I'll create an SQL folder. This folder never gets uploaded to a host server, and it's a good place to keep all project-related SQL files in. Each time I install a new sNews project - offline (localhost) or on a host server - I use the appropriate SQL file to create the database tables for that install. Or, if I am using the same database for several different snews installations, I'll take a copy of the snews_xx.sql file... add a prefix to the table-names, and save it as a new file used specifically for that installation.

An SQL file is basically a text file with a .sql extension added to it. If you have used phpMyAdmin to make a back-up of an existing database, the back-up was created as an .sql file. The extension enables phpMyAdmin to execute the script within it automatically, so you don't have to manually copy and paste it into an SQL query textarea. These files can contain all sorts of commands to do different things, such as checking for existing tables and dropping them (if found), or overwriting existing data and inserting new data into specific columns within a specific table. So... when we create an .sql file, we'll be using the same format that a back-up file is created in. You can make a full-time career out of learning and applying all there is to know about MySQL in the MySQL 5.0 Reference Manual.

Creating the File

Let's start by converting the existing script from the readme.html file included with the sNews 1.5 installation package. Open a copy of the file in a browser window (offline, no need to be online). And open your "good" code editor... and by "good", I mean a code editing application designed for developer use... not Notepad or WYSIWYG web-page applications like Dreamweaver or FrontPage. I use PHP Designer because... well... when you can have the Ferrari of Editors for Free... why not?

If you are usiing PHP Designer, open a new (empty) .sql file... File >> New >> SQL. Copy and paste the script from the readme.html file into the new .sql file. Now, save it where you want to keep your master .sql file, but keep it open... add the following comment section to the top of your file, and re-save it:

-- MySQL: Table-set for (project name)
-- Creation Date: (full date)
--
-- DB Host: localhost
-- DB Name: my snews
-- List any changes made to default tables
-- --------------------------------------------------------
--

The double-dash (hyphen) at the start of each line comments the line out so we can add reference notes without affecting table creation. This also makes the lines italic and grey in color. You can replace the double-dashes with a #-sign for the same purpose, but the lines will appear to be active and assume the colors assigned to it by your code editor. You can change the version numbers to whatever you wish and include any useful references... such as the date of creation, etc.

The Table script parts

Each table is created by a block of script. If there is some data to insert into a table, it will be followed by a group of INSERT INTO strings, each holding the data to be inserted for each entry.

A default sNews 1.5 or 1.6 package starts with a set of four tables, and only the settings table has data insert strings. Let's take a look at the script that creates the settings (fourth) table. In this case, I've started with a string that tells phpMyAdmin to DROP a settings table if one already exists. The next CREATE TABLE string tells phpMyAdmin to create the table and call it settings, and the opening bracket at the end starts creating the columns in the table. In this case, there are three columns being created. And the last line - a closing bracket followed by a semi-colon - ends the instructions for creating the table. This same general format is used to create all MySQL database tables.

-- -------------------------------------
-- Table structure for table 'settings'
--
DROP TABLE IF EXISTS settings;

CREATE TABLE settings (
    id int(8) primary key auto_increment,
    name varchar(20) NOT NULL,
    `value` varchar(60) NOT NULL
);

Data Insertion: Notice that there are 23 lines under the above script that all begin with INSERT INTO. Each of these lines inserts data into the settings table, and this data is used by the sNews engine (snews.php file) to run the site when you go to view the Home page for the first time. Once you have logged into your site's Admin Panel (with the username and password test | test), you can change any of the settings values in the Settings panel, and the data in this table's columns will be updated when you save your new settings.

-- Insert data for table 'settings'
--
    INSERT INTO settings VALUES (1, 'website_title', 'sNews 1.6');
    INSERT INTO settings VALUES (2, 'home_sef', 'home');
    INSERT INTO settings VALUES (3, 'website_description', 'sNews CMS');
Conclusion

You have completed the steps of converting your table-creating script into a valid .sql file. Make sure you have saved it with the .sql extension to a safe place on your hard drive so you can use it to create tables quickly using phpMyAdmin.

Ready-made SQL File Downloads: snews1.6.sql | snews1.5.31.sql | snews1.4.sql

And, as noted earlier, you can also take these 'master' files, copy and create custom table-sets to be used for multiple installations of sNews... and have all the tables in one single database.