What is MYSQL Trigger?

A trigger is a rule that you put on a table which basically says, whenever you DELETE, UPDATE or INSERT something in this table, also do something else.

DROP TRIGGER IF EXISTS deleteUser;
DELIMITER |
CREATE TRIGGER deleteUser AFTER DELETE ON ul_logins
FOR EACH ROW BEGIN
  DELETE FROM userbase WHERE userID = old.id;
END;
|
DELIMITER ;

DELIMITER | 

CREATE TRIGGER `after_insert_cart_items`

AFTER INSERT ON `trigger_cart_items` 

FOR EACH ROW

    BEGIN

        INSERT INTO trigger_cart_log (cart_id, item_id)

        VALUES (NEW.cart_id, NEW.item_id);

    END

|
DELIMITER ;

DELIMITER | 

CREATE TRIGGER `after_update_cost`

    AFTER UPDATE ON `trigger_items_cost` FOR EACH ROW

    BEGIN

       UPDATE trigger_items

       SET price = (NEW.cost * 1.3)

       WHERE item_id = NEW.item_id;

    END

|
DELIMITER ;

CREATE TRIGGER `before_update_cost`

    BEFORE UPDATE ON `trigger_items_cost` FOR EACH ROW

    BEGIN

        IF NEW.cost < 50 THEN

            SET NEW.cost = 50;

        ELSEIF NEW.cost > 50 AND NEW.cost < 100 THEN

            SET NEW.cost = 100;

        END IF;

    END

What is the diffrent between HAVING and WHERE clause?

1. WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates).
2.   HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
3.   HAVING is used to check conditions after the aggregation takes place.
4.   The HAVING clause was added to SQL because the WHERE keyword could not be used with       aggregate functions.

The difference between the two is in the relationship to the GROUP BY clause:

• WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records.
• HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.

WHERE is applied as a limitation on the set returned by SQL; it uses SQL's built-in set oeprations and indexes and therefore is the fastest way to filter result sets. Always use WHERE whenever possible.

HAVING is necessary for some aggregate filters. It filters the query AFTER sql has retrieved, assembled, and sorted the results. Therefore, it is much slower than WHERE and should be avoided except in those situations that require it.

HAVING checks the condition on the query result already found. But WHERE is for checking condition while query runs.
Let me give an example to illustrate this. Suppose you have a database table like this.

 usertable{ int userid, date datefield, int dailyincome }

Suppose, the following rows are in table:

1, 2011-05-20, 100
1, 2011-05-21, 50
1, 2011-05-30, 10
2, 2011-05-30, 10
2, 2011-05-20, 20

Now, we want to get the userids and sum(dailyincome) whose sum(dailyincome)>100
If we write:
SELECT userid, sum(dailyincome) FROM usertable WHERE sum(dailyincome)>100 GROUP BY userid
This will be an error. The correct query would be:
SELECT userid, sum(dailyincome) FROM usertable GROUP BY userid HAVING sum(dailyincome)>100

How to print start in different format?

*
**
***
****
*****
******

<?php
function star($num){
    if($num==1){
        echo '*';
        return;
    }
    else{
        for($i=0; $i<$num; $i++){
            if($i==0){
                echo '*<br>';
            }
            else{
                for($j=0; $j<$i; $j++){
                    echo '*';
                }
                echo '*<br>';
            }
        }
    }
}
star(6);
?>

How to use PHP data object?

PHP Data Object/PDO TOC Step By Step Tutorial PHP Data Object is a Database Connection Abstraction Library for PHP 5.

What is PDO?

• a PHP5 extension written in a compiled language (C/C++)
• a Lightweight DBMS connection abstract library (data access abstraction library)
• 
  

Why PDO?

• Support great number of database systems supported by PHP
• You don't need rewriting of many line code for each database. Just write one and run anywhere
• Speed. PDO written in compiled language, PHP libraries (ADOdb, PEAR DB) written in an interpreted language
• Your software more easy to install. Do not need third party software
• 
  

Whenever you need PDO?

• You need portable application that support many database system
• You need speed

Connect to MySQL

<?php
/*** mysql hostname ***/$hostname = 'localhost';
/*** mysql username ***/$username = 'username';
/*** mysql password ***/$password = 'password';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database';
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }?>

Connect to Firebird

<?phptry {
    $dbh = new PDO("firebird:dbname=localhost:C:\Programs\Firebird\DATABASE.FDB", "SYSDBA", "masterkey");
    }  
catch (PDOException $e)
    {
    echo $e->getMessage();
    }?>

Connect to Oracle

<?phptry {
    $dbh = new PDO("OCI:", "username", "password")
    }
catch (PDOException $e)
    {
    echo $e->getMessage();
    }?>

Connect to SQLite

<?phptry {
    /*** connect to SQLite database ***/
    $dbh = new PDO("sqlite:/path/to/database.sdb");
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }?>

How to create MYSQL indexes?

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
While creating index, it should be considered that what are the columns which will be used to make SQL queries and create one or more indexes on those columns.
Practically, indexes are also type of tables, which keep primary key or index field and a pointer to each record into the actual table.
The users cannot see the indexes, they are just used to speed up queries and will be used by Database Search Engine to locate records very fast.
INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to insert or update index values as well.

Simple and Unique Index:

You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Here is the syntax to create an Index on a table

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

You can use one or more columns to create an index. For example, we can create an index on tutorials_tbl using tutorial_author.

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

You can create a simple index on a table. Just omit UNIQUE keyword from the query to create simple index. Simple index allows duplicate values in a table.

If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name.

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)

There are four types of statements for adding indexes to a table:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): This statement creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): This adds an ordinary index in which any value may appear more than once.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): This creates a special FULLTEXT index that is used for text-searching purposes.

How to create view in mysql?

A database view is a virtual table or logical table which is defined as a SQL query with joins. Because a database view is similar to a database table, which consists of rows and columns, so you can query data against it. Most database management systems, including MySQL, allows you to update data in the underlying tables through the database view with some prerequisites. 

A database view is dynamic because it is not related to the physical schema. The database system stores database views as a SQL Select statement with joins. When the data of the tables changes, the view reflects that changes as well.

Advantages of database view

 The following are advantages of using database views.

• A database view allows you to simplify complex queries: a database view is defined by an SQL statement that associates with many underlying tables. You can use database view to hide the complexity of underlying tables to the end-users and external applications. Through a database view, you only have to use simple SQL statements instead of complex ones with many joins.
• A database view helps limit data access to specific users. You may not want a subset of sensitive data can be queryable by all users. You can use database views to expose only non-sensitive data to a specific group of users.
• A database view provides extra security layer. Security is a vital part of any relational database management system. Database views provides extra security for a database management system. A database view allows you to create only read-only view to expose read-only data to specific users. Users can only retrieve data in read-only view but cannot update it.
• A database view enables computed columns. A database table should not have calculated columns however a database view should. Suppose in the orderDetails table you have quantityOrder (the number of ordered products) and priceEach (price per product item) columns. However the orderDetails table does not have computed column to store total sales for each line item of the order. If it has, the database schema would not be a good design. In this case, you can create a computed column named total, which is a product of quantityOrder and priceEach to store the computed result. When you query data from the database view, the data of the computed column is calculated on fly.
• Database view enables backward compatibility. Suppose you have a central database, which many applications are using it. One day you decided to redesign the database to adapt with the new business requirements. You remove some tables and create several new tables, and you don’t want the changes affect other applications. In this scenario, you can create database views with the same schema as the legacy tables that you’ve removed.

Disadvantages of database view

 Besides the advantages above, there are several disadvantages of using database views:

• Performance: querying data from a database view can be slow especially if the view is created based on other views.
• Tables dependency: you create view based on underlying tables of the a database. Whenever you change the structure of those tables that view associates with, you have to change the view as well.

Some rules to create updatable view:

• The SELECT statement must only refer to one database table.
• The SELECT statement must not use GROUP BY or HAVING clause.
• The SELECT statement must not use DISTICT in the column list of the SELECT clause.
• The SELECT statement must not refer to read-only views.
• The SELECT statement must not contain any expression (aggregates, functions, computed columns…)

Create a simple view

CREATE VIEW student_details
   AS SELECT s.id, s.name sname, d.name dname, c.name cname
FROM student AS s
INNER JOIN district AS d ON s.district = d.id
INNER JOIN course AS c ON s.course = c.id;

Create view with JOIN

CREATE VIEW customerOrders AS

SELECT  D.orderNumber,

         customerName,

         SUM(quantityOrdered * priceEach) total

FROM orderDetails D

INNER JOIN orders O ON O.orderNumber = D.orderNumber

INNER JOIN customers C ON O.customerNumber =  C.customerNumber  

GROUP BY D.orderNumber

ORDER BY total DESC;

Create view with subquery

CREATE VIEW vwProducts  AS

SELECT productCode,

        productName,

        buyPrice

FROM products

WHERE buyPrice > (

      SELECT AVG  (buyPrice)

      FROM  products

)

ORDER BY buyPrice DESC

 

 

How to check the internet connection using PHP function fsockopen?

<?php
//function to check if the local machine has internet connection
function checkConnection()
{
    //Initiates a socket connection to www.itechroom.com at port 80
    $conn = @fsockopen("www.itechroom.com", 80, $errno, $errstr, 30);
    if ($conn)
    {
        $status = "Connection is OK";
        fclose($conn);
    }
    else
    {
        $status = "NO Connection<br/>\n";
        // $status .= "$errstr ($errno)";
    }
    return $status;
}

echo checkConnection();
?>

How to print string in reverse order using recursion?

function reverse_recursion($str){
    if(strlen($str)==1)
        return $str;
    else{
        $len = strlen($str);
        return ucwords(reverse_recursion(substr($str,1,strlen($str)-1)).$str[0]);
    }
}

echo reverse_recursion('Kumar Abhimanyu');

How to print string in reverse order without recursion?

function reverse($str){
    $rev = NULL;
    for($i=strlen($str)-1; $i>=0; $i--)
        $rev.=$str[$i];
        return ucwords(strtolower($rev));
}
echo reverse('Kumar Abhimanyu');

How to print facorial in PHP using recursion?

function fact($n){
    if($n==1)
        return $n;
    else
        return fact($n-1)*$n;
}

echo fact(4);

How tp print Fibonacci Series in PHP?

function fab($max=null){
    $a = 0;
    $b = 1;
    $count = 0;
    echo $a.', ';
    echo $b.', ';
    while($count < $max){
        $c = $b + $a;
        echo $c.', ';
        $a = $b;
        $b = $c;
        $count++;
    }
}
echo fab(22);

It will print up to22.