Pretty annoying, exploding an empty string with – for example space – returns an array with one element, indexed with 0 and containing an empty string:

$string = "";
explode(" ", $string);

returns

array(
  [0] => ''
)

There is a simple workaround that also removes the unnecessary empty elements – array_filter(). This function removes all the array elements whose values evaluate to boolean false:

array_filter(explode(" ", $string));

Returns:

array(
)

There are multiple reasons I recommend using pure-ftpd-mysql as ftp service: can authenticate both physical and virtual (read from database) users. It’s highly but easily configurable.

Installing:

apt-get install pure-ftpd-mysql

The configuration files are found at /etc/pure-ftpd.

By default, pure-ftpd authenticates only users that has an uid 1000 or over. Thus root cannot authenticate (having uid of 0). By default, when the system was installed, a user was created and this has the uid of 1000, so you can authenticate with this user: go to an another computer (over LAN) and try to connect via a FTP client.

To authenticate virtual users, first you need to set up  the following:

  • have access to MYSQL tables via ip, username and password
  • create a table that has the following important fields: username, password (encoded as md5, sha1, crypt or password) and an optional field ftp_enabled that is 1 if the user’s FTP access it’s enabled.

Edit the /etc/pure-ftpd/db/mysql.conf file and uncomment/change the following lines:

  • MYSQLServer ip-address (leave 127.0.0.1 if localhost)
  • MYSQLPort port (leave 3306 if not changed)
  • MYSQLUser mysql_username
  • MYSQLPassword mysql_password
  • MYSQLDatabase mysql_database_name
  • MYSQLCrypt crypt (md5, sha1, crypt or password)
  • MYSQLGetPW SELECT `ftp_password` FROM `users` WHERE `username`=”\L” AND `ftp_enabled`>0 AND `account_status`=0
  • MYSQLGetDir SELECT CONCAT(‘/var/ftp/’, LPAD(`id`, 8, ‘0’)) FROM `users` WHERE `username`=”\L”

Note on the last setting: pure-ftpd-mysql will create automatically a separate directory for each authenticated user in /var/ftp. The lpad function just pads the username with zeroes up to 8 characters. Obviously you can have anything here to name the user’s directory.

Important settings

To add startup options for the FTP service, go to /etc/pure-ftpd/conf and create files named as the options in the documentation. Be careful, the documentation lists the options lowercase, but the configuration files needs to be named with capitalized words (would be nice to write the documentation correctly…). The files contain the respective options as text (yes, no, 1, 500, etc.)

The following config files that have importance:

  •  AllowDotFiles – yes|no; Allow anonymous users to read files/directories starting with dot (hidden)
  • CreateHomeDir – yes; It’s important to automatically create the home dir for virtual users
  • CustomerProof – yes; Prevents your users against making bad ‘chmod’ commands, that would deny access to files/directories to themselves.
  • DontResolve – yes; Prevent reverse DNS lookup. Useful to speed up the FTP connection (if the main DNS server is down or unreachable, the FTP connection slows down to crawl).
  • MaxClientsNumber – XXX; Adjust this number according to the server capacity and number of expected clients.
  • MaxClientsPerIP – X;
  • MaxIdleTime – XX; In minutes.
  • NoAnonymus – yes|no; Allow/deny anonymous authentication.
  • NoChmod – yes|no; Allow/deny the chmod command.
  • PAMAuthentication – yes|no; Allow/deny authentication of physical users.
  • PerUserLimits – X Y; X – max connections with the same username, Y – max anonymous connections.

To apply the settings, restart the server

/etc/init.d/pure-ftpd-mysql restart

If there are errors upon startup, you need to check the configuration file names or their content.

You may read the full documentation at pureftpd.org.

I was playing with some statistics and wanted to display some queries as a single, comma-delimited text.

Why one may need this? Since the result of the queries can be concatenated in an endless ways by (eg. PHP) code… The answer: to have one-line query returning directly the string – eg. for statistical purposes only.

The data

Consider any type of data, for example:

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_bin NOT NULL,
`color` varchar(20) COLLATE utf8_bin NOT NULL,
`quantity` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`));

Insert some data…

INSERT INTO `test` (`id`, `name`, `color`, `quantity`) VALUES
(1, 'Orange', 'orange', 5),
(2, 'Apple', 'red', 25),
(3, 'Strawberry', 'red', 15),
(4, 'Banana', 'yellow', 12),
(5, 'Lemon', 'yellow', 1),
(6, 'Raspberry', 'red', 15),
(7, 'Raspberry', 'red', 15),
(8, 'Plum', 'blue', 5),
(9, 'Blueberry', 'blue', 25),
(10, 'Blue grapes', 'blue', 1),
(11, 'Grapes', 'green', 1),
(12, 'Kiwi', 'green', 5);

The solution: MySQL GROUP_CONCAT

To return any query as string, use the GROUP_CONCAT():

SELECT GROUP_CONCAT(`name`) FROM `test`
>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi

To alter the order of the results:

Doesn't work:
SELECT GROUP_CONCAT(`name`) FROM `test` ORDER BY `name`;
>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi

Does work:
SELECT GROUP_CONCAT(`name` ORDER BY `name`) FROM `test`;
>> Apple,Banana,Blue grapes,Blueberry,Grapes,Kiwi,Lemon,Orange,Plum,Raspberry,Raspberry,Strawberry

To enumerate distinct values:

SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test`;
>> orange,red,yellow,blue,green

Ordering works like this:

SELECT GROUP_CONCAT(DISTINCT `color` ORDER BY `color`) FROM `test`;
>> blue,green,orange,red,yellow

Filtering

The WHERE clause must be specified at the end of the query, not inside the GROUP_CONCAT():

SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test` WHERE `quantity`>10;
>> red,yellow,blue

Specify a separator

For practical purposes, you may want a different separator. Use the SEPARATOR clause inside the GROUP_CONCAT(). Notice the spaces around the dashes:

SELECT GROUP_CONCAT(DISTINCT `color` SEPARATOR ' - ') FROM `test` WHERE `quantity`>10;
>> red - yellow - blue

Documentation

You may want to read more about GROUP_CONCAT()… Visit the official documentation page at dev.mysql.com.

This article explains some very basic linux commands an facts needed when setting up and working with a web server. Note that this article isn’t covering all about Debian, in fact covers a very few but important knowledge one may need.

This article contains information mainly for beginners.

Some useful facts

  • under Linux, everything is a file. Even a device, such as a HDD or port
  • files have no extension. In fact the can have but files aren’t recognized by extension. Thus, in a file named ‘aaa’ you can put a wav sound, a text, or anything binary. File names are case-sensitive!
  • you can add/remove software (packages) with apt-get. There are no two similarly named packages in Linux. These programs/packages usually hold their config files under /etc
  • there are programs or services. Programs are like a text editor. You can run many instances and you can exit them. Services are running in background (eg. ftp, apache).
  • the main system is text-based. You must write at the command prompt to interact with the system. BTW everything is very simple and archaic…

To log in or log out

Logging in is automatically initiated. Without logging in you can’t do anything, even you can’t stop the server (except pressing the power button to initiate shutdown)

To log out use:

logout

To start/stop/reboot the machine

Use one of the following:

reboot
halt
shutdown -h now

To see what’s here (files and directories here)

ls
ls -l
ls --help

To change directory

cd lower_dir
cd /absolute_dir/dir/dir

You may want to see more files/directories so install the Linux’s Norton Commander-like app, Midnight Commander:

apt-get install mc
mc

See the free space

df
df -h

Change the permissions over files and dirs

chmod 0xxx filedir
chmod --help

You can read more about permissions at wiki.debian.org.

To manage running services

If you change a service’s config, you must restart the service.

/etc/init.d/xxxxx restart
/etc/init.d/xxxxx stop
/etc/init.d/xxxxx start
service xxxxx restart
service xxxxx stop
service xxxxx start

Replace xxxxx with the service name.

[To be continued…]

With a Samba server installed and running on Linux, we can access partly or the whole system via LAN.

Install Samba:

apt-get install samba

Follow the install wizard and enter “workgroup” or the group your network uses.

The outside access can be configured for a special directory and for a special user or it’s possible to see the entire system. The latter can be used for administration purposes via LAN, but it’s not recommended for production servers or for paranoiac users.

Set up restricted access

  1. (On the server) add a directory for samba shares
    mkdir /samba

    (Change the /samba directory according to your needs)

  2. Add a usergroup for samba
    groupadd sambagroup
  3. Give the directory to the group
    chgrp sambagroup /samba
  4. Change the rights
    chmod 0777 /samba
  5. Add a user
    useradd -g sambagroup winuser
  6. Give the user a password
    passwd winuser
  7. Add samba password. This will be the password to access the server.
    smbpasswd -a winuser
  8. Enable user in samba
    smbpasswd -e winuser
  9. Add the following to /etc/samba/smb.conf. Change the SambaShareName to a nice one.
    [SambaShareName]
    path = /samba
    writable = yes
    public = no
    valid users = winuser
    force create mode = 0777
    force directory mode = 0777
  10. Restart the Samba server (every time you make changes to /etc/samba/smb.conf, the Samba server needs to be restarted).
    /etc/init.d/samba restart

Set up full access

It’s not a problem to give full access to the whole system if the server isn’t accessible from outside the router or the local area network is accessed only by the developer(s).

  1. Add samba password
    smbpasswd -a root
  2. Enable user in samba
    smbpasswd -e root
  3. Add the following to /etc/samba/smb.conf
    [SambaShareName]
    path=/
    writable=yes
    public=no
    valid users=root
    force create mode = 0777
    force directory mode = 0777
  4. Restart the Samba server
    /etc/init.d/samba restart

Note: in a Windows system, you will see the server as “SambaShareName”. Change this name accordingly.

At first glance localhost seems to be a good solution for developing PHP web apps, but it has some drawbacks, as:

  • switching between projects isn’t so easy for moderately complex websites, especially when working with databases, having fixed directory structure, etc.)
  • it’s difficult to change the server (Apache) configuration
  • it’s difficult to switch between projects
  • some features can be tested only on a physical (or virtual) server(s) (memcached, network file systems, mysql-based ftp login, etc.)

Setting up a Linux server it’s easier than I thought first… OK, at the beginning I read tons of documentation and spent many hours to find information on the internet to set up and configure everything as needed.

The server will have these features (packages installed):

  • Apache 2
  • PHP5 (+gdi, MYSQL)
  • MYSQL
  • FTP
  • Samba to access the server directly from Windows

Here we start…

This article is based on a Debian 6 installation. Over time – as Debian evolves – differences in the namings and setup process may change.

You can use a virtual machine (recommended) or a physical computer.

What do you need for a virtual machine?

  • download and install the vmware player from vmware.com.
  • download the latest Debian net install ISO file (32-bit or 64-bit)
  • start the vmware player, create a new virtual machine by following the wizard. To read more about creating the vmware virtual machine, read this article.

What do you need for a physical machine?

  • an older computer (a second-hand it’s good if purchased), having a decent processor, at least 512mb of RAM and a decent HDD (10-20Gb it’s enough for simple development)
  • connect the computer to the network and use a temporary monitor for installing the OS
  • write a CD with the desired Debian (32/64-bit) and start installing

For both virtual/physical configurations you will need a network router. Connect your computer and the physical Linux server to this router. Later you may want to change the router’s settings in order to have access to the server from outside of your LAN.

Next, for both cases, virtual or physical server, install the Debian OS.

Take a blank paper or create a new text file on an another machine and note everything you enter (names, passwords, etc.) during the installation!

  1. Boot from the CD and wait for the Installer boot menu. Choose “Install”.
  2. Choose “English” for the installation process
  3. Choose your exact location. It’s important to set up correctly the time zone and to reach automatically the nearest servers for the extra packages that will be installed lately
  4. Configure locales. Choose “United States – en_us UTF-8”. Note that it’s best to use UTF-8
  5. Select the keyboard layout. As programmer, you may want the default “American English”
  6. The installer will begin to install the basic packages then prompts for the network config. Use any “hostname” you want
  7. For “Domain name” you may write anything or leave it blank
  8. Enter and re-enter the root password. If you wish to use the server only inside LAN, you may enter a weak password because you will enter this password frequently in the future. If you wish to reach the server also from the internet, a strong password is recommended!
  9. Create a non-root user; enter the names and passwords.
  10. Set-up the disk partitioning as you wish. The easiest way is to use “Guided – use entire disk” – “All files in one partition”
  11. Wait for the “installing the bases system” finishes and the choose the “Debian archive mirror country”.
  12. An important step, choose what extra packages to install on the system. The best choice is to install ONLY the “Standard system utilities”. Don’t install the “Graphical desktop environment” as you will not use this and it will eat up only valuable system resources.
  13. Choose “Yes” when asking to install GRUB boot loader.

At this point the system should restart without problems. Log in with the username ‘root’ and given  password. If everything works, the first stage it’s finished.

Before using the server, a lot of stuff has to be installed. Until now, we installed the system without anything extra to let you choose and install only the packages you want to use later.

Install the basic packages and configure network

Basically the server doesn’t needs a separate, physical monitor because it will be accessed remotely. So you can place the physical machine everywhere, preferable far away from living spaces to exclude the electron smog and noise created by running machine. To access remotely the server, use the putty.exe (download the putty.exe from putty.org and copy to c:\windows folder). But first let’s configure the access:

Install SSH

SSH is needed to access the server remotely via putty.

apt-get install ssh

Install Midnight Commander

apt-get install mc

To start the Midnight-commander, enter ‘mc’. You will need this app to edit configuration files more easy.

Set up a fixed IP address for the server

Start mc. Go to /etc/network and edit the interfaces file.

First comment out the line

# iface eth0 inet dhcp

then add to the end of the file:

iface eth0 inet static
address 192.168.1.xxx
netmask 255.255.255.0
network 192.168.1.0
broadcast 192.168.1.255
gateway 192.168.1.1

Notes:

  • we assume that your router is configured as 192.168.1.x. If there are other values in the network configuration, change the values accordingly.
  • replace xxx with the desired number for the server. Make sure that no other devices are set up with the xxx number.
  • replace the 192.168.1.1 with the router’s address

Reboot the server. If some processes fail to start, check the interfaces file and/or reboot the router. If everything seems to be OK, you may check the ip address from your Windows machine by starting cmd and typing

ping 192.168.1.xxx

If it’s working, we’re finished in the front of the server so we can access it remotely via putty:

  • At your Windows machine: Start -> run-> putty.exe (works only if you previously downloaded and copied putty.exe to c:\windows)
  • Enter the 192.168.1.xxx address to “Host name (or IP address)”. Use “Port” 22
  • Make sure “SSH” is checked
  • Give a name at “Saved Sessions”
  • Optionally you may change at Window -> Translation the “remote Character Set” to UTF-8
  • Save the session for future use
  • Click the “Open” button (A dialog may pop up about a missing registry value)

At this point you should see the server’s login prompt. Use the root/password combination to log in, it should work fine.

Notes regarding login-logout and restarting/stopping the remote server:

  • use logout to log out
  • use reboot to restart the server
  • use halt to stop the server

(Read more about useful Linux commands and facts).

Installing the packages needed for the web server

Install Apache

apt-get install apache2

Enable the rewrite engine (for using SEO-friendly url’s)

a2enmod rewrite

To set up domains/subdomains on LAN, please read this article.

Install MySQL

apt-get install mysql-server mysql-client

To log in into MySQL and execute SQL commands, use:

mysql -uroot -p

Install PHP5

apt-get install php5

You may want to install extra PHP packages:

apt-get install php5-mysql
apt-get install php5-gd
apt-get install php5-imagick

Important note: in order to activate the PHP5 plugin in Apache, you must restart the Apache service

service apache2 restart

or

/etc/init.d/apache2 restart

It’s best to restart the server before testing the installations…

To test the web server, open a browser on the Windows machine and type 192.168.1.xxx and press enter at the address bar. Yo should see “It Works!“. If you see a blank page or ‘The page cannot be found’, there is an error that may be:

  • the ip address isn’t the server’s address (check with cmd -> ping 192.168.1.xxx)
  • the Apache service isn’t running on the server (check with ps aux | less)
  • the Apache service is running, but after some installation(s) something wasn’t restarted (restart the server)

There’s more…

At this point you have a very basic, but working web server. In order to start developing, you will need a Samba of FTP access to edit the php files on the server. Please follow the Samba or FTP articles to set up these.

I have two tables, “products” and “brands”. In the “products” table, the product’s name is in the “name” field. The product’s brand is represented by the # (diez) character. To know which is the brand, there’s a “brand_id” field.

So far we have:

products table:
  id: int
  name: varchar
  brand_id: int

brands table:
  id: int
  name: varchar

I was thinking in the other day, can I build the name only in SQL? The problem is that not all products have a brand…

The first approach was the REPLACE() function:

function REPLACE(str, from_str, to_str)

Nice, but… There’s always a but… In the documentation it doesn’t states, when to_str is NULL, the result is also NULL! Seems to me illogical, normally I expect the from_str
cleared from str in this case…

So, the query

SELECT REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id)) FROM products;

results in several NULL rows… This is wrong, since products without brands should be displayed as-is.

What solves the problem, is the IF function:

IF(condition, true_value, false_value)

We use the “brand_id” field as condition, we replace the diez only if brand_id > 0. The final query looks like this:

SELECT IF(brand_id > 0, (REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id))), name) FROM products;

Not a big problem, but catches my eyes and at some point looks unprofessional…

Scenario:

<p><img src="icon.png" /> Some text</p>

When looking at the rendered page, the image is down by 1px, relative to text. Although the img tag is pre-formatted in CSS to appear vertically centered to the text, it’s appearance is still wrong. I’ve checked this in many browsers and I found that this everywhere looks the same…

Look at this example:

Some text

Check closely the image position… To see better the image-text alignment, select the text or press Ctrl-A.

The solution…

Set the image position to relative and move up by 1px:

style = "vertical-align: middle; position: relative; top: -0.15em;"

Now it looks nice:

Some text

That’s it. For the simplicity and standard workflow, you may include in your css file:

img {
  border: none;
  vertical-align: middle;
}
img.text {
  vertical-align: middle;
  position: relative;
  top: -0.15em;
}

and use the style in html:

<p><img src="icon.png" class="text"/> Some text</p>
Inline image down by 1px

Screenshot of selection using inline images