How to change the root password for MySQL in XAMPP

Method 1: reset XAMPP MySQL root password through web interface:

After you started your XAMPP server, go to the browser and type the URL http://localhost/security/ (incase you’ve modified XAMPP server port, you need to include that port number also in previous URL). The security page will be shown where you can change the root password for MySQL. This will update the phpMyAdmin config also.

Method 2: reset XAMPP MySQL root password through SQL update:

1. Start the Apache Server and MySQL instances from the XAMPP control panel.
2. After the server started, open any web browser and give http://localhost:8090/phpmyadmin/ (if you are running XAMPP on 8090 port). This will open the phpMyAdmin interface. Using this interface we can manager the MySQL server from the web browser.
3. In the phpMyAdmin window, select SQL tab from the right panel. This will open the SQL tab where we can run the SQL queries.
4. Now type the following query in the textarea and click Go
   UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root'; FLUSH PRIVILEGES;
5. Now you will see a message saying that the query has been executed successfully.
6. If you refresh the page, you will be getting a error message. This is because the phpMyAdmin configuration file is not aware of our newly set root passoword. To do this we have to modify the phpMyAdmin config file.
7. Open the file [XAMPP Installation Path] / phpmyadmin / config.inc.php in your favorite text editor.
8. Search for the string $cfg\['Servers'\]\[$i\]['password'] = ''; and change it to like this, $cfg\['Servers'\]\[$i\]['password'] = 'password'; Here the ‘password’ is what we set to the root user using the SQL query.
9. Now all set to go. Save the config.inc.php file and restart the XAMPP server.

Method 3: reset XAMPP MySQL root password through Command Prompt

Set the mysql/bin folder path to the environment variables $path and $temp in computer

Run the mysql in xamp server.

and execute the following command in command prompt (Don't forget to choose 'Run as Administrator' option while selecting command prompt)

c:\>mysqladmin.exe -u root password root_password (provide your root_password).

All set, now connect to mysql using the following command

c:\>mysql -u root -p

Enter password: *********

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.4.20-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> > show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| phpmyadmin         |

| test               |

+--------------------+

5 rows in set (0.023 sec)

The above command shows list of databases in mysql. 

MySQL Incorrect datetime value: '0000-00-00 00:00:00' Problem rectification Error 1292 (22007) in mysql

Solution for  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

(on MySQL 5.7.13).

I kept getting the Incorrect datetime value: '0000-00-00 00:00:00' error.

Strangely, this worked: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. I have no idea why the former fails and the latter works... maybe a MySQL bug?

At any case, this UPDATE query worked:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'

All the best !!!

Change the root password for MySQL in XAMPP

By default, when you install XAMPP in your windows machine, the root password for the MySQL is set to empty. But this is not recommended, as the MySQL database without a password will be accessible to everyone. To avoid this, a proper/secure password must be set to the user root. To do it in XAMPP, there are two ways.

Method 1: reset XAMPP MySQL root password through web interface:

After you started your XAMPP server, go to the browser and type the URL http://localhost/security/ (incase you’ve modified XAMPP server port, you need to include that port number also in previous URL). The security page will be shown where you can change the root password for MySQL. This will update the phpMyAdmin config also.

Method 2: reset XAMPP MySQL root password through SQL update:

1. Start the Apache Server and MySQL instances from the XAMPP control panel.
2. After the server started, open any web browser and give http://localhost:8090/phpmyadmin/ (if you are running XAMPP on 8090 port). This will open the phpMyAdmin interface. Using this interface we can manager the MySQL server from the web browser.
3. In the phpMyAdmin window, select SQL tab from the right panel. This will open the SQL tab where we can run the SQL queries.
4. Now type the following query in the textarea and click Go
   UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root'; FLUSH PRIVILEGES;
5. Now you will see a message saying that the query has been executed successfully.
6. If you refresh the page, you will be getting a error message. This is because the phpMyAdmin configuration file is not aware of our newly set root passoword. To do this we have to modify the phpMyAdmin config file.
7. Open the file [XAMPP Installation Path] / phpmyadmin / config.inc.php in your favorite text editor.
8. Search for the string $cfg\['Servers'\]\[$i\]['password'] = ''; and change it to like this, $cfg\['Servers'\]\[$i\]['password'] = 'password'; Here the ‘password’ is what we set to the root user using the SQL query.
9. Now all set to go. Save the config.inc.php file and restart the XAMPP server.

Change Date Format From YYYY-MM-DD to DD-MM-YYYY also getting yesterday date for particular date in PHP. Retriving the day record from MySql

//////////////////////////////// GET LAST DATE OR YESTERDAYS /////////////////////////////////////////////////////////

$dt=date('d/m/Y');      // Gives todays date in dd/mm/yyyy format let it be my today's post publishing date i.e., 09/11/2017
 
$lastdate=0;               // Lets assusme lastdate is zero, as let x=0 
 
$vardate = str_replace('/', '-', $dt);   /* To convert the format we should first change our date as only digits, i.e., without special symbols  now vardate=09112017 */
 
$formatdt=date("Y-m-d",strtotime($vardate));  /* Now formatdt = 2017-11-09, dont miss strtotime function, missing it gives weird result such as 1970-01-01. */
 
$prevdt=DateTime::createFromFormat('Y-m-d',$formatdt);  /* Create a DateTime object, I tried several ways like date('d/m/Y',strtotime("-1 days"))  etc., but nothing worked well. This procedure delivered perfect result */
 
$prevdt->modify('-1 days'); 
/* Now we have current date in our object , we can do whatever we want like 

$start_date = '2013-03-06';
$date = DateTime::createFromFormat('Y-m-d',$start_date);

$date->modify('+1 month');
echo $date->format('Y-m-d');//2013-04-06

$date->modify('+4 year');
echo $date->format('Y-m-d');//2017-04-06

$date->modify('+6 day');
echo $date->format('Y-m-d');//2017-04-12

$date->modify('+24 hours');
echo $date->format('Y-m-d');//2017-04-13

$date->modify('-7 years');
echo $date->format('Y-m-d'); //2010-04-13

$date->modify('-18 months');
echo $date->format('Y-m-d'); //2008-10-13 

etc. 

Here our case is to get previous date so I used '-1 days' . Hope you understood */

$yesterdaydate=$prevdt->format('Y-m-d');  /* Obtaining previous date in yyyy-mm-dd format for database query retrieval purpose, Mysql date format is yyyy-mm-dd H:M:S */

$ydt=date('d/m/Y',strtotime($yesterdaydate));  // Converting previous date our reading format i.e., dd/mm/yyyy
 
$chkexists = mysql_result(mysql_query("SELECT IFNULL(MAX(id),0) FROM repdone WHERE tdate='$ydt'"),0);  /* Checking whether database entry exists in selected table on this particular date. This is case of daily reports. */
 
if($chkexists == 0){
  // CASE PREVIOUS DAY IS HOLIDAY FOR SELECTED DATE
 
  $chkdt=date('Y-m-d H:i:s',strtotime($yesterdaydate));
 /* GETTING previous date in mysql date format, note that we are using $yesterdaydate variable which is in yyyy-mm-dd format previous date, this format is best acceptable format for all coding languages but the users need readable format i.e., dd-mm-yyyy or dd/mm/yyyy format. That's why both formats are stored in separate variables as well as both formats are stored in database also. */

  $lastdate = mysql_result(mysql_query("SELECT IFNULL(MAX(repdate),0) FROM dailystatistics where repdate <= '$chkdt'"),0);
   // Getting Last maximum date  below the selected date from database through query
 
if($lastdate==0) {
  // In case database query fails. This code doesn't execute but for safe side it is written due to date format problems
 
  $chkrep = mysql_result(mysql_query("SELECT IFNULL(id,0) FROM repdone WHERE tdate='$dt'"),0); 
  /* If Todays  Record inserted then the value is 1 to get yesterdays date otherwise zero gives max id for yesterdays date */
  $lastrepid =  mysql_result(mysql_query("SELECT IFNULL(MAX(id),0) FROM repdone where id<$chkrep"),0);  // Getting just below id of selected date record
  if($lastrepid > 0) {
      $lastdate=mysql_result(mysql_query("SELECT IFNULL(tdate,0) FROM repdone where id='$lastrepid'"),0); // getting the date of the previous record id
  }
}

if($lastdate!=0)
  $vardate = str_replace('/', '-', $lastdate);  // Again for converting the date to dd/mm/yyyy format we need to replace the special symbols
  $ydt=date("d/m/Y",strtotime($vardate));  // Now we have previous date of selected date ($dt) in format dd/mm/yyyy is in $ydt variable
 
 } // End of chkexists


////////////////////////////////////////////////////////////////////////////////////////////////////////

Delete either first set of rows or last set rows in MYSQL in dynamic fashion

DELETE FROM table <condition> ORDER BY the field DESC|ASC limit 100

for first 100,

DELETE FROM table <condition if required> ORDER BY <field> ASC limit 100

and for last 100,

DELETE FROM table <condition if required> ORDER BY <field> DESC limit 100

CGridView Multiple rows update , Bulk Update of records in YII

By keeping a simple check box before each record in CGridView we can achieve bulk update of records. User can check the selected records and click on UpdateALL button , this is very useful when multiple records having same data to update. Here in this case we are having File Monitoting System . The Inward section receives more than 200 files daily and in which some files belongs to same section with same PR Number so instead of updating each file and giving the file number and forwarding to section, User can simply select the files and update the selected files at once.

1. To achieve above, first we design the view part. So in the admin view of the main table (here say it      is files) then the path would be >application_folder/protected/views/files/admin.php , Open the
     file in which we have a CGridView defined already

     First note the user about this simple action, the below statements is for that purpose only

   <b><font color="red"> Bulk Update: </font> Select same file number inward list by clicking the    check boxes beside inward numbers and then click new button "Update ALL" give all the     
   required fields and click save </b> 

   Then we need a Button with label "Update ALL" in our view,
   
   <?php
            $baseUrl = Yii::app()->baseUrl;
            $imgpath = $baseUrl.'/images/new.gif';
    ?>
   <div class="row buttons">
    <center>
        <table border='0'><tr>
             <td> <img src="<?php echo $imgpath ?>" /></td>
             <td><?php echo CHtml::button('Update 
                                           ALL',array('name'=>'btnupdateall','class'=>'updateall-button')); ?>
            </td>
         </tr></table>
    </center>
    </div>

   Now we need to register a client script (JQuery script) for the button, Please add the following underlined lines to the script.
 <?php
       Yii::app()->clientScript->registerScript('search', "
        $('.search-button').click(function(){
$('.search-form').toggle();
return false;
      });
      $('.search-form form').submit(function(){
p$('#inward-grid').yiiGridView('update', {
data: $(this).serialize()
});
return false;
      });
      $('.updateall-button').click(function(){
       var atLeastOneIsChecked = $('input[name=\"inward-grid_c0[]\"]:checked').length > 0;
        if (!atLeastOneIsChecked)
        {
                alert('Please select atleast one inward number to Update');
        }
        else if (window.confirm('Are you sure you want to Update ALL the selected inward files?'))
        {
                document.getElementById('inward-search-form').action='index.php?r=inward/updateall';
                document.getElementById('inward-search-form').submit();
        }
      });
      ");

  ?>

and Now in columns of the CGridView  add a check box before the file number field

<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'inward-grid',
'selectableRows'=>2,   // Two means many 0 means none
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
        array(
                    'value'=>'$data->inw_no',
                    'class'=>'CCheckBoxColumn',
                    ),
'inw_no',

Now the check box is added to the gridview.

2. The Action Part:  We have defined the action for the UpdateALL button in our view now we have        to write the action in our controller i.e., inward controller. (note the above highlighted text
      action=index.php?r=inward/updateall)

     ////////////////////////////////// UPDATE ALL ////////////////////////////////////////////////////

     public function actionUpdateall()
    {
   $session=new CHttpSession;
        if (isset($_POST['inward-grid_c0']))
        {
               $session->open();
               $session['sel'] = $_POST['inward-grid_c0'];
        }
       
       // if(isset($_POST['inward-grid_c0']))
        if(isset($session['sel']))
        {
                $upd_inws = $session['sel'];
// $upd_inws = $_POST['inward-grid_c0'];
                $uname=Yii::app()->user->name;
                $baseUrl = Yii::app()->baseUrl;
                $cs = Yii::app()->getClientScript();

                $cs->registerScriptFile($baseUrl.'/js/utility.js');
                $cs->registerScriptFile($baseUrl.'/js/validations.js'); 
                
                /* $model_inw=new Inward;
                $model_inwTrans = new InwTrans; */
                $model = new Inward;
            $cnt=count($_POST);
            $isinw = isset($_POST['Inward']);
                //if(isset($_POST['Inward']))
                
                //if(isset($_POST['Inward']))
                if(isset($_POST['frender']))
                {
                                             
                        $model->attributes=$_POST['Inward'];
                        $valid = true;

                        if(($uname<>'record.collectorate') and 
                                ($model->sec_fno!='' or $model->sec_fno!=null) or 
                                ($model->sec_year!='' or $model->sec_year!=null)){
                                //$model->scenario='PR';
                           if(($model->sec_fno!='')and($model->sec_year!='')){
                            $valid=true;
                           }else{
                            $valid=false;
                           }
                        }
                      
                        if(($uname==='record.collectorate') and $model->clo_yn==='Y'){
                           // $model->scenario='finalization';
                        if(($model->sec_fno!='')and($model->sec_year!='')and($model->nfp!='')
                        and($model->cfp!='')and($model->tot!='')and($model->clo_date!='')and
                        ($model->clo_cat!='')){
                        $valid=true;
                        }else{
                        $valid=false;
                        }
                        }
                            

                           // Dialog::message('Model',"model attributed $model->sec_fno");

                        if($valid){
// Dialog::message('title',"username is $uname");
$curYear= date('Y');
$model->sec_year = $curYear - $model->sec_year;
                               foreach ($upd_inws as $inwno){
                                $umodel = Inward::model()->findByPk($inwno);
                              //  Dialog::message('INFO',"Got Inward number $umodel->inw_no");
                                 if($umodel->updateallFields($model)){
                                        $umodel->save();
                                  }
                               } 
                               $session->close();

// destroys all data registered to a session.
                       //  $session->destroy();
                      //$this->actionAdmin();
                      Dialog::message('INFO', "Records Saved Successfully Click Home button before proceeding");
                               $searchModel=new Inward('search');
                               $searchModel->unsetAttributes();  // clear any default values
  $searchModel->sec_fno=$model->sec_fno;
  $searchModel->sec_year=$model->sec_year;
                      $this->render('admin',array(
    'model'=>$searchModel,
      ));
                        }else{
                       
                          Dialog::message('ERROR', "unable to save Please Enter all the fields");
                       /*  $process = Yii::app()->createController('FirstController'); //create instance of controller
                         $process->test1();  */
                        // InwardController::errorpage();
                           $this->render('errorpage');
                        }

                     
                }else{
               
                 $this->render('updateall',array(
                        'model'=>$model,
                 ));
                }
        }
        else
        {
                //Yii::app()->user->setFlash('error', 'Please select at least one record to Update.');
          Dialog::message('ERROR', "Please select at least one record to Update.");
          $model = new Inward;
               $this->render('admin',array(
   'model'=>$model,
      ));
        }               
    }
////////////////////////////////////////////////////////////////////////////////////////////////////

3. Finally the UpdateAll view has to be created with the fields which are common for the files. ie., in our views/app_folder/updateall.php

//////////////////////////////// updateall.php  ////////////////////////////////////////////////////

<div class="form">

<?php $form=$this->beginWidget('CActiveForm', array(
        'id'=>'inward-form',
        'enableAjaxValidation'=>false,
        'htmlOptions' => array('enctype' => 'multipart/form-data'),
)); ?>

<p class="note">Fields with <span class="required">*</span> are required.</p>
<?php echo $form->errorSummary($model); ?>
<center>
<?php 
     $uname=Yii::app()->user->name;
?>
<table id="withborder" border=0 cellspacing=0 cellpadding=0 bgcolor="lightblue">
<tr><td>
<table border=0 cellspacing=0 cellpadding=0 bgcolor="lightblue">
<div class="row"> <tr><td>
                <?php echo $form->labelEx($model,'PR Number'); ?>
</td><td>
<?php echo $form->textField($model,'sec_fno',array('size'=>8,'maxlength'=>11)); ?>
<?php echo $form->error($model,'Sec. Asst.'); ?>
<?php echo "PR year"; ?>
   <?php  
$curYear= date('Y');
$prevYear = $curYear-5;
   echo $form->dropDownList ($model, 'sec_year', range($curYear,$prevYear), array('prompt'=>'Select Year'));  ?>
  <?php echo $form->error($model,'sub_scode'); ?>
</td>
</tr>
</div>
<?php
    if($uname<>'record.collectorate'){
?>
 <div class="row">
   <tr><td>
                <?php echo $form->labelEx($model,'Forward to --->'); ?>
      </td><td>
 <?php echo $form->dropDownList($model, 'sec_code' , CHtml::listData(Sections::model()->findAll(), 'sec_code', 'sec_name'),
                        array(
                                'prompt'=>'Select Section',
                                'ajax'=> array(
                                'type' => 'POST',
                                'url'=>CController::createUrl('Inward/Sec'),
                                'update'=>'#'.CHtml::activeId($model,'sub_scode'),
                                )
                        )
                    );
        ?>
                <?php echo $form->error($model,'Sec. Asst.'); ?>
          <?php echo "Sec.Asst."; ?>

 <?php echo $form->DropDownList($model,'sub_scode',CHtml::listData(SubSections::model()->findAllByAttributes(array('sec_code'=>$model->sec_code)),'sub_scode','sub_section'),array('prompt'=>'Select Subsection')); ?>
                <?php echo $form->error($model,'sub_scode'); ?>
         </td>
        </tr>
    </div>

 <div class="row">
                <tr>
                <td>
                <?php echo $form->labelEx($model,'File Status'); ?>
                </td>
                <td>
                <?php echo $form->textField($model,'filestatus',array('size'=>50,'maxlength'=>300)); ?>
                            <?php echo $form->error($model,'filestatus'); ?>
                </td>
                </tr>
        </div>


        <div class="row">
                <tr>
                <td>
                <?php echo $form->labelEx($model,'Remarks'); ?>
                </td>
                <td>
                <?php echo $form->textField($model,'sec_rem',array('size'=>50,'maxlength'=>360)); ?>
                            <?php echo $form->error($model,'sec_rem'); ?>
                </td>
                </tr>
        </div>
<?php } ?>
<?php   $tuname=Yii::app()->user->name; ?>

<?php  if($tuname=='record.collectorate') { ?>

<tr>
        <div class="row">
        <td>
<?php echo $form->labelEx($model,'close the file?'); ?> 
</td> <td>
<?php echo $form->dropDownList($model,'clo_yn',
array('N' =>'No', 'Y'=> 'Yes'),
array(
'ajax'=>array(
          'type'=>'POST',
          'url'=>CController::createUrl('Inward/Dis'),
          'update'=>'#description_id',
)));
?>
        </td>
</div>
</tr>

</table>
<table border=0 cellspacing=0 cellpadding=0 bgcolor="lightblue">
<?php $tc=$model->clo_yn; ?>
<tr>
<td>
<div id="description_id">
<?php if($tc=='Y'){ ?>

<?php
echo "<table>";
echo "<tr><td>";
echo "<b>Close type</b>";
echo "</td>";
echo "<td>";
echo $form->dropDownList($model,'clo_cat',array('D'=>'D','L'=>'L','N'=>'N','R'=>'R'),array('prompt'=>'Select Dis'));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>Closing Date <br/> (Year-Mon-Date)</b>";
echo "</td>";
echo "<td>";
 echo $form->textField($model,'clo_date',array('size'=>30,'maxlength'=>30));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>NFP</b>";
echo "</td>";
echo "<td>";
 echo $form->textField($model,'nfp',array('size'=>10,'maxlength'=>10));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>CFP</b>";
echo "</td>";
echo "<td>";
echo $form->textField($model,'cfp',array('size'=>10,'maxlength'=>10));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>Total</b>";
echo "</td>";
echo "<td>";
echo $form->textField($model,'tot',array('size'=>10,'maxlength'=>10));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>Remarks</b>";
echo "</td>";
echo "<td>";
 echo $form->textField($model,'clo_rem',array('size'=>45,'maxlength'=>360));
echo $form->error($model,'clo_rem'); 
echo "</td>";
echo "</tr>";
echo "</table>";
?>

<?php } ?>
</div>
</td>
</tr>
<?php  }
echo CHtml::hiddenField('frender' , '1', array('id' => 'frender'));

 ?>
<?php /// END OF CLOSING IF for RECORD SECTION ?>

        <div class="row buttons">
         <tr>
           <td><td>
           <td align="left">
                <?php echo CHtml::submitButton($model->isNewRecord ? 'Save' : 'Save'); ?>
                <?php // echo CHtml::button('Save',array('submit' => array('inward/updateall'))); ?>
           </td>
        </tr>
        </div>
</table>
</td>
</tr>
</table>

<?php $this->endWidget(); ?>

</div><!-- form -->

</center>

Thats it.. I have done for my example with defined fields, change the fields according to your requirement.

GOOD LUCK!!!

Using Data access objects DAO in yii

 Updating a record using DAO in yii 

$connection1=Yii::app()->db;
$sql1="update inwtrans set out_section='$sname',out_time='$tdt' where id=$max";
$command1=$connection1->createCommand($sql1)->execute();

Similarly Inserting a record example usig DAO is below 

$connection=Yii::app()->db;
$sql="insert into inw_trans ( inw_no, inw_year, sec_code, sub_scode, in_time, status, in_section, ipadd) values ($inwno,'$inwyr',$sec_code,$subsec,'$tdt','O','$sname','$ipadd')";
$command=$connection->createCommand($sql)->execute();

Error handling code to perform the transactions : 

$prevyr = intval(date('Y')-1);
$connection=Yii::app()->db;
$transaction = $connection->beginTransaction();
try {
     $sql="insert into inward2(fms_rno,ref_no,uid) select fms_rno,ref_no,uid from inward where inw_year='$prevyr';";
    $connection->createCommand($sql)->execute();
    $sql1="delete from inward where inw_year='$prevyr'";
    $connection->createCommand($sql1)->execute();
    $sql2="alter table inward auto_increment=1";
    $connection->createCommand($sql2)->execute();
    $sql3="insert into inw_trans2(inw_no,sec_code,sub_scode) select inw_no, sec_code, sub_scode from inw_trans where inw_year='prevyr'";
    $connection->createCommand($sql3)->execute();
    $sql4="delete from inw_trans where inw_year='$prevyr'";
    $connection->createCommand($sql4)->execute();
    $sql5="alter table inw_trans auto_increment = 1;";
    $connection->createCommand($sql5)->execute(); 
    $transaction->commit();
}catch (Exception $e) {
$transaction->rollBack();
}

Example of a query which return an array

$list= Yii::app()->db->createCommand('select * from post')->queryAll();

$rs=array();
foreach($list as $item){
    //process each item here
    $rs[]=$item['id'];
}
return $rs;

if you want to bind some params:

$list= Yii::app()->db->createCommand('select * from post where category=:category')->bindValue('category',$category)->queryAll();

if you just want to run a query return nothing return:

Yii::app()->db->createCommand('delete * from post')->query();