Category: php dev tools

These are the tools for the development of PHP and mySQL

Formatting a “Pretty Date” from an “Ugly” Database Timestamp

One of the constant issues when working with legacy data is how ugly it is. When you’re calling a stored procedure to pull out legacy data from an Oracle Storage Management System backend, the resulting text string is formatted in a way that… Well, let’s just say its hideous looking, or at least we can say that it’s not human readable. Fortunately I’ve come up with a large library of conversion methods to take this data and format it into a way that will look good in the application front end UI. For example here’s a here’s a sample from my library that can take a MySQL timestamp and format it to look nice on a web page or application front end. To save critical UI space and help differentiate old records from new ones, the display shows a different format for how old the record is. For example records from today will simply say what time they were entered. Records from this week will have the day of the week and records older than that will have the dates. I use techniques like these frequently while handling the data conversion from where it’s stored in a legacy back in to being displayed on the modern front end user interfaces.

//********************************************************************************
function formatDateFromDb($timestamp){
    $time_object = new DateTime($timestamp, new DateTimeZone('UTC'));
    $time_object->setTimezone(new DateTimeZone('America/Los_Angeles'));
    /*echo("<p>records from last year {$time_object->format('m/d/y g:i A')} </p>");
    echo("<p>records from last week {$time_object->format('D F j g:i A')} </p>");
    echo("<p>records from current week (but not from today) {$time_object->format('D g:i A')} </p>");
    echo("<p>records from current day {$time_object->format('g:i A')} </p>");*/
    
    $current_year = date('Y');
    $timestampyear = date('Y', strtotime($timestamp));
    $current_week = date('W');
    $timestampweek = date('W', strtotime($timestamp));
    $current_day = date('N');
    $timestampday = date('N', strtotime($timestamp));
    $current_hour = date('H');
    $timestamphour = date('H', strtotime($timestamp));
    $prettyDate = "";
    
   
    
    if($current_year - $timestampyear > 0) {
        //records from last year |  03/08/21 3:41 PM
        $prettyDate = $time_object->format('m/d/y g:i A');
    } else {
        if($current_week - $timestampweek > 0){
            //records from last week | Mon March 8 3:41 PM
            $prettyDate = $time_object->format('D F j g:i A');
        } else if(($current_week - $timestampweek == 0 && $current_day - $timestampday >0) || ($current_week - $timestampweek == 0 && $current_day - $timestampday == 0 && $timestamphour < 8)){
             //  records from current week (but not from today) |  Mon 3:41 PM
            $prettyDate = $time_object->format('D g:i A');
        } else if($current_week - $timestampweek == 0 && $current_day - $timestampday == 0 && $timestamphour >= 8){
            // records from current day | 3:41 PM
            $prettyDate = $time_object->format('g:i A');
        } else {
            $prettyDate = "time format not found for {$timestamp}";
        }
     
    }
    return $prettyDate;
}
//-----------------------------------------------------
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Looking at the PHP Form Post Object

I use php to talk with MySQL db server. It comes in handy for making apps. Here are the code variations for receiving POST variables from a client request and ,for debug, a quick list of the variables, names and values. Using this: print_r($_POST); this gives a breakdown of the array holding the data from the post variables…. Array ( [signindate] => 11/4/2011 [signintime] => 15:33 PM [nameofchild] => dev en [parentsignature] => don e [in] => Sign In ) to access an element of the array I use a couple methods.. to get them one by one  by name so I can put them into the database.. $myformelement=$_POST[‘form element field html id name’];

Now that the records are correctly inserting into database, my next step is to  use some ajax to call the php behind the scenes while leaving my webapp ui view intact.

  • Check if a person is selected
  • Create an XMLHttpRequest object
  • Create the function to be executed when the server response is ready
  • Send the request off to a file on the server

PHP expression and comparison techniques

Ternary operations:

If ($username !== “Deedubs”){$username=$newusername;}  You know what, this doesn’t need a shorthand notation, its nice and compact.

 

Search for a string within a string using php

Need to compare or discover a certain text inside a string on the server side? PHP has some great stuff for this.

strpos() will find the position of the first occurrence of a substring in a string. For example:
I want to find if the text “blue” is in the sentence “My blue heaven”.
$mystring=”My blue heaven”;
$findme=”blue”;
$pos = strpos($mystring, $findme);

// Note our use of ===.  Simply == would not work as expected
// if the position  was the 0th (first) character.
if ($pos === false) {
echo “The string ‘$findme’ was not found in the string ‘$mystring'”;
} else {
echo “The string ‘$findme’ was found in the string ‘$mystring'”;
echo ” and exists at position $pos”;
}

find file extension using php

lets learn the search capabilities of php to find if the file is a jpg or a png file, and then perform an action.

$filename=”myfile.png”;
$fileextension = pathinfo($filename, PATHINFO_EXTENSION);

if($fileextension == “png”){echo(“we got a png to display.”)}else{echo(“jpg image display”);}

Works great for what I have goin on. Can’t reveal the deets, but it’s huge, of course.

 

How to make a date formatted to insert into MySQL database using PHP

This is great, goes with the jquery ui pop up calendar datepicker. Formats the date to be inserted into mysql’s funky date field.

$my_formatted_date = date(‘Y-m-d’, strtotime($my_unformatted_date));

 

Here is a MySQL select for nothing older than 15 minutes.
select darecord, dadate from mytable where dadate > NOW() – INTERVAL 15 MINUTE

Here is how to calculate the amount time between two time entries:
SELECT TIMEDIFF(`time_in`, `time_out`)  FROM `project_timecards`

 

As stated in Date and Time Literals:

MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.
  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Therefore, the string '08/25/2012' is not a valid MySQL date literal. You have four options (in some vague order of preference, without any further information of your requirements):

  1. Configure Datepicker to provide dates in a supported format using its dateFormat option: 
    $( ".selector" ).datepicker({ dateFormat: "yyyy-mm-dd" });
  2. Use MySQL’s STR_TO_DATE() function to convert the string: 
    INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y'))
  3. Convert the string received from jQuery into a PHP timestamp—e.g. using strtotime(): 
    $timestamp = strtotime($_POST['_date']);

    and then either:

    • format the timestamp using date(): 
      $date = date('Y-m-d', $timestamp);
    • pass the timestamp directly to MySQL using FROM_UNIXTIME(): 
      INSERT INTO user_date VALUES ('', '$name', FROM_UNIXTIME($timestamp))
  4. Manually manipulate the string into a valid literal: 
    $parts = explode('/', $_POST['date']); $date = "$parts[2]-$parts[0]-$parts[1]";