SQL Queries - Delete, Insert & Trigger

Delete from multi table - Interconnect:

DELETE a.*, b.*, qz.* FROM tbl_question a LEFT JOIN tbl_answer b ON b.question_id = a.id LEFT JOIN tbl_question_quiz as qz ON qz.question_id=a.id WHERE a.id = 13 and qz.quiz_id=15

Insert Answer in 'tbl_answer' table:

INSERT INTO tbl_answer (question_id, title, is_correct) SELECT '32' AS question_id,title,is_correct FROM tbl_answer WHERE question_id=1

Trigger:

$trigger_chk = mysql_query('SELECT COUNT(*) as chk_row FROM information_schema.triggers WHERE trigger_name="before1_quiz_delete";',$j4g_db);   $t_chk = @mysql_fetch_assoc($trigger_chk);   if ($t_chk['chk_row'] == 0) {     $trigger_query = "     CREATE TRIGGER `before_quiz_delete` BEFORE DELETE ON `tbl_quiz` FOR EACH ROW     DELETE qq, q, qu, a FROM tbl_question_quiz AS qq     LEFT JOIN tbl_question AS q ON q.id=qq.question_id     LEFT JOIN tbl_answer AS a ON a.question_id=qq.question_id     LEFT JOIN tbl_quiz_user AS qu ON qu.quiz_id = qq.quiz_id     WHERE qq.quiz_id = OLD.id;";       mysql_query($trigger_query,$j4g_db); }

Yii Syntax

Getting errors

$model->validate(); $errores = $model->getErrors(); p($errores);

Set Flash Message

//Controller Yii::app()->user->setFlash("success", "Thanks for Vendor Regisration.!");   //View <?php foreach (Yii::app()->user->getFlashes() as $key => $message) {     echo '<div class="flash-' . $key . '">' . $message . "</div>\n"; } ?>

Form:

<?php    $form = $this->beginWidget('CActiveForm', array(      'id' => 'create_message',      'enableAjaxValidation' => false,      'action' => CController::createUrl('messages/createmessage?id='.$id),      'enableClientValidation' => true,      'clientOptions' => array(        'validateOnSubmit' => true,      //        'validateOnChange'=>true,      //        'validateOnType'=>false,      ),    ));    ?>      <?php $this->endWidget(); ?>

Submit Button

<?php echo CHtml::submitButton('Cancel'); ?>

Javascript function call from the begin widget after the validation of Yii

<?php    $form = $this->beginWidget('CActiveForm', array(      'id' => 'venue-liquor-form',      'action' => 'addliquor?vid=' . $vid,      'enableAjaxValidation' => false,      'enableClientValidation' => true,      'clientOptions' => array(        'validateOnSubmit' => true,        'afterValidate' => 'js:checkErrors'      ),      'stateful' => false,      'htmlOptions' => array('enctype' => 'multipart/form-data'),    ));    ?>      <?php $this->endWidget(); ?>      // javascript function    function checkErrors(form, data, hasError) {     if(hasError!=true){       }    }

Ajax Call in Javascript

$.ajax({     type: 'GET',     url: '<?php echo CController::createUrl('messages/AjaxMessage'); ?>',     asyc: false,     data: {'c': c},     success: function(data) {      if(data){       $('#v_more_div').before(data);       $('#more_count').val(parseInt($('#more_count').val())+1);      }else{       $('#v_more_div').remove();      }     },     beforeSend: function() {      jQuery('.inner_tab_contant').addClass('loading');     },     complete: function() {      jQuery('.inner_tab_contant').removeClass('loading');     }    });

Ajax call in view file (in PHP tag)

<?php    echo CHtml::ajaxlink('Show All', array('artist/statusactive'), array(     "type" => "GET",     "data" => array("type" => '-1', "vid" => $vid, 'value'=>'js:jQuery("#search_value").val()'),     "success" => "function(data){           $(document).off('click.yiiGridView');           var value=jQuery('#search_value').val();      jQuery('.manage_floor').html(data);           jQuery('#search_value').val(value);           jQuery('#search_type').val('-1');      }",     "beforeSend" => "function(){ jQuery('.manage_floor').addClass('loading');   }",     "complete" => "function(){ jQuery('.manage_floor').removeClass('loading'); }",      ), array('live' => false, 'class' => 'blue', 'id' => rand(0, 99999)));    ?>

Radio Button in Ajax

<?php echo $form->radioButtonList($model, 'changed_by', array(0=>'Events',1=>'Time'),    array('separator'=>'', 'labelOptions'=>array('style'=>'display:inline'), "async"=>false,    'onclick' =>CHtml::ajax(array("type"=>"GET",    "url"=>array("floor/floorby"),    "data"=>array('test'=>'js:this.value','vid'=>$vid,'id'=>$id),    "update"=>"#changev",    "complete"=>"function() {  $('.datepick').datetimepicker({dateFormat: 'yy-mm-dd'});}"   )))); ?>

Create Link using Yii Syntax

<?php echo CHtml::link("Delete","javascript:void(0)",array('class'=>'blue','onclick'=>'deletemessage('.$data["id"].')') ); ?>     CHtml::link() method     <?php echo CHtml::link('Link Text',array('controller/action')); ?>     <?php echo CHtml::link('Link Text',      array('controller/action',       'param1'=>'value1',       'param2'=>'value2',       'param3'=>'value3')); ?>     <?php echo CHtml::link('Link Text',     array('controller/action',        'param1'=>'value1'), array('target'=>'_blank'); ?>     Different Module :   <?php echo CHtml::link('Link Text',array('/module-id/controller/action')); ?>     Same Module :   <?php echo CHtml::link('Link Text',array('/{$this->module->id}/controller/action')); ?>     Linking to   <?php echo CHtml::link('Delete',"#", array("submit"=>array('delete', 'id'=>$data->ID), 'confirm' => 'Are you sure?')); ?>

Create URL

<?php echo CController::createUrl('messages/createmessage?id='.$id); ?>   <?php echo CController::createUrl('messages/createmessage',array('id'=>$id)); ?>

Grid update

$.fn.yiiGridView.update("artist-grid");

Grid Status and Action(Insert/Delete)

array(    'name' => 'Status',    'type' => 'raw',    'value' => 'CHtml::tag("div",  array("style"=>"text-align: center;cursor:pointer" ,"onclick"=>"statuschange({$data["id"]})","class"=>"down-link","id" => "{$data["id"]}","href"=>"javascript:void(0);") ,        CHtml::tag("img", array( "src" => UtilityHtml::getStatusImage(GxHtml::valueEx($data, \'status\'))))       )',    'htmlOptions' => array('width' => '50px')   ),   array(    'name' => 'Action',    'type' => 'raw',    'value' => ' CHtml::tag("div",  array("style"=>"float: left; margin:5px; cursor:pointer" ,"onclick"=>"updateartist({$data["id"]})","id" => "{$data["id"]}","href"=>"javascript:void(0);") ,           CHtml::tag("img", array( "src" => "' . Yii::app()->request->baseUrl . '/images/update.png"))       ). CHtml::tag("div",  array("style"=>"float: left; margin:5px; cursor:pointer" ,"onclick"=>"deleteartist({$data["id"]})","id" => "{$data["id"]}","href"=>"javascript:void(0);") ,           CHtml::tag("img", array( "src" => "' . Yii::app()->request->baseUrl . '/images/delete.png"))       )',   ),

jQuery Validation

<script type="text/javascript" vsrc="/redkey/js/jquery.validate.js"></script>     <script>    $(document).ready(function(){       $("#venue-set-price-change-from").validate(           {     rules: {      "VenueManageEventfloor[rk_event_id]": "required",                           "VenueManageEventfloor[start_date_time]": "required",                           "VenueManageEventfloor[end_date_time]": "required"     },     messages: {      "VenueManageEventfloor[rk_event_id]": "Please Select Event name.",                           "VenueManageEventfloor[start_date_time]": "Please Select Event Start date.",                           "VenueManageEventfloor[end_date_time]": "Please Select Event date."     }    }       );     });   </script>     <?php echo $form->textField($model, 'change_price', array("class"=>"required number",'onkeypress' => 'return numbersonly(event)','value'=>$model->change_price)); ?>

Flash Message

$('#msg1').html('<div class=\"flash-success margin_r15\">Record save Successfully.</div>').fadeIn();   setTimeout( 'jQuery(\'#msg1\').fadeOut();',2000);

If Condition In Grid

array(     'name' => 'Event Name',     'type' => 'raw',     'value' => '($data["event_name"]!="")?CHtml::link       ($data["event_name"],array("//event/detail","id"=>$data["id"],"subeventid"=>         $data["date_id"])):"No Event."',    ),

CHtml::textField() Method

<?php echo CHtml::textField('Text', 'some value',    array('id'=>'idTextField',          'width'=>100,          'maxlength'=>100); ?>

List Data

CHtml::listData() method   <?php        /*you can use here any find method you think        proper to return your data from db*/        $models = categories::model()->findAll();          // format models resulting using listData         $list = CHtml::listData($models,                   'category_id', 'category_name');           print_r($list); ?>

CHtml::dropDownList() Method

<?php echo CHtml::dropDownList('listname', $select,                 array('M' => 'Male', 'F' => 'Female'),                 array('empty' => '(Select a gender)'));

Using Data From a Model Function

At model:   public function getGenderOptions(){       return array('M' => 'Male', 'F' => 'Female');   }   At view:   <?php echo CHtml::dropDownList('listname', $select,                 $model->genderOptions,                 array('empty' => '(Select a gender'));   Using data from db     // retrieve the models from db   $models = categories::model()->findAll(                    array('order' => 'category_name'));     // format models as $key=>$value with listData   $list = CHtml::listData($models,                   'category_id', 'category_name');     <?php echo CHtml::dropDownList('categories', $category,                 $list,                 array('empty' => '(Select a category'));

Reading Record

// find the first row satisfying the specified condition       $post=Post::model()->find($condition,$params);     // find the row with the specified primary key      $post=Post::model()->findByPk($postID,$condition,$params);     // find the row with the specified attribute values      $post=Post::model()->findByAttributes($attributes,$condition,$params);     // find the first row using the specified SQL statement      $post=Post::model()->findBySql($sql,$params);       // find all rows satisfying the specified condition      $posts=Post::model()->findAll($condition,$params);     // find all rows with the specified primary keys      $posts=Post::model()->findAllByPk($postIDs,$condition,$params);     // find all rows with the specified attribute values      $posts=Post::model()->findAllByAttributes($attributes,$condition,$params);     // find all rows using the specified SQL statement      $posts=Post::model()->findAllBySql($sql,$params);     // get the number of rows satisfying the specified condition      $n=Post::model()->count($condition,$params);     // get the number of rows using the specified SQL statement      $n=Post::model()->countBySql($sql,$params);     // check if there is at least a row satisfying the specified condition      $exists=Post::model()->exists($condition,$params);

Updating Record

$post=Post::model()->findByPk(10);      $post->title='new post title';      $post->save(); // save the change to database   // update the rows matching the specified condition        Post::model()->updateAll($attributes,$condition,$params);       // update the rows matching the specified condition and primary key(s)      Post::model()->updateByPk($pk,$attributes,$condition,$params);     // update counter columns in the rows satisfying the specified conditions      Post::model()->updateCounters($counters,$condition,$params);

Sql Query in Yii

$criteria = new CDbCriteria;   $criteria->select = "t.*";   $criteria->join =   "";   $criteria->condition = "";   $criteria->addCondition("");   $criteria->group = "t.id";   $criteria->order = "t.id";   $criteria->limit = "10";   $resultSet = VenueEvent::model()->findAll($criteria);

If condition in YII & MySql

$criteria = new CDbCriteria;   $criteria->select = "t.*,IF (t.rk_venue_id = ".$vid." and t.rk_venue_id= tu.venue_id and t.default_price_option='default',tu.table_price, t.other_price ) as price";   $criteria->join = ",rk_venue_default_price as tu";   $criteria->condition = "t.rk_venue_id = ".$vid;   $criteria->addCondition("t.rk_venue_id= tu.venue_id");   if(isset( $status) && $status != "-1"){     $criteria->addCondition('t.status="'.$status.'" '); }else{      $criteria->addCondition('t.status!="2"'); }   if(isset($keyword) && !empty ($keyword)) {     $criteria->addCondition('t.table_number LIKE "%'.$keyword.'%" '); }