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

Zend Custom validation

Cutom validation in zend

--------------------------------

1. Requirement is i have two fields and validate two fields are not null, user want give atleast one value

Proceed following steps for zend custom validation

-----------------------------------------

step1:

create the name space at application.ini file for pointing the custom library folder

autoloadernamespaces[] = "ZC"

ZC - Is my custom folder name at Library

Step2:

Create file with following directory

Library/ZC/validate/HornPercent.php

HornPercent.php

--------------------

<?php

/**

 * Zend Framework

 *

 * LICENSE

 *

 * This source file is subject to the new BSD license that is bundled

 * with this package in the file LICENSE.txt.

 * It is also available through the world-wide-web at this URL:

 * http://framework.zend.com/license/new-bsd

 * If you did not receive a copy of the license and are unable to

 * obtain it through the world-wide-web, please send an email

 * to license@zend.com so we can send you a copy immediately.

 *

 * @category   Zend

 * @package    Zend_Validate

 * @copyright  Copyright (c) 2005-2011 Zend Technologies USA Inc. (http://www.zend.com)

 * @license    http://framework.zend.com/license/new-bsd     New BSD License

 * @version    $Id: Identical.php 23775 2011-03-01 17:25:24Z ralph $

 */

/** @see Zend_Validate_Abstract */

require_once 'Zend/Validate/Abstract.php';

/**

 * @category   Zend

 * @package    Zend_Validate

 * @copyright  Copyright (c) 2005-2011 Zend Technologies USA Inc. (http://www.zend.com)

 * @license    http://framework.zend.com/license/new-bsd     New BSD License

 */

class ZC_Validate_HornPercent extends Zend_Validate_Abstract

{

    /**

     * Error codes

     * @const string

     */

    const SUM_EXCEED      = 'sumExceed';

    const SUM_LESS      = 'sumLess';

    /**

     * Error messages

     * @var array

     */

    protected $_messageTemplates = array(

        self::SUM_EXCEED      => "Sum of percetnage should not be grater than 100",

        self::SUM_LESS      => "Sum of percetnage should not be less than 100",

    );

    /**

     * @var array

     */

    protected $_messageVariables = array(

        'token' => '_tokenString'

    );

    /**

     * Original token against which to validate

     * @var string

     */

    protected $_tokenString;

    protected $_token;

    protected $_strict = true;

    /**

     * Sets validator options

     *

     * @param  mixed $token

     * @return void

     */

    public function __construct($token = null)

    {

        if ($token instanceof Zend_Config) {

            $token = $token->toArray();

        }

        if (is_array($token) && array_key_exists('token', $token)) {

            $this->setToken($token['token']);

        } else if (null !== $token) {

            $this->setToken($token);

        }

    }

    /**

     * Retrieve token

     *

     * @return string

     */

    public function getToken()

    {

        return $this->_token;

    }

    /**

     * Set token against which to compare

     *

     * @param  mixed $token

     * @return Zend_Validate_Identical

     */

    public function setToken($token)

    {

        $this->_tokenString = (string) $token;

        $this->_token       = $token;

        return $this;

    }

    /**

     * Defined by Zend_Validate_Interface

     *

     * Returns true if and only if a token has been set and the provided value

     * matches that token.

     *

     * @param  mixed $value

     * @param  array $context

     * @return boolean

     */

    public function isValid($value)

    {

        if(is_array($value)){

            $total = 0;

            foreach($value as $key => $valArr){

                $total += $valArr['percent'];

            }

        }

        if($total> 100){

            $this->_error(self::SUM_EXCEED);

            return false;

        }

        if($total < 100){

            $this->_error(self::SUM_LESS);

            return false;

        }       

        return true;

    }

}

-----------------------------------------------

Step 3:

Add following lies to zend form init function

$this->addElementPrefixPath('ZC_Validate',

                                    'ZC/Validate/',

                                    'validate');

step 4:

Add following validator for element

$this->getElement('horns')->setAllowEmpty(false)->addValidator('HornPercent', false, array('token' => 'horn_percent'));   

Mysql update table comparing with another table

update `fjm_listings` a, fjm_zipcodes b set a.search_title = if(b.zipcode_abbr = '',  CONCAT(trim(replace(replace(a.title, a.class_name, ''), '<br>', '')),'<br>', a.class_name,  ' ', b.state_abbr),
CONCAT(trim(replace(replace(a.title, a.class_name, ''), '<br>', '')), '<br>', a.class_name, ' ', b.zipcode_abbr, ' ', b.state_abbr)) where a.zipcode = b.zipcode