Living in a world of GUIs (Graphical User Interface) many people are no longer aware about what SQL can do. Recently a friend asked me about, is it possible to extract the email adresses of my registered user out of my Joomla? Sure it is!

It is not even difficult and the idea behind was to load them into some email list to send out season greeting to all subscribers. To keep it simple here I will explain it by using a more common wordpress example, but in principle it can be done for any data the same way.

Most email clients are able to import a CSV (Comma separated List of Values) File by default, as also Excel or Access will ‘eat’ them without problems.

Now we have to invoke the MySQL Command Line Client or any other tool which enables us to execute a  SQL Statement hopefully we will ending up with such a prompt:

mysql>

There we first tell MySQL  to work with the specific database in question.

mysql> use DATABASE     (e.q   use wordpress)

With the command desc we can get a field description of our table.

mysql> desc TABLENAME   (e.q.  desc wp_users)

Knowing about the table structure now, with select we can extract the data wished:

mysql> select user_nicename as Name,user_email as Email from wp_users;
+——-+———————+
| Name  | Email               |
+——-+———————+
| admin | admin@localhost.com |
+——-+———————+
1 row in set (0.00 sec)

Seeing the output and being sure about the result will match our needs we are now finally going to spool the query result into a file:

mysql> select user_nicename,user_email into outfile ‘c:/temp/u_emails.csv’ fields terminated by ‘;’ from wp_users;
Query OK, 1 row affected (0.01 sec)

Content of “c:/temp/u_email.csv”:

admin;admin@localhost.com

or as variation:

mysql> select user_nicename,user_email into outfile ‘c:/temp/u_emails.txt’ fields enclosed by ‘”‘ terminated by ‘;’ from wp_users;
Query OK, 1 row affected (0.01 sec)

Content of “c:/temp/u_email.csv”:

“admin”;”admin@localhost.com”

And now it should be easy for you to import all emails addresses within your email client’s address book.

As a Web Developer we sooner or later maybe required to work with a CMS (Content Management System), perhaps out of own interest or plainly being forced by a customer request.

The idea behind such tools is the try to separate the data from the presentation layer. Following this approach later on it will be easy to change the look and feel of your site just by changing or updating the template used, immediately all content pages will get shown in that brand new look without changing them one by one.

Setting up such a CMS is fairly easy, first by uploading all files that come with it to your server. We now need to put together your database details as they are the key to your central data store and specifying them together with a few other essential (but usually not difficult to answer) questions during a typical setup procedure. From then on it is just learning about and working with the tool ñspecify what tool, is it CMS(?) – to get your site ready to use.

Your site, usually developed on your own server, or within a subfolder of the original environment sooner or later you may find the need to move your installation.

In all cases the principle process is the same. Following the idea of a CMS, data can be either located within the file system, the database, or within both. So moving a CMS always requires moving both!

We have already given you a few tips here about how to move the files between servers quickly and therefore we will now focus on the database data only.

The main benefit of a database is that the data is stored within, in a very efficient way for accessing it quickly and in a structured way. Most databases come with some binary data export/import tools for transferring both data and structures.

Making a WordPress move and the usual MySQL database, phpMyAdmin is the tool of our choice. Mostly offered by the hosting provider itself it is often part of your hosting package and easy to access.

Once started it offers you a nice and neat surface interface enabling you to work with your database and it hopefully will not take you long to get familiar with it. With the main view opened and looking at your database the menu directly offers you the options export and import. Now choosing export we usually can go on with the offered defaults. But to prevent problems I would recommend you to ensure that the check boxes “Add DROP TABLE” and “Complete inserts” are checked and “Export type” is defined as “INSERT”.

With this set we can now press “Go” and the result will be a long and curious screen output containing both, the data and all necessary SQL (Structured Query Language) elements, later on it is necessary to re-import this data.

This output we will now cut & paste to an ASCII Editor of our choice and the reason for this is there maybe references pointing to structures of the old server we now have to change (now hopefully making them “relative”). Within the editor this can be done easily by using the usual search and replace function.

Importing the data back into the database or transferring it to another one, we now copy & paste our content back into phpMyAdmin and connect to the new target database. For the re-import I recommend the SQL functionality of the tool. There you can just paste your script into the big SQL input field and finally press the OK button below. And after a few seconds of processing, a friendly message will tell us that our data is stored back within the database.

Having ported both – data and files – we finally need to change a few runtime settings of the WordPress installation.

Once again you should be aware that we are working with two different locations; database and filesystem and you will understand tweaks will be required to both.

All the necessary information to access the database is stored within a file wp-config.php. We should remember that file as it was part of the initial installation we did earlier.

Now we open this configuration file within an editor of our choice and replace the relevant parts e.q. username, password, database, server and table prefix with the correct new values.

Finally and as a last step we have to head back to the phpMyAdmin and navigate down to a table called  wp_options. Browsing the content of this table we will see two option rows siteurl (option_id=1) and home (option_id=39) which contains the old site URL as an argument. Adjusting them with the new site URL will finally allow us to run the WordPress on our new Server without issues.

Sooner or later it can come to a situation in which we are keen to work with some blogging software or CMS. Or we need to upload a bigger set of photos to our server as for we want to publish them within an online slideshow or album.

Sure we can upload all zillion files with a handy ftp client  … file by file, but we soon will figure out about it will take ages to complete …. or we can try to speed it up using the best out of all worlds ……..

Most of todays webhosting is done on UNIX systems and even if we are not familiar with the UNIX Operation System itself, the knowledge of a few basic Unix Commands will help us speeding up with file handling in general. Assuming we DO have shell access to our webserver system (often called ssh (Secure Shell) access) the only thing we need is a ssh client and a minimum set of UNIX instructions to succeed.

As you might have got already, I’m suggesting you to upload all your files in a single set as an archive. Zip or Gzip your files locally and upload them to your server within one junk. As you will see later on, this has a lot of advantages as for it keeps the amount of data we have to transfer small as for it is zipped and therefore compressed.  As it also lowers or eliminates the rate of transfering problems you usually will have uploading several hundred files instead.

Alter uploading our file we now enter the server within our ssh client and switch to the folder in which we have uploaded our archive file earlier. We can do that with a simple command like cd /home/username/htdocs

Depending on which archive format we have used or deal with, we can now directly unzip the archive with: unzip filename.zip
(ZIP support is directly build in within all major UNIX implementations). But given the case we deal with some .gz file (mostly used within the UNIX world providing software distributions), we have to use a different set of commands.

In this case two different tools were used to provision us with the software package. One was the GZIP (compression) utility of UNIX, while before the Archive builder TAR was used to fit together all the several files and folders making it up to the final tool we are keen to use.

While GZIP is just a compressor, TAR could directly archive and compress itself. But to keep it easy here, I will explain it in two separate steps pointing out how the two tools work together to give you an idea about how it works.

In this mentioned second case we ususally start with some file called filename.tar.gz. Now we first have to decompress the archive file with a command: gzip -d  filename.tar.gz  and this will leave us with a decompress file now only named filename.tar.

As a now final step for getting our software at hand we finally have to extract all the content out of the archive file with the following command: tar xf filename.tar

Doing so will directly extract the archive content within the folder we are actually sitting in, by extracting all the achived directory structures stored within the archive file. As problem out of this we could end up with an unwished folder like product-version-x.x containing all the files, instead as wished having them directly within the root folder of our hosted environment.

This is not really done by the developer to annoy us as someone could assume now! In a difference to that, it is done for security purposes preventing us to mess up our file structures by mixing content which does not belong together … and at the end best practice on UNIX.

To correct this problem we now switch to the new subfolder with cd foldername and issue the command mv * ../.  invoking the UNIX command line utility move, which will move us all content one folder above.

Getting rid of the now orphaned subfolder is easily done with rmdir foldername  or … but be carefully with that rm -R foldername   as for it will delete everything within and below.  Is it necessary to mention that before we have to switch to the folder above with cd .. ???

And last and finally in case we should be happy with the new subfolder, but want to get rid of the version number though, we plainly can rename the folder by using the move utility again mv oldname newname e.q mv wordpress-2.6.3 wordpress