Concatenate 2 or more fields from a database table

MySQL

If you want to get a result of firstname and lastname together as a single field, you will use CONCAT_WS(separator string, field1, field2,…) function.
Here’s I will show you how. For example, a table name Manager has 4 fields and some records as shown below.

id firstname lastname email_address
1 Linda Doe linda@mynetworksolutions.com
2 Didi Menita didi@yahoo.com
3 Jane Doe jane@adobe.com
4 Elisa Bethoni bethoni@microsft.com

You will use the following query statement to produce the expected result as shown below.

SELECT id, CONCAT_WS(‘ ‘,firstname,lastname) AS managername, email_address FROM Manager;

Results:

id managername email_address
1 Linda Doe linda@mynetworksolutions.com
2 Didi Menita didi@yahoo.com
3 Jane Doe jane@adobe.com
4 Elisa Bethoni bethoni@microsft.com

Mathematical Functions in MySQL

MySQL

Name

 Description
ABS() Return the absolute value
ACOS() Return the arc cosine
ASIN() Return the arc sine
ATAN2(), ATAN() Return the arc tangent of the two arguments
ATAN() Return the arc tangent
CEIL() Return the smallest integer value not less than the argument
CEILING() Return the smallest integer value not less than the argument
CONV() Convert numbers between different number bases
COS() Return the cosine
COT() Return the cotangent
CRC32()(v4.1.0) Compute a cyclic redundancy check value
DEGREES() Convert radians to degrees
EXP() Raise to the power of
FLOOR() Return the largest integer value not greater than the argument
LN() Return the natural logarithm of the argument
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
LOG() Return the natural logarithm of the first argument
MOD() Return the remainder
OCT() Return an octal representation of a decimal number
PI() Return the value of pi
POW() Return the argument raised to the specified power
POWER() Return the argument raised to the specified power
RADIANS() Return argument converted to radians
RAND() Return a random floating-point value
ROUND() Round the argument
SIGN() Return the sign of the argument
SIN() Return the sine of the argument
SQRT() Return the square root of the argument
TAN() Return the tangent of the argument
TRUNCATE() Truncate to specified number of decimal places

Below are just some functions above that I’ve tested with my database.

ABS() function

SELECT abs(-2.55);

The result is 2.55

CEIL() OR CEILING() function

You can use either function, it still returns the same result.

SELECT CEIL(1.56);

The result is 2

Another example that you can add value to each record during the query. For example, your table “Cost” has 2 records in the field “money” which are 7.80 and 15.25 respectively. Now you want to add 100 to each record and get the ceiling values rather than the exact values. You can use the query below:

select ceil(money + 100) from cost;

The results are 108, 116 accordingly.

DEGREES() function

SELECT DEGREES(PI());

FORMAT(X,D) funtion

SELECT format(700000, 2);

The result is 700,000.00

PI() function

Select PI();

The result is 3.141593

POW() or POWER() function

Select Pow(2,2);

The result is 4

TRUNCATE() function

SELECT TRUNCATE(10.778, 2);

The result is 10.77

Group By and Order By

MySQL

In MySQL query, GROUP BY and ORDER BY will give you 2 different results.

Group By will return the unique values of a field while Order By will return number of records with alphabetical or numeric order based on what field you specify.

This is an example of query using Group By, you will see that the results will only list a unique lastname:

SELECT firstname, lastname, email_address FROM somedb.users GROUP BY lastname;

The results will be:
   Alice, Bloom, bloom@gmail.com
   Daniel, Nguyen, dnguyen@yahoo.com
   Lynda, Dina, dina@mail.com

While the query using Order By will return the records that have multiple lastname in alphabetical order, usually it’s  descendent by default unless you specify ASC for ascendent order.

SELECT firstname, lastname, email_address FROM somedb.users ORDER BY lastname;

The returned records will be:
   Alice, Bloom, bloom@gmail.com
   Titinan, Bloom, tibloom123@yahoo.com
   Vivian, Bloom, vivian@mail.com
   Binh, Nguyen, binhnguyen@microsoft.com
   Daniel, Nguyen, dnguyen@yahoo.com
   Lynda, Dina, dina@mail.com
   Yvon, Dina, yvon@adobe.com

In conclusion, use Group By when you want to get unique records of a field, and use Order By when you want to get a full lists without unique record restriction. Group By expression is useful when you need to apply statistic data. For example, if you want to count how many users in specific state are using your application. While Order By expression is useful when you need a list of user’s info listed in order for your marketing purposes.

How to set up configuration file for multiple servers

Joomla Development

If you install Joomla on multiple servers, you can have a config file that works on all servers. You don’t have to change the config file everytime you copy to a different host. I will show you how to do it easily, so you can work on a development server as well as production server without breaking the site.

Open the config file in Joomla root folder. You can use Notepad or any PHP Editor. Add a condition statement “if else” right before the class JConfig.

$server = $_SERVER[‘HTTP_HOST’];

if($server == ‘server1’){

  //place the class Config here and change all appropriate variables for that server

  class JConfig {
     var $offline = ‘0’;
     var $editor = ‘tinymce’;

    …

    var $host = ‘host_1’;
    var $user = ‘username_1’;
    var $db = ‘database_1’;
    var $dbprefix = ‘js_’;

   } //remember to close your class

}

else{

//place the class Config here and change all appropriate variables for the second server

  class JConfig {
     var $offline = ‘0’;
     var $editor = ‘tinymce’;

    …

    var $host = ‘host_2’;
    var $user = ‘username_2’;
    var $db = ‘database_2’;
    var $dbprefix = ‘js_’;

   } //remember to close your class

}

You’re done! Now you can use the file on 2 different servers. If you have more than 2 servers, use another if or you can start using switch() function.

Creating a directory in php

PHP

If your website is shared on *nix server, a directory created through mkdir() will not be assigned to you, but to the user that your host’s server or php process is running under, usually ”apache’ or ‘httpd’.

In practice, this means that you can create directories, even add files to them, but you can’t delete the directory or its contents nor change permissions to a directory.

It is therefore advised to create directories through PHP’s FTP API using PHP function. This is a function I wrote:

Hope this comes in handy for someone.

You might notice that when you create a new directory using this code: mkdir($dir, 0777); The created folder actually has permissions of 0755, instead of 0777. Why is this? Because of umask() in php engine.

The default value of umask, at least on my setup, is 18. Which is 22 octal, or
0022. This means that when you use mkdir() to CHMOD the created folder to 0777,
PHP takes 0777 and substracts the current value of umask, in our case 0022, so the result is 0755 – which is not what you expected.

The “fix” for this is simple, include this line: $old_umask_value = umask(0);

Right before creating a folder with mkdir() to have the actual value you put be used as the CHMOD. If you would like to return umask to its original value when you’re done, use this php function: umask($old_umask_value);