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


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

PHP “&” character in $_GET variable issue i.e., sending PHP variable consisting value '&' through hyperlink

For example, consider the below code,

The $product consisting value jeans&shirts

 $abslink="first.php?purchase=$product";

echo "<a href=\" $abslink \">120 </a> ";

Now the first.php has to receive the value jeans&shirts , here is the below code

 <?php

require "test.php";

$variable = $_GET['purchase'];

echo $variable;

?>

But we receive the output as 'jeans' not 'jeans&shirts' . Since it consisting of & symbol the url break the value and treats the next string as variable name i.e., shirts.

To rectify above problem,

You will want to urlencode() your string:

// Your link would look like this:
'localhost/first.php?purchase='.urlencode('jeans&shirts');

When you want to use it, you would decode it:

echo $variable = urldecode($_GET['purchase']);

ENCODE: http://php.net/manual/en/function.urlencode.php

DECODE: http://php.net/manual/en/function.urldecode.php

---

EDIT: To test write this:

echo $url = 'localhost/first.php?purchase='.urlencode('jeans&shirts');
echo '<br />';
echo urldecode($url);

Your result would be:

// Encoded
localhost/first.php?purchase=jeans%26shirts
// Decoded
localhost/first.php?purchase=jeans&shirts

BULK Update in YII using CGRIDVIEW

A Description of the action of UpdateALL button
<b>
<font color="red"> Bulk Update: </font>
Select same file number in the office list by clicking the check boxes beside the file numbers
and then click new button "Update ALL", then provide all the required fields and click save. This saves all the files.
</b>

<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>

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

To copy the same cell value ( do not increment cell number) in excel without increment in excel formula while dragging

For example if we have a excel sheet with tables in which we have data,
There are two sheets namely, PRESENT and DATESHEET.

In which the datesheet A1 cell consisting todays date in one format and C1 cell consisting report exported time and E1 consisting dateTime according to Database format. These three are required in our database to generate a report.

There is a requirement the report to be generated datewise format, But as we export the data from a website or database the date field is not present , so we need to export the data in a excel/csv format and we need to right a script in which the date field should be added.

suppose our data is exported to sheet namelt PRESENT ( here I have taken attendance report example)

As said earlier we have datesheet and present sheet. Now we need open a new tab/sheet in our excel file with name SCRIPT

In this script sheet we need to write the formula as stated below

=CONCATENATE("insert into daypresent(slno,office_name,today_date,report_time,db_datetime,tot_registered_emp,
tot_present) values('",$present.A4,"','",$present.B4,"','",$datesheet.$A$1,"','",
$datesheet.$C$1,"','",$datesheet.$E$1,"','",$present.C4,"','",$present.E4,"');")

Kindly observe in the above formula we are using Sheetname.Cell_reference prefixed with $ symbol. The symbol refer to that particular sheet and the specified cell. So here we are concatenating the two sheets cells in our script sheet.

Fine, after entering the formula in the first row in script sheet we can get the all records by simply dragging the cell to the rows (this is depends on our database record count). To the count we need to drag the formula.

Here is the case, The date is common for all the rows when we drag the cell formula the excel feature increment the cell value automatically we makes the next insert statement for row two . So in the next cell the formula becomes

=CONCATENATE("insert into daypresent(slno,office,tdate,rdate,rtime,regemp,present) values('",$present.A5,"','",$present.B5,"','",$datesheet.$A$1,"','",$datesheet.$C$1,"','",
$datesheet.$E$1,"','",$present.C5,"','",$present.E5,"');")

Present.A4 to Present.A5 , Present.B4 to Present.B5 .....       this is what we required but
 what about the datesheet we need the same cell value for all the rows

to achieve this, we need to give $datesheet.$A$1

the  cell values remains same even we drag the contents if we specify $A$1. That is the cell count wont increment