Interview Questions

1. File upoading with progress bar in PHP

        http://www.johnboy.com/php-upload-progress-bar/

2.curl posting xml max size and its config for third party website

3.jquery - code for apending the html element with existing "div" tag ?

   ex:

<div>

    <div>

                <input type="text" name="uid">

                <input type="radio" name="utype">

         </div>

</div>

How to append the one more html element within the "div" and before text box

Ans: $('div>div:eq(0)').prepend('html element')

4.class with function overloading

     class a{

        function sum(){

           // do something

       }

      

        function sum($arg1=0){

           // do something

       }

}

How do you differencitate above functions

Ans: Preferences are

   1. Argument length

   2. Argument data type

   3. Argument default value    

5.singelton Pattern

          

The Singleton ensures that there can be only one instance of a Class and provides a global access point to that instance. Singleton is a "Gang of Four" Creational Pattern.

The Singleton pattern is often implemented in Database Classes, Loggers, Front Controllers or Request and Response objects. 

6.zend URL rewriting

       Extending the router class and predispatch function we can do this

7. find out the internet connectivity is active for ajax call

 Ans:

      

$.ajax({
type: 'GET',
url: 'http://www.mywebapp.com/keepAlive',
timeout: 15000,
success: function(data) {},
error: function(XMLHttpRequest, textStatus, errorThrown) {}
})

8.search hacking

   1) Make sure magic quote is off and try with ' user input

   2)Try with cross site scripting

9.coding for confliting the files

         We can make use of some "svn" tools

10.error/exception handling methods

11.Memchache, DB comparision -> How to compare cache data and DB data is same or not?

12.Pagination while deleting the record?

13.How to keep tracking the display bulk of data ?

     - Use pagination

    - cache method

  

Triggers

 //After inserting the record

DROP TRIGGER IF EXISTS `fjm`.`updatePhtoVideoCountOnInsert`//
CREATE TRIGGER `fjm`.`updatePhtoVideoCountOnInsert` AFTER INSERT ON `fjm`.`fjm_files`
 FOR EACH ROW BEGIN
if NEW.modelName = 'listing' then
UPDATE fjm_listings b set photo_count = (SELECT COUNT(*)  FROM fjm_files l WHERE l.attachment_id = NEW.attachment_id  AND l.modelName = 'listing' GROUP BY l.attachment_id) WHERE b.id = NEW.attachment_id;
end if;
if NEW.modelName = 'listingvideos' then
UPDATE fjm_listings b set video_count = (SELECT COUNT(*)  FROM fjm_files l WHERE l.attachment_id = NEW.attachment_id  AND l.modelName = 'listingvideos' GROUP BY l.attachment_id) WHERE b.id = NEW.attachment_id;
end if;
END
//

//After update

DROP TRIGGER IF EXISTS `fjm`.`updatePhtoVideoCountOnUpdate`//
CREATE TRIGGER `fjm`.`updatePhtoVideoCountOnUpdate` AFTER UPDATE ON `fjm`.`fjm_files`
 FOR EACH ROW BEGIN
if NEW.modelName = 'listing' then
UPDATE fjm_listings b set photo_count = (SELECT COUNT(*)  FROM fjm_files l WHERE l.attachment_id = NEW.attachment_id  AND l.modelName = 'listing' GROUP BY l.attachment_id) WHERE b.id = NEW.attachment_id;
end if;
if NEW.modelName = 'listingvideos' then
UPDATE fjm_listings b set video_count = (SELECT COUNT(*)  FROM fjm_files l WHERE l.attachment_id = NEW.attachment_id  AND l.modelName = 'listingvideos' GROUP BY l.attachment_id) WHERE b.id = NEW.attachment_id;
end if;
END
//


//After delete
DROP TRIGGER IF EXISTS `fjm`.`updatePhtoVideoCountOnDelete`//
CREATE TRIGGER `fjm`.`updatePhtoVideoCountOnDelete` AFTER DELETE ON `fjm`.`fjm_files`
 FOR EACH ROW BEGIN
if OLD.modelName = 'listing' then
UPDATE fjm_listings b set photo_count = (SELECT COUNT(*)  FROM fjm_files l WHERE l.attachment_id = OLD.attachment_id  AND l.modelName = 'listing' GROUP BY l.attachment_id) WHERE b.id = OLD.attachment_id;
end if;
if OLD.modelName = 'listingvideos' then
UPDATE fjm_listings b set video_count = (SELECT COUNT(*)  FROM fjm_files l WHERE l.attachment_id = OLD.attachment_id  AND l.modelName = 'listingvideos' GROUP BY l.attachment_id) WHERE b.id = OLD.attachment_id;
end if;
END
//

zend Auth add more credential

   // Get our authentication adapter and check credentials
        $adapter = $this->_getAuthAdapter();
   
    //Need to add more credential for user
    $select = $adapter->getDbSelect();
        $select->where('status = 1 AND password is not null AND password!=""');
 $adapter->setIdentity($values['username']);
        $adapter->setCredential(md5($values['password']));

        $auth = Zend_Auth::getInstance();
        $result = $auth->authenticate($adapter);
        if ($result->isValid()) {
            $user = $adapter->getResultRowObject();
            $auth->getStorage()->write($user);
            return $this->_redirect('/sitemgr/pages');
        }



//or else we can use following simple code

$authAdapter->setTableName('register')
            ->setIdentityColumn('username')
            ->setCredentialColumn('pwd')
            ->setCredentialTreatment('? AND status = "active"');

Regular expression

[a-z] -> match any string
[a-z]+ -> strict match any string
^[a-z] -> srating  match any string
^[a-z]+ -> srating strict  match any string
^[a-z]? -> may be  match any string
a$  - end with a
^   - start
?
+
*   - all chars
.   - any single mchars
(join|update)

[^0-9] - not match 0 to 9
/d
/s
/S
::digits::


a$

Sample Triggers

DROP TRIGGER IF EXISTS `fjm`.`updateListingCountOnInsert`//

CREATE TRIGGER `fjm`.`updateListingCountOnInsert` AFTER INSERT ON `fjm`.`fjm_listings`

 FOR EACH ROW BEGIN

UPDATE fjm_classes b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1 AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id GROUP BY l.class_id);

UPDATE fjm_classes b set active_listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id AND l.sold='0' GROUP BY l.class_id);

UPDATE fjm_states b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id GROUP BY l.state_id);

UPDATE fjm_states b set active_listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id AND l.sold='0' GROUP BY l.state_id);

UPDATE fjm_breeds_classes bc set bc.listing_count = (SELECT count( * )+0 FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND bc.breed_id = lb.breed_id AND bc.class_id = l.class_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id, l.class_id);

UPDATE fjm_breeds b set b.listing_count = (SELECT COUNT(*)+0  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND b.id = lb.breed_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id);

END

//

DROP TRIGGER IF EXISTS `fjm`.`updateListingCountOnUpdate`//

CREATE TRIGGER `fjm`.`updateListingCountOnUpdate` AFTER UPDATE ON `fjm`.`fjm_listings`

 FOR EACH ROW BEGIN

UPDATE fjm_classes b set listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1 AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id GROUP BY l.class_id);

UPDATE fjm_classes b set active_listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id AND l.sold='0' GROUP BY l.class_id);

UPDATE fjm_states b set listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id GROUP BY l.state_id);

UPDATE fjm_states b set active_listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id AND l.sold='0' GROUP BY l.state_id);

UPDATE fjm_breeds_classes bc set bc.listing_count = (SELECT COUNT(*)  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND bc.breed_id = lb.breed_id AND bc.class_id = l.class_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id, l.class_id);

UPDATE fjm_breeds b set b.listing_count = (SELECT COUNT(*)  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND b.id = lb.breed_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id);

update `fjm_listing_breeds` lb  set lb.status = 0 where lb.listing_id = NEW.id;

update `fjm_listing_breeds` lb  set lb.status = 1 where lb.listing_id = NEW.id and NEW.status= 1 and NEW.is_paid = 1 and NEW.is_delete = 0;

END

//

DROP TRIGGER IF EXISTS `fjm`.`updateListingCountOnDelete`//

CREATE TRIGGER `fjm`.`updateListingCountOnDelete` AFTER DELETE ON `fjm`.`fjm_listings`

 FOR EACH ROW BEGIN

UPDATE fjm_classes b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1 AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id GROUP BY l.class_id);

UPDATE fjm_classes b set active_listing_count = (SELECT COUNT(*)+0   FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id AND l.sold='0' GROUP BY l.class_id);

UPDATE fjm_states b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id GROUP BY l.state_id);

UPDATE fjm_states b set active_listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id AND l.sold='0' GROUP BY l.state_id);

UPDATE fjm_breeds_classes bc set bc.listing_count = (SELECT COUNT(*)+0  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND bc.breed_id = lb.breed_id AND bc.class_id = l.class_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id, l.class_id);

UPDATE fjm_breeds b set b.listing_count = (SELECT COUNT(*)+0  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND b.id = lb.breed_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id);

END

//

Mysql DB Export via command prompt

C:\xampp\mysql\bin>mysqldump -uroot fjm  > test.sql

Mysql query optimization

Use "Explain" keyword to check the query runnning status
Ex: Explain select column_name from table_name where column_name = value