Pages

Excel Functions : Convert Numbers into Words

Modified code given below for those who want to use it. Currency inputs are optional and you will not need to input currency details in case you are using it for Indian Currency. Still this can be used for any currency provided you give currency inputs.

Remove Microsoft Word HTML tags

The following function takes some nightmarish Word HTML and return a clean HTML output that you can use safely on the web.
function cleanHTML($html) {
 $html = ereg_replace("<(/)?(font|span|del|ins)[^>]*>","",$html);
 $html = ereg_replace("<([^>]*)(class|lang|style|size|face)=("[^"]*"|'[^']*'|[^>]+)([^>]*)>","<\1>",$html);
 $html = ereg_replace("<([^>]*)(class|lang|style|size|face)=("[^"]*"|'[^']*'|[^>]+)([^>]*)>","<\1>",$html);
 return $html
}
Source : http://tim.mackey.ie/CommentView,guid,2ece42de-a334-4fd0-8f94-53c6602d5718.aspx

Calculate Age from Database in MySQL

This function can be used to calculate age.
DELIMITER $$

DROP FUNCTION IF EXISTS Age $$

CREATE FUNCTION Age( dob DATE )
RETURNS CHAR(20)
BEGIN
DECLARE years INT default 0;
DECLARE months INT default 0;
DECLARE days INT default 0;
DECLARE age DATE;

-- Check that the dob we're given is useful
IF dob is null or dob = 0 or dob = '0000-00-00' THEN
 RETURN dob;
END IF;
SELECT date_add('0001-01-01', interval datediff(current_date(),dob) day ) INTO age;
SELECT YEAR(age) -1 INTO years;
SELECT MONTH(age)-1 INTO months;
SELECT DAY(age) -1 INTO days;

IF years THEN
 RETURN concat(years,'y ',months,'m');
ELSEIF months THEN
 RETURN concat(months,'m ',days,'d');
ELSE
 RETURN concat(days,' days');
END IF;
END $$
DELIMITER ;

Use

SELECT Age(DOB) FROM table; 
Source : http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Validating Form Input

This function strips unwanted characters (extra space, tab, newline) from the beginning and end of the data using the PHP trim() function, strips any quotes escaped with slashes and passes it through htmlspecialchars().
function checkInput($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

Use

checkInput($_POST['postdata']); 
checkInput($_GET['getdata']);

Redirect page

The header() function sends a raw HTTP header to a client but it is important to notice that header() must be called before any actual output is sent. In my experience, Sometime we have to redirect the page after actual output is sent . In that case , I use this fucntion .

function get_redirect($location) {
 print "<script>";
 print " self.location='$location'"; 
 print "</script>";

}

Use


get_redirect('index.php');
I found this function in php.net which seems very useful
<?php
function redirect($filename) {
    if (!headers_sent())
        header('Location: '.$filename);
    else {
        echo '<script type="text/javascript">';
        echo 'window.location.href="'.$filename.'";';
        echo '</script>';
        echo '<noscript>';
        echo '<meta http-equiv="refresh" content="0;url='.$filename.'" />';
        echo '</noscript>';
    }
}
?>

Use


redirect('http://www.google.com');

UCFirst Function for MySQL

Below is a MySQL implementation of PHP’s ucfirst function which capitalizes the first letter of each word in a string
DELIMITER $$
CREATE FUNCTION CAP_FIRST (INPUT VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE len INT;
    DECLARE i INT;
    SET len   = CHAR_LENGTH(INPUT);
    SET INPUT = LOWER(INPUT);
    SET i = 0;
    WHILE (i < len) DO
        IF (MID(INPUT,i,1) = ' ' OR i = 0) THEN
            IF (i < len) THEN
                SET INPUT = CONCAT(
                    LEFT(INPUT,i),
                    UPPER(MID(INPUT,i + 1,1)),
                    RIGHT(INPUT,len - i - 1)
                );
            END IF;
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN INPUT;
END$$
DELIMITER ;

Use

SELECT CAP_FIRST('my string of words');
Source : http://www.flynsarmy.com/2011/12/ucfirst-function-for-mysql/

Introduction to a database class

Being in nature a lazy person, I hate repeating code. As most of my websites use databases, I use a database class in all my websites. Now the purpose of this tutorial isn't to explain classes or object orientated programming (OOP) - that's a subject for another time. I'll just be showing the nuts and bolts of my database class and the basic functions I use most often when working with MySQL databases. The first thing I do is create a file database.php (usually saving it in an includes subdirectory) with the following code:
<?
class Database
    {
    var $Host     = "localhost"; // Hostname of our MySQL server.
    var $Database = "databasename"; // Logical database name on that server.
    var $User     = "username"; // User and Password for login.
    var $Password = "password";
    var $Link_ID  = 0;  // Result of mysql_connect().
    var $Query_ID = 0;  // Result of most recent mysql_query().
    var $Record   = array();  // current mysql_fetch_array()-result.
    var $Row;           // current row number.
    var $LoginError = "";
    var $Errno    = 0;  // error state of query...
    var $Error    = "";
    
//-------------------------------------------
//    Connects to the database
//-------------------------------------------
    function connect()
        {
        if( 0 == $this->Link_ID )
            $this->Link_ID=mysql_connect( $this->Host, $this->User, $this->Password );
        if( !$this->Link_ID )
            $this->halt( "Link-ID == false, connect failed" );
        if( !mysql_query( sprintf( "use %s", $this->Database ), $this->Link_ID ) )
            $this->halt( "cannot use database ".$this->Database );
        } // end function connect

//-------------------------------------------
//    Queries the database
//-------------------------------------------
    function query( $Query_String )
        {
        $this->connect();
        $this->Query_ID = mysql_query( $Query_String,$this->Link_ID );
        $this->Row = 0;
        $this->Errno = mysql_errno();
        $this->Error = mysql_error();
        if( !$this->Query_ID )
            $this->halt( "Invalid SQL: ".$Query_String );
        return $this->Query_ID;
        } // end function query

//-------------------------------------------
//    If error, halts the program
//-------------------------------------------
    function halt( $msg )
        {
        printf( "</td></tr></table><b>Database error:</b> %s<br>n", $msg );
        printf( "<b>MySQL Error</b>: %s (%s)<br>n", $this->Errno, $this->Error );
        die( "Session halted." );
        } // end function halt

//-------------------------------------------
//    Retrieves the next record in a recordset
//-------------------------------------------
    function nextRecord()
        {
        @ $this->Record = mysql_fetch_array( $this->Query_ID );
        $this->Row += 1;
        $this->Errno = mysql_errno();
        $this->Error = mysql_error();
        $stat = is_array( $this->Record );
        if( !$stat )
            {
            @ mysql_free_result( $this->Query_ID );
            $this->Query_ID = 0;
            }
        return $stat;
        } // end function nextRecord

//-------------------------------------------
//    Retrieves a single record
//-------------------------------------------
    function singleRecord()
        {
        $this->Record = mysql_fetch_array( $this->Query_ID );
        $stat = is_array( $this->Record );
        return $stat;
        } // end function singleRecord

//-------------------------------------------
//    Returns the number of rows  in a recordset
//-------------------------------------------
    function numRows()
        {
        return mysql_num_rows( $this->Query_ID );
        } // end function numRows
        
    } // end class Database
?> 

Use

<?php
// include the database class
include ('includes/database.php');

// create an instance of the Database class and call it $db
$db = new Database;

// do a query to retrieve a single record
$Query = "SELECT * FROM tablename LIMIT 1";
$db->query($Query);        // query the database
$db->singleRecord();    // retrieve a single record
echo $db->Record['Field_Name'];        // output a field value from the recordset

// do a query to retrieve multiple records
$Query = "SELECT * FROM tablename";
$db->query($Query);        // query the database
while ($db->nextRecord())
    {
    echo $db->Record['Field_Name']."<br>rn";        // output a field value from the recordset
    } // end while loop going through whole recordset
?> 
Source : http://www.tipsntutorials.com/tutorials/PHP/76