How to Delete record from different table with out using sub query using sql or mysql.

If i want to delete record from a single table i just need to run a simple delete command like below .

Query to delete single record :

DELETE FROM
    `first_table`   

         WHERE
              `my_value`='FooBar';  

Now suppose i want to delete record from two different table then every person first comes in there mind using join with delete command like below :

Query with join return error :

DELETE FROM
    `first_table`
          INNER JOIN `second_table` ON `second_table`.`id_column`=`first_table`.`id_column`
             WHERE
               `my_value`='FooBar'; 

But you know above query return an error something like below :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN `second_table` ON `second_table`.`id_column`=`my_table`.`id_column` WHERE `my_va' ne 1

The Solution :

Sticking with the query above we can get it to work with a simple amendment. All we need to do is put the name of the table we’re deleting from between the words ‘DELETE’ and ‘FROM’ like below.

Query with join  :

DELETE `first_table` FROM
    `first_table`
        INNER JOIN `second_table` ON `second_table`.`id_column`=`first_table`.`id_column`
         WHERE
           `my_value`='FooBar'; 

If you like this post don't forgot to leave a comment 

Chears  :)

What is a trigger ?

  Triggers are procedures that are stored in the database and are implicitly run, or fired,

  when something happens.

  Classification tree of triggers ?
 

     Ans: Triggers can be of following types  :

• DML triggers on tables.
• INSTEAD OF triggers on views.
• System triggers on DATABASE or SCHEMA: With DATABASE, triggers fire for each event for all users; with SCHEMA,triggers fire for each event for that specific user.

  But more convenient way of classifying triggers is on the basis of Events on which they are
  fired   :

• DML statements (DELETE, INSERT, UPDATE)
• DDL statements (CREATE, ALTER, DROP)
• Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)

            

Now Creating triggers : 8 common types of trigger are as follow :



a) Before Insert Trigger 


Syntax : 

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE INSERT
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example : 

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2),
  create_date date,
  created_by varchar2(10)
);

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing INSERT into table
   SELECT user INTO v_username
   FROM dual;
   
   -- Update create_date field to current system date
   :new.create_date := sysdate;
   
   -- Update created_by field to the username of the person performing the INSERT
   :new.created_by := v_username;
   
END;

When would i like to use and when not  ?

You can not create a BEFORE trigger on a view.

You can update the :NEW values.

You can not update the :OLD values.

 
B) After Insert Trigger 

Syntax 

CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER INSERT
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example :

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN
   
   -- Find username of person performing the INSERT into the table
   SELECT user INTO v_username
   FROM dual;
   
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity,
     cost_per_item,
     total_cost,
     username )
   VALUES
   ( :new.order_id,
     :new.quantity,
     :new.cost_per_item,
     :new.total_cost,
     v_username );
     
END;

When would i like to use and when not  ?

You can not create an AFTER trigger on a view.

You can not update the :NEW values.

You can not update the :OLD values.

 

C) Before Update Trigger

Syntax 

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE UPDATE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2),
  updated_date date,
  updated_by varchar2(10)
);

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing UPDATE on the table
   SELECT user INTO v_username
   FROM dual;
   
   -- Update updated_date field to current system date
   :new.updated_date := sysdate;
   
   -- Update updated_by field to the username of the person performing the UPDATE
   :new.updated_by := v_username;
   
END;

When would i like to use and when not  ?

You can not create a BEFORE trigger on a view.

You can update the :NEW values.

You can not update the :OLD values.

D) After Update Trigger

Syntax 

CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER UPDATE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing UPDATE into table
   SELECT user INTO v_username
   FROM dual;
   
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity_before,
     quantity_after,
     username )
   VALUES
   ( :new.order_id,
     :old.quantity,
     :new.quantity,
     v_username );
     
END;

When would i like to use and when not  ?

You can not create an AFTER trigger on a view.

You can not update the :NEW values.

You can not update the :OLD values.

E) Before Delete Trigger


Syntax

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE DELETE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example :

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing the DELETE on the table
   SELECT user INTO v_username
   FROM dual;
   
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity,
     cost_per_item,
     total_cost,
     delete_date,
     deleted_by )
   VALUES
   ( :old.order_id,
     :old.quantity,
     :old.cost_per_item,
     :old.total_cost,
      sysdate,
      v_username );
      
END;

When would i like to use and when not  ?

You can not create a BEFORE trigger on a view.

You can update the :NEW values.

You can not update the :OLD values.

 

F) After Delete Trigger 

Syntax 

CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER DELETE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Example :

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
   ON orders
   FOR EACH ROW

DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing the DELETE on the table
   SELECT user INTO v_username
   FROM dual;
   
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity,
     cost_per_item,
     total_cost,
     delete_date,
     deleted_by)
   VALUES
   ( :old.order_id,
     :old.quantity,
     :old.cost_per_item,
     :old.total_cost,
     sysdate,
     v_username );
     
END;

 
When would i like to use and when not :

You can not create an AFTER trigger on a view.

You can not update the :NEW values.

You can not update the :OLD values.

How to find current triggers in oracle :

 
Ans: Following data distcionary are present in oracle for  viewing triggers information  .

These are like tables so user can select from these tables to find information  .

• USER_TRIGGERS
• ALL_TRIGGERS
• DBA_TRIGGERS

Where do trigger stores ?

Ans: Triggers are stored in the data dictionary, within the SYSTEM tablespace. Their names and some properties are visible through views like dba_triggers (and all_triggers and user_triggers) and additionally through views dba_objects (and all_objects and user_objects) where object_type = 'TRIGGER'.

Is there any size limit on triggers  ?
Ans : The size of the trigger cannot be more than 32K.
According to the Oracle Reference guide, the limit is imposed by the tool executing the stored procedure or trigger, and can be between 2000 and 3000 lines. It does not say how many characters or bytes this equates to, although non-stored SQL statements are limited to 64K characters. (But this statement is  for oracle 9)


What is most efficient way of creating long  triggers  ?

Ans : The best way to write long triggers is to use them to call required procedures . They should be written to call procedures from packages which have procedures that do actual work

What points to be taken into consideration before planning to create trigger  ?

Ans : guidelines when designing your triggers:

• Use triggers to guarantee that when a specific operation is performed, related actions 
  
  are performed.
  
  
• Do not define triggers that duplicate features already built into Oracle Database. For 
  
  example, do not define triggers to reject bad data if you can do the same checking 
  
  through declarative integrity constraints.
  
  
• Limit the size of triggers. If the logic for your trigger requires much more than 60 lines of 
  
  PL/SQL code, it is better to include most of the code in a stored procedure and call the 
  
  procedure from the trigger.
  
  
• Use triggers only for centralized, global operations that should be fired for the triggering 
  
  statement, regardless of which user or database application issues the statement.
  
  
• Do not create recursive triggers. For example, creating an AFTER UPDATE statement 
  
  trigger on the Emp_tab table that itself issues an UPDATE statement on Emp_tab, causes the
  
  trigger to fire recursively until it has run out of memory.
  
  
• Use triggers on DATABASE judiciously. They are executed for every user every time the 
  
  event occurs on which the trigger is created.
  
     
  
  Your Comments are invaluable for us . Please provide them.  I like to welcome

          questions that you need to find answer on this topic .....

     

      Chears :)

      Happy coding guys ..

Check paragraph is in upper case or lower case

There is some situation when you need to check a whole paragraph is in upper case or lower case in php coding.

Below is simple function which will help you to solve your problem:

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

function checkCase($paragraph){
    if(isset($paragraph) && $paragraph!=""):
        $paragraph=preg_replace('/[^A-Za-z0-9\-]/'," ", $paragraph);
        $paraArr=array_filter(explode(" ",$paragraph));
        $totalword=count($paraArr);
        $capsCount=0;
        foreach($paraArr as $word):
            if(ctype_upper($word)):
                $capsCount++;          
            endif;
        endforeach;
            if($capsCount == $totalword):
            return true;
            else:
            return false;
            endif;
        return $capsCount;
    endif;
}


**************************FUNCTON CALLING **********************************

$testcase = "THIS IS PHPSOLLUTIONS.BLOGSPOT.IN TESTING PARAGRAPH TO CHECK THAT PARAGRAPH IS IN UPPER CASE OR LOWER CASE";

if(checkCase($testcase)){
echo "Paragraph is in Upper case.";
}else{
echo "Paragraph is in Lower case.";
}



Happy coding guys.
Chears  :)

Add "You might also like this" widgit on blogger or your website

In some blogs and websites you may have seen this ‘You might also like’ widget somewhere below the posts in the footer part. It will help you to keep your readers for some more time on your blog. Here in this tutorial I will take you through a set of instructions on how to add ‘You might also like’ widget for blogger or website like below preview .

Below is simple steps to add this widgit :

1- Step :  First of all go to linkwithin. website.

 2- Step : Fill the form with ,
 

a- Enter your email

b- Enter Your Bloglink (Your blog url with http://)

c- Choose the platform as 'Blogger'.

            d-    You can decide how many stories you want to show below posts, May be 
              set to 3,4 or 5.

e.      Now choose Light Dark Combination if your blog has light background then leave it blank otherwise check that Box.

f-  Click on 'Get Widget!' button

3-Step : Now you display another screne if you are selected Blogger platform in form like below preview .

4- Step : Now you just need simply click on Add Widget button .

5- Step : Now drag and drop to the bottom of the Blog Posts section like below preview .

You did it successfully ....... chears 




If you like this post so please appriciate it by post a comment ...:)

How to sort multidimentional array using php

Suppose you have an multidimentional array like below example and you want to sort that array from any index value 

Lets take an example - I have an array with car name, model and color index like below

 $cars = array
  (
  array('car'=>"Volvo",'model'=>"2014",'color'=>"blue"),
  array('car'=>"BMW",'model'=>"2014",'color'=>"red"),
  array('car'=>"Saab",'model'=>"2012",'color'=>"yellow"),
  array('car'=>"Land Rover",'model'=>"2013",'color'=>"green")
  );

Now i want to sort this array by car name in accending order. Below is a simple function.

function multi_sort($arr, $index) {
    $b = array();
    $c = array();
    foreach ($arr as $key => $value) {
        $b[$key] = $value[$index];
    }
    asort($b);
    foreach ($b as $key => $value) {
        $c[] = $arr[$key];
    }
    return $c;
}

In the above function you just need to pass two parameter -

1. Array that you want to sort.
2. Index from which value you want to sort.

Method of using -

$cars=multi_sort($cars, 'car');

echo "<pre>";

print_r($cars);die;

Output :

Array
(
    [0] => Array
        (
            [car] => BMW
            [model] => 2014
            [color] => red
        )

    [1] => Array
        (
            [car] => Land Rover
            [model] => 2013
            [color] => green
        )

    [2] => Array
        (
            [car] => Saab
            [model] => 2012
            [color] => yellow
        )

    [3] => Array
        (
            [car] => Volvo
            [model] => 2014
            [color] => blue
        )

)

If you like this post don't forgot to leave comment 

Happy coding :)

Chears  

Codeigniter Interview Question and Answers for 1-2 year experiance

1. What is codeigniter.

Codeigniter is open source , web application framework.Its is for building websites using php.Codeigniter is loosely based on MVC pattern. Most simple framework in php , which is you will easily learn.Its mostly known for its speed as compare to other frameworks in php.
Its goal is to enable you to develop projects much faster than you could if you were writing code from scratch, by providing a rich set of libraries for commonly needed tasks, as well as a simple interface and logical structure to access these libraries.

2. When and who developed codeigniter.

The first public version of CodeIgniter was released on February 28, 2006.

3. What is current version of codeigniter.

version 2.2.0 was released June 2014.The development version of codeigniter 3.0-dev was release on October 20, 2011.Preview version 3.0-dev, is certified open source software licensed with the Open Software License.

4. What are the features of codeigniter.

• Codeigniter is free to use,its an open source framework.
• Its light weight.The core system requires only a few very small libraries.Not like other frameworks that require heavy file libraries.
• CodeIgniter is Fast.Its faster than any other framework in php.
• The URLs generated by CodeIgniter are clean and search-engine friendly.You will change any url to what ever you want from files.
• CodeIgniter is Extensible.The system can be easily extended through the use of your own libraries, helpers, or through class extensions or system hooks.
• CodeIgniter Uses MVC(Model View Controller) which allows great separation between logic and presentation.
• CodeIgniter requires nearly zero configuration,does not require you to use the command line,not forced to learn a templating language.
• Full Featured database classes with support for several platforms,Security and XSS Filtering,Error Logging.

5. Explain Codeigniter File Structure.

When you download Codeigniter you will see the following folder structure :-

• application
  • cache
  • Config
  • Controllers
  • core
  • errors
  • helpers
  • hooks
  • language
  • libraries
  • logs
  • models
  • thirdparty
  • views
• system
  • core
  • database
  • fonts
  • helpers
  • language
  • libraries

6. Explain Application Flow Chart in codeigniter.

Application flow chart from Codeigniter documentaion :

• The index.php serves as the front controller, initializing the base resources needed to run CodeIgniter.
• The Router examines the HTTP request to determine what should be done with it.
• If a cache file exists, it is sent directly to the browser, bypassing the normal system execution.
• Security. Before the application controller is loaded, the HTTP request and any user submitted data is filtered for security.
• The Controller loads the model, core libraries, helpers, and any other resources needed to process the specific request.
• The finalized View is rendered then sent to the web browser to be seen. If caching is enabled, the view is cached first so that on subsequent requests it can be served.

 7. Explain MVC in Codeigniter.

Model–View–Controller (MVC) is an architecture that separates the representation of information from the user’s interaction with it.

Controller:- The Controller serves as an intermediary between the Model, the View. controller mediates input, converting it to commands for the model or view.
Model:-The Model represents your data structures. Typically your model classes will contain functions that help you retrieve, insert, and update information in your database.The model consists of application data and business rules.
View:-The View is the information that is being presented to a user. A View will normally be a web page.A view can be any output representation of data.
For more detail understanding MVC please read this article What is MVC(Model-View-Controller) Architecture.

8. What are the hooks in codeigniter.

CodeIgniter’s Hooks feature provides a means to tap into and modify the inner workings of the framework without hacking the core files.How ever you like to cause some action to take place at a particular stage in the execution process. In other word, hooks allow you to execute a script with a particular path within the Codeigniter.
The hooks feature can be globally enabled/disabled by setting the following item in the application/config/config.php file:

$config['enable_hooks'] = TRUE;

Hooks are defined in application/config/hooks.php file.For example

1	$hook['pre_controller'] = array(

2	'class'    => 'MyClass',

3	'function' => 'Myfunction',

4	'filename' => 'Myclass.php',

5	'filepath' => 'hooks',

6	'params'   => array('test', 'test1', 'webs')

7

);

In the above example hook 'pre_controller' is called hook point. There are many different types of hook point in Codeigniter which is below-

• post_controller_constructor
• pre_controller
• post_sytem
• pre_system
• cache_override
• display_override
• post_controller

9. How you will add or load an model in codeigniter.

Models will typically be loaded and called from within your controller functions. To load a model you will use the following function:

$this->load->model('Model_name');

10. What are the helpers in codeigniter.

Helpers, as the name suggests, help you with tasks. Each helper file is simply a collection of functions in a particular category.There are URL Helpers, that assist in creating links, there are Form Helpers that help you create form elements, Text Helpers perform various text formatting routines, Cookie Helpers set and read cookies, File Helpers help you deal with files, etc.
Loading a helper file is quite simple using the following function:

$this->load->helper('name');

11. How you will use or add codeigniter libraries.

All of the available libraries are located in your system/libraries folder. In most cases, to use one of these classes involves initializing it within a controller using the following initialization function:-

$this->load->library('class name');

12. How you will work with error handling in codeigniter.

CodeIgniter lets you build error reporting into your applications using the functions:-

• show_error():- This function will display the error message supplied to it using template application/errors/error_general.php.
• show_404() :- Function will display the 404 error message.
• log_message(‘level’, ‘message’) :- This function lets you write messages to your log files. You must supply one of three “levels” in the first parameter, indicating what type of message it is (debug, error, info), with the message itself in the second parameter.

13. What are the security parameter for XSS in CodeIgniter?

Codeigniter has got a cross-site scripting hack prevention filter. This filter either runs automatically or you can run it as per item basis, to filter all POST and COOKIE data that come across. The XSS filter will target the commonly used methods to trigger JavaScript or other types of code that attempt to hijack cookies or other malicious activity. If it detects any suspicious thing or anything disallowed is encountered, it will convert the data to character entities. Read More...

14. Explain how you can enable CSRF (Cross Site Request Forgery) in CodeIgniter? 

You can activate CSRF (Cross Site Request Forgery) protection in CodeIgniter by operating your application/config/config.php file and setting it to

$config [ ‘csrf_protection’] = TRUE;

If you avail the form helper, the form_open() function will insert a hidden csrf field in your forms automatically. Read More...

15. Explain how you can prevent CodeIgniter from CSRF?

There are several ways to protect CodeIgniter from CSRF, one way of doing is to use a hidden field in each form on the website.  This hidden field is referred as CSRF token; it is nothing but a random value that alters with each HTTP request sent. As soon as it is inserted in the website forms, it gets saved in the user’s session as well.  So, when the form is submitted by the users, the website checks whether it is the same as the one saved in the session. If it is same then, the request is legitimate. 
Read More...

16. Explain what is inhibitor in CodeIgniter? 

For CodeIgniter, inhibitor is an error handler class, using the native PHP functions like set_exception_handler, set_error_handler, register_shutdown_function to handle parse errors, exceptions, and fatal errors. 

17. Explain routing in Codeigniter? 

In CodeIgniter, the way PHP files served is different rather than accessing it directly from the browser. This process is called routing. Routing in CodeIgniter gives you freedom to customize the default URL pattern to use our own URL pattern according to the requirement. So, whenever there is a request made and matches our URL pattern it will automatically direct to the specified controller and  function. 

18. Why is there a need to configure the URL routes? 

Changing the URL routes has some benefits like

• From SEO point of view, to make URL SEO friendly and get more user visits
• Hide some URL element such as a function name, controller name, etc. from the users for security reasons
• Provide different functionality to particular parts of a system.

Please leave a comment if you like this post.

Chears :)
Best Of Luck For Your Interview.