Mathematical Functions in 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

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

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

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);


How to build an external function login to phpBB forums

Today, I will show you how to build a function to log in a user from outside of the phpBB forums. Your forums has to be version phpBB3.

First of all, empty all files in the cache directory of your forums. Build a single file and name it something like login_from_outside.php and place it in the root of your forums directory.

Define a line to protect your file from hackers:
define(‘IN_PHPBB’, true);

Then include the following functions and common files from forums libraries. Remember to define the proper root path according to your server environment.
$phpbb_root_path = ‘./’; // this root path has to be according to your server path.
$phpEx = substr(strrchr(__FILE__, ‘.’), 1);
include($phpbb_root_path . ‘common.’ . $phpEx);
include($phpbb_root_path . ‘includes/functions_user.’ . $phpEx);

Then start the required sessions, so you can have all the values in hand.

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

If a user already logged in from another application, carry all the required sessions over including username, password, and email values. Those are required values for forums login function to work properly.
$my_username =       $_SESSION[‘username’];
$my_userpassword =   $_SESSION[‘userpassword’];
$my_useremail =      $_SESSION[‘useremail’];

/*
* Force user go to login page if they try to open this forum front-end page without login into previous application
*/
if(empty($my_username) || empty($my_userpassword)){
header(“location: /applications/”);
exit();
}

Next, build a hash encrypted password of a user to match the data in forums database.
$user_row = array(
‘username’=> $my_username,
‘user_password’=> phpbb_hash($my_userpassword), // using forum’s hash password
‘user_email’=> $my_useremail,
‘group_id’=> 2,
‘user_type’=> 0,
);

Now this is the fun part.
/*
* pass the username to this function to check if user exist in Forum users table
* return FALSE if user exist – TRUE if user not exists :>)
*/
$user_id_ary = array();
$rtn = user_get_id_name($user_id_ary, $user_row[‘username’], $user_type = false);

if(!$rtn){
// if user exists, automatically log them in to the Forum. Do not add user to users table any more.’;
log_user_in_from_outside();
}
else{
// if user not exists, add user to users & user_group tables; then automatically log him in to Forum
$rebuild_user_row = array(
‘username’=> $aw_username,
‘user_password’=> phpbb_hash($aw_userpassword),
‘user_email’=> $aw_useremail,
‘group_id’=> 2,
‘user_type’=> 0,
‘user_ip’=>$_SERVER[‘REMOTE_ADDR’],
);
$userid = user_add($rebuild_user_row, $cp_data = false);
log_user_in_from_outside(); //call the included function
}

Then you got to build a real function to be called to log user in. You can create a separate file and include that file using include_once or require_once function. You can also create a function in the same file. Either way that works best for you. To my opinion, writing a function in a separate file is a much more cleaner than in the same file. If you want to modify a function in the future, you can only change at 1 spot and not affect other codes or logic.

/*
*  This function will be called to automatically log user to the Forum from another application
*/
function log_user_in_from_outside(){
// remember to declare global variables before using them. Otherwise, you wouldn’t have the values ready
global $user, $auth, $my_username, $my_userpassword;

// this _SERVER function allows you to move your codes to different server without breaking the the logic.
$forums_host = ‘http://’.$_SERVER[‘HTTP_HOST’].’/forums’;

if($user->data[‘is_registered’]){
// if user didn’t login to other application yet, take him/her to the forums frontpage to log in
header(“location: $forums_host”);
}
else{
// insert data to users & user_group tables in forum DB; then log customer in the forum automatically
$result = $auth->login($my_username, $my_userpassword, True);

if ($result[‘status’] == LOGIN_SUCCESS){
header(“location: $forums_host”);
}
else{
header(“location: $forums_host/ucp.php?mode=login”);
}
}
}