MySQL Server

46K

Solutions

22K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Share tech news, updates, or what's on your mind.

Sign up to Post

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
2
Database Backup and Recovery Best Practices
LVL 2
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
1
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
0
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
0
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.
0
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
0
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
1
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective solution is to partition the data into more manageable parts.
Modern databases (including MySQL and MariaDB) have and offer a feature called table partitioning which reduces query execution times and makes the data more manageable. In this article I assume you have MySQL 5.6 or newer or MariaDB 10.0.4 or newer.

What is Table Partitioning?


Table partitioning is a data organization scheme to divide large quantity of data into multiple data storage (data partitions). The whole purpose of table partitioning is to divide the table data into smaller and more manageable data units, shorten reorganization needs/ -time and speed up inserts/updates/deletes and queries execution time that on regular tables becomes slower as the data in the table grows.


Partitioning types


MySQL and MariaDB (as does most modern databases) offer several types of table partitioning. These types are


Range and range columns partitioning (the most commonly used),

create table mytable ( id int(11) NOT NULL, ….. )

partition by range ( id ) (

partition p0 values less than ( 10000),

partition p1 values less than ( 20000), ….

);

Open in new window


List and list columns partitions,

create table 

Open in new window

1
Introduction
This article is intended for those who are new to PHP error handling.  It addresses one of the most common problems that plague beginning PHP developers: effective error visualization.

PHP error handling is well-documented in the online man pages, but the documentation often eludes beginners, who are trying to learn PHP by copying examples they found on the internet.  Copying the code without understanding the code is an antipractice, and there are so many bad PHP examples out there, it can sometimes be difficult to find good ones!  This article will help you get the most, quickly and easily, out of PHP's basic error handlers.  You can get excellent error information, informative and well-targeted, by just following a few simple examples.  So stop copying those obsolete internet examples, and instead make a few "teaching examples" for your own library.  By the time you've finished this article, you will have some great insights into how to diagnose the most common errors quickly and find good solutions.

What's Covered Here
PHP has unlimited opportunities for errors, but in practice only a few things are needed to get good diagnostics.  You need to be able to see the errors in PHP and you need to …
3
 
LVL 110

Author Comment

by:Ray Paseur
Comment Utility
Thanks, Martin.

Some time ago when I published articles, there was a cascade of approvals - something like "It's OK" then "It's good" then "It's really good" and each of these approvals gave some more points as well as comments about how the article can be made better.  Does E-E still do that?
0
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logout, and Easy Access Control.
3
Learn how to optimize MySQL for your business need
LVL 2
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

load balancing
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
0
Creating and Managing Databases with phpMyAdmin in cPanel.
0
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to do amazing, modern things with bits!
27
 
LVL 35

Author Comment

by:gr8gonzo
Comment Utility
@philkryder - The impact is going to be different depending on the application. For example, let's say that you do have this situation where you're dealing with 20 million 15-byte strings (roughly 286 megabytes).

If you're dealing with a database, then typically you're in a client/server setup, so the client needs the data locally in order to do any viewing or processing (this can be expanded also to a scenario where you've got DB Server -> Web Server -> End User, and data has to be transferred twice - and that's just basic setups).

Typically, the data's going to be transmitted over a TCP/IP network connection, so you're also adding about 8 megs of TCP overhead for 300,000,000 bytes (versus 2 megabytes for 80,000,000 bytes). You also have overhead of the structure containing/defining the data, plus any extra identifying data, but we'll set that aside for now.

So let's say that the database server has all the resources it needs to send all the data across to the client. On a LAN, it might not take too long either way, but you'll still notice a significant difference between transferring 78-ish megs vs 294-ish megs.

If you're dealing with a situation where you transfer that over a broadband connection of some kind, the difference will be even larger.

So the majority of the time, your "extra time" is going to be found in the data transfer, since that's often the slowest point. For the sake of having some example numbers, let's say that we're transferring over a 60Mbps connection, so we're looking at anywhere from 30 seconds - 45 seconds to download a roughly-300 meg payload, and 10-15 seconds to download a roughly 80-meg payload.

We'll say that we can save about 20-30 seconds in transfer time.

Now let's say the client app finally has the data in memory. It probably needs to store it somewhere, so there's some kind of structure involved, which also means an extra % of overhead storage in memory. Let's say you have a C# app using a DataTable structure. A string column in a DataTable takes up a LOT more memory per row than a long column. I don't have hard numbers in front of me, but I did have a project once where I hadn't defined my column type and I was accidentally storing small integers as a string. When I fixed the problem with a byte type definition on the field, memory usage dropped by several hundred megabytes.

So now we've got a client app with the data in memory. What's the next step?

If we're displaying the data on-screen, then chances are that you're probably only converting a handful of records at a time - maybe 200 at most.  The time it takes to convert 200 longs into string is around 0.001 seconds each time (just a rough test on my end).

But let's say you want to convert those 20 million numbers into 20 million entries all at once to be written to a file. Let's say that looping through 20 million records in memory takes a full 3 seconds, with absolutely no processing at all, so it wouldn't matter if the numbers were already converted or not - 3 seconds is the starting baseline. Your question is then - how much ADDITIONAL time would it take to convert 20 million numbers? A rough test on my machine shows about 40 seconds, and of course, that's going to depend on processor power. Many machines might be even slower.

So now we have some example numbers.
You have about 20-30 seconds of savings on the transfer side. If you were to turn around and convert all of those to strings immediately and write them to a file, then you're probably looking at a speed LOSS of roughly 10 seconds. Of course, this also doesn't take anything else into consideration (the value of bandwidth pipes / network saturation / memory / likelihood of this scenario / scalability across multiple clients / etc).

For displaying the data on-screen, you have about 20-30 seconds of savings on the transfer side, and virtually no time spent on conversion since you're not doing them all at once. The client's not going to notice a split-second of extra time every time they move to a new page of results.

Either way, there is a LOT of extra value in more efficient storage. There will always be some scenarios where it doesn't make sense to compact everything, but that's where being a good programmer comes into the picture and understanding the business case and how it translates into data flows and the value of each resource involved, and how that multiplies with concurrent load.
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
Comment Utility
Excellent article!  The only thing I would have included would be a small section on Endianness (Big Endian vs. Little Endian).
0

Introduction

Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the principles and examples of the original article on a 1:1 basis.  As I created the examples for this article, I came across some anomalies in the PHP OOP DateTime implementation.  Here are some of my impressions and some notable gotcha's that I found.


It is possible to create an infinite loop with date/time arithmetic.  Your code will look right, but transitions at the boundaries of daylight savings time will change the values in your DateTime objects.  I hope PHP will fix this.

See: http://php.net/manual/en/datetime.sub.php#114780


When you create a new DateTime object, you can assign both a date/time string and a timezone.  If you create the DateTime object from a Unix timestamp, the timezone cannot be used in the constructor.  You must assign the timezone to the object separately, after the object is created.


The ISO-8601 format for DateTime::format() method is not 100% ISO-8601 compatible.  You can use DateTime::ATOM instead if you need perfect compatibility.


The designator for a Unix timestamp in DateTime::__construct() is the at-sign ("@") prepended to the numeric Unix timestamp.  The at-sign ("@") is also the PHP error-control operator, but it has no error-control meaning in the DateTime constructor.


There is no avoiding arithmetic - DateTime::getOffset() returns its answer in seconds, but functions that use this information may expect minutes or hours.


I found no convenient way to instantiate a DateTime object and get a formatted date/time string in a single line of code.  The marriage of PHP date() and strtotime() was a convenience that is not here any more.  I'll update this article if I find a good solution.


At PHP 7.1+ the DateTime constructor incorporates microseconds when the object is constructed from the current time.  This may increase the risk that two DateTime objects will be unequal.


Now the good news.  Most of what you learned about date formatting with the date() format parameters is still applicable.  You will write a little more code with the OOP notation, but that is common for OOP programming and may help to improve readability.  And none of the common tasks we use in procedural PHP is impeded by the OOP DateTime notation.  A list of the most useful references is included at the end of this Article.


The DATE/TIME Tower of Babel

Human beings can read dates and times in a variety of ways.  We readily understand such things as September 5th, 2010, and we know that it comes before November 11th, 2010,and after May 9th, 2010.  We have shorthand conventions that let us write things like 9/5/2010 or the military correspondence format 5 Sep 2010.  But when we try to process dates and times in a computer program, this variety of formats becomes a jumble of confusion.  In response to the confusion, the International Organization for Standards was moved to publish a standard in 1988.  The standard has been revised and extended somewhat since the original publication.  The only thing that mystifies students of history is why it took so long to promulgate a standard.


Toward a Universal DATETIME Notation

Date formats for computer processing are prescribed by the ISO-8601 standard.  The ISO-8601 standard places all the information in fixed-width data strings with leading zeros where needed.  When you read ISO-8601 standard information you notice immediately that everything lines up in columns.  The larger values are to the left and progressively smaller values are to the right, starting with Year, then Month, then Day, then Hour, etc.  Date/time information formatted according to the standard is very useful because it is both easy to read and easy to understand in comparisons and computations.  For example, the date '2010-09-05' is a valid ISO-8601 string meaning September 5th, 2010.  Imagine how difficult it would be to write programming that works with dates in the text formats, or dates that are formatted like this: 05.09.2010.  Does that mean May 9th, 2010 or September 5th, 2010?  Fortunately the ISO-8601 standard removes the ambiguity.


The ISO-8601 standard removes the ambiguity about the time of day, as well.  All of the hours are represented on a 24-hour clock, so there is no question about what "two o'clock" might mean.  The string 0200 or 02:00:00 refers to 2:00 a.m.  If you're thinking of 2:00 p.m. your ISO-8601 standard will use 14:00:00.


This link gives a good discussion and examples of permissible variations on the ISO-8601 format.

See http://en.wikipedia.org/wiki/ISO_8601


The Value of PHP time() is Always the Same

No matter where in the world you stand, the value of time() is always the same.  The PHP function time() returns the number of seconds since the Unix Epoch.  While local times may differ, time() ticks on second-by-second.  Run this script to see the effect.  Knowing this, we can do arithmetic with seconds, and then return human-readable date and time values that are sensible for different locations around the world.

<?php
                                        /**
                                         * Different Timezones share the same Unix Timestamp
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        // TIMEZONES AROUND THE GLOBE
                                        $locations
                                        = array
                                        ( 'Pacific/Auckland'
                                        , 'Australia/Sydney'
                                        , 'Australia/Perth'
                                        , 'Asia/Tokyo'
                                        , 'Asia/Calcutta'
                                        , 'Asia/Tel_Aviv'
                                        , 'Africa/Cairo'
                                        , 'Europe/Rome'
                                        , 'Europe/London'
                                        , 'Atlantic/Bermuda'
                                        , 'America/Chicago'
                                        , 'America/Anchorage'
                                        , 'Pacific/Honolulu'
                                        , 'UTC'
                                        )
                                        ;
                                        // ITERATE OVER THE TIMEZONES
                                        foreach ($locations as $location)
                                        {
                                            // SET OUR ZONE AND LOCAL TIME
                                            $zoneobj = new DateTimeZone($location);
                                            $dateobj = new DateTime(NULL, $zoneobj);
                                        
                                            // SHOW THE LOCATION AND THE CURRENT DATE / TIME
                                            echo PHP_EOL;
                                            echo str_pad($location, 24, ' ');
                                            echo $dateobj->format(DateTime::RSS);
                                        
                                            // SHOW THE NUMBER OF SECONDS SINCE THE EPOCH, RECOMPUTED IN EACH TIMEZONE
                                            echo ' ';
                                            echo $dateobj->format('U');
                                        }


Handling External Input

Whenever you accept a date/time string from an external data source, what is the first thing you should do?  Turn it (immediately) into an instance of the DateTime object.  The DateTime constructor can turn almost any English language human-readable date/time text into an object for use in DateTime computations and displays. Like the PHP function date(), the DateTime::format() method can turn DateTime information into a formatted date. 

<?php 
                                        /**
                                         * The DateTime constructor may throw an Exception
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $external = 'Yesterday';
                                        try
                                        {
                                            $dateobj = new DateTime($external);
                                        }
                                        catch (Exception $e)
                                        {
                                            // ON FAILURE, SHOW THE EXCEPTION
                                            var_dump($e);
                                        }
                                        echo PHP_EOL . $dateobj->format(Datetime::ATOM);
                                        echo PHP_EOL;

Once you have done that internal date format conversion your programming will handle internal date/time computations easily.


When you are storing date/time values, you may want to store them in the ISO-8601 format strings or in the form of Unix timestamps.  I prefer to use the formatted strings - they are easier to understand when you're debugging.


Handling External Input that is Invalid

The PHP DateTime constructor can turn virtually any human-readable date / time text into a DateTime object - but it won't work on everything.  When it fails to make the conversion, it throws Exception.  You can and should test for this.


<?php
                                        /**
                                         * Invalid date/time strings can be detected when you catch the Exception
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $external = 'Nonsense';
                                        $dateobj  = FALSE;
                                        try
                                        {
                                            $dateobj = new DateTime($external);
                                        }
                                        catch (Exception $e)
                                        {
                                            // ON FAILURE, SHOW THE EXCEPTION
                                            var_dump($e);
                                        }
                                        if ($dateobj)
                                        {
                                            $iso_datetime = $dateobj->format(Datetime::ATOM);
                                            echo "SUCCESS: $external EQUALS ISO-8601 $iso_datetime";
                                        }
                                        else
                                        {
                                            echo "ERROR: I DO NOT UNDERSTAND $external";
                                        }


Handling External Input that is Formatted (contrary to the disambiguation rules)

The PHP DateTime Constructor works much the same way as strtotime(), making some assumptions and obeying certain rules about the format of the date / time input string.  But what if you have dates that are not formatted according to the rules?  One of the common issues arises when we have dates that are in European order d-m-y, but have been written with slashes, implying American order m/d/y.  This may result in incorrect output when the values for m and d  are both in the range from 1 - 12, and will cause an exception when m exceeds 12.  Fortunately PHP has a way around this issue, in the static constructor createFromFormat().  We can tell PHP what format to use as it interprets the date.  This example shows how.


<?php
                                        /**
                                         * Date values separated by slash are assumed to be in American order: m/d/y
                                         * Date values separated by dash are assumed to be in European order: d-m-y
                                         * Exact formats for date/time strings can be injected with createFromFormat()
                                         *
                                         * http://php.net/manual/en/datetime.createfromformat.php
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        // THIS WOULD IMPLY MONTH == 19, OBVIOUSLY INVALID
                                        $external = "19/10/2016 14:48:21";
                                        
                                        // HOWEVER WE CAN INJECT THE FORMATTING WHEN WE DECODE THE DATE
                                        $format = "d/m/Y H:i:s";
                                        $dateobj = DateTime::createFromFormat($format, $external);
                                        
                                        $iso_datetime = $dateobj->format(Datetime::ATOM);
                                        echo "SUCCESS: $external EQUALS ISO-8601 $iso_datetime";


Interesting Forms of External Input

All of these external inputs work correctly with the DateTime constructor and this gives you and your clients powerful ways of talking about dates and computing with dates.

- 3 hours

tomorrow

tomorrow midnight

tomorrow 1:35pm

March 15, 1986

yesterday

yesterday + 1 week

next year

now

now + 627 hours 15 minutes

last Tuesday

third Wednesday

3 minutes, 15 seconds


Producing "Pretty" Dates From DateTime Objects ISO-8601 Dates

When you are ready to present the dates to people and you want them to see nicely formatted dates, you can use the DateTime::format() method to do the reformatting.  Let's say you have this timestamp value: 1,284,593,400 (which equals the ISO-8601 date: '2010-09-15T18:30:00-0500'. )  Maybe you got it out of your data base or computed it in your script.  It's meaningful, but not what you expect when you open the envelope.  "Please join us for cocktails on Unix Timestamp 1,284,593,400."  How can we get something that would be appropriate on an invitation?  Here is how you might do the conversion from a timestamp or ISO-8601 date to the pretty date.

<?php 
                                        /**
                                         * The DateTime::format() can change the rendering of date values and produce pretty dates
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $iso_date = '2010-09-15T18:30:00';
                                        $formats  = 'l, F jS, Y \a\t g:ia';
                                        $dateobj  = new DateTime($iso_date);
                                        echo PHP_EOL . $dateobj->format($formats);

Outputs "Wednesday, September 15th, 2010 at 6:30pm" -- very civilized!


Breaking down the date formatting, we have the following (all documented on PHP.net)...

$pattern
                                        = 'l, '    // LOWERCASE L - text name of the day of the week
                                        . 'F '     // UPPERCASE F - text name of the month
                                        . 'j'      // LOWERCASE J - day of the month
                                        . 'S, '    // UPPERCASE S - ordinal like the letters in 1st, 2nd, etc 
                                        . 'Y '     // UPPERCASE Y - 4-digit year
                                        . '\a\t '  // ESCAPED LETTERS... More to follow
                                        . 'g:'     // LOWERCASE G - hours on a 12-hour clock
                                        . 'i'      // LOWERCASE I - minutes 
                                        . 'a'      // LOWERCASE A - designator of "am" or "pm"
                                        ;


You might envision several patterns that you need in your application.  Most PHP sites and services are written with a central "common.php" file that gets included into all the specialized scripts.  You could prepare the formatting patterns in the common script and refer to them by a variable name, or define the patterns as constants.

define('MILITARY_CORRESPONDENCE', 'j M y');
                                        define('SHORT_8601', 'Y-m-d');
                                        define('COCKTAILS', 'l, F jS, Y \a\t g:ia');


What about the escaped letters?  Like the date() function, the DateTime::format() uses letter patterns as signals to describe the conversion into human-readable character strings, so some letters have special relevance and some do not.  Any character that is not used as a signal in the date pattern string is returned from the function unaltered.  Punctuation is one example - commas and hyphens go right through.  But if we want to get one of the signal letters back, we must tell PHP to ignore the signal and just return the letter.  We do that with the back-slash character, as shown here.  Try running these two lines of code to see how the escape can be useful:

echo PHP_EOL . date('The year is: Y');
                                        echo PHP_EOL . date('\T\h\e \y\e\a\r\ \i\s\: Y');


Setting Your Own Clock Values

Since the values contained in the DateTime object are usable in many different time zones, you want to have control over the way PHP interprets the time zone.  You control this time with the DateTimeZone object.  You can set the DateTimeZone in the DateTime constructor, or you can inject a new DateTimeZone into a DateTime object via the setTimeZone() method.  Here is an example that I use, because my server is located in the midwest at ChiHost.com.


<?php
                                        /**
                                         * DateTime objects are usually aware of time zones via the DateTimeZone object
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $string  = 'Now';
                                        $zoneobj = new DateTimeZone("America/Chicago");
                                        $formats = 'l, F jS, Y \a\t g:ia (e)';
                                        $dateobj = new DateTime($string, $zoneobj);
                                        echo PHP_EOL . $dateobj->format($formats);
                                        
                                        // WHAT TIMEZONE SHOULD I USE?
                                        $tz_ids = DateTimeZone::listIdentifiers(DateTimeZone::PER_COUNTRY, 'US');
                                        foreach($tz_ids as $zone)
                                        {
                                            echo PHP_EOL . $zone;
                                        }


Special Meanings for NOW and TODAY

We know how to convert external date/time strings into DateTime objects, and we know how to return them to human-preferred formats with DateTime::format().  Now we can return to some of the special meanings of date/time strings.  NOW and TODAY are particularly useful.

<?php 
                                        /**
                                         * NOW and TODAY have special meanings in the DateTime object; NOW includes time of day
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $date = new DateTime('Now');
                                        echo PHP_EOL . $date->format(DateTime::RSS);
                                        
                                        $date = new DateTime('Today');
                                        echo PHP_EOL . $date->format(DateTime::RSS);
                                        
                                        $date = new DateTime('Now', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $date->format(DateTime::RSS);
                                        
                                        $date = new DateTime('Today', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $date->format(DateTime::RSS);

The principal difference between NOW and TODAY is the time of day.  TODAY is always today's local date at midnight, with hour, minute and second equal to zero.  NOW is today's date including the current time of day.  So in our September example, NOW and TODAY have UNIX timestamp values equal to 1,284,593,400 and 1,284,526,800 respectively.  The difference is 66,600.  This is the number of seconds from midnight to 6:30pm (18 hours plus 30 minutes).  


This script shows that the Unix timestamp is consistent, no matter what timezone you have specified.

<?php 
                                        /**
                                         * The Unix Timestamp values for NOW and TODAY are the same, no matter what timezone
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $dateobj = new DateTime('Now');
                                        echo PHP_EOL . $dateobj->format('U');
                                        
                                        $dateobj = new DateTime('Today');
                                        echo PHP_EOL . $dateobj->format('U');
                                        
                                        $dateobj = new DateTime('Now', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $dateobj->format('U');
                                        
                                        $dateobj = new DateTime('Today', new DateTimeZone('UTC'));
                                        echo PHP_EOL . $dateobj->format('U');


Computing the Difference Between DateTime Objects

This script shows how to compute the difference between two DateTime objects, using the diff() method.


<?php 
                                        /**
                                         * Computing the difference between NOW and TODAY in different timezones
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        // TESTING UTC
                                        $zoneobj    = new DateTimeZone('UTC');
                                        $date_now   = new DateTime('Now',   $zoneobj);
                                        $date_today = new DateTime('Today', $zoneobj);
                                        $date_diff  = $date_now->diff($date_today);
                                        $print_diff = $date_diff->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . $zoneobj->getName();
                                        echo PHP_EOL . $print_diff;
                                        echo PHP_EOL;
                                        
                                        // TESTING ANOTHER ZONE
                                        $zoneobj    = new DateTimeZone('America/New_York');
                                        $date_now   = new DateTime('Now',   $zoneobj);
                                        $date_today = new DateTime('Today', $zoneobj);
                                        $date_diff  = $date_now->diff($date_today);
                                        $print_diff = $date_diff->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . $zoneobj->getName();
                                        echo PHP_EOL . $print_diff;
                                        echo PHP_EOL;
                                        
                                        // USING METHOD CHAINING FOR TIGHTER CODE
                                        $print_diff  = $date_now->diff($date_today)->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . $zoneobj->getName();
                                        echo PHP_EOL . $print_diff;
                                        echo PHP_EOL;


As you can see, it is important to be consistent about your timezone.  You can deal with this phenomenon in one of two ways.  You can set your timezone to UTC.  Or you can set your timezone to a reasonable setting for the location of your server.  I prefer the latter.  See http://php.net/manual/en/timezones.php and http://php.net/manual/en/timezones.others.php for more.


Time Without Date

Let's say we want to know the elapsed time between two events, and we want to express it in notation like HH:MM:SS. DateTime::diff() and DateTime::format() can help with this.

<?php 
                                        /**
                                         * Computing the difference between two times
                                         */
                                        error_reporting(E_ALL);
                                        echo '<pre>';
                                        
                                        $alpha = "2:30:47pm";
                                        $omega = "3:43:16pm";
                                        $date_alpha = new DateTime($alpha);
                                        $date_omega = new DateTime($omega);
                                        $elapsed    = $date_omega->format('U') - $date_alpha->format('U');
                                        $date_diff  = $date_omega->diff($date_alpha);;
                                        $print_diff = $date_diff->format('%H:%i:%S');
                                        
                                        echo PHP_EOL . "TWIXT $alpha AND $omega THERE ARE " . number_format($elapsed) . " SECONDS";
                                        echo PHP_EOL . "RIGHT ELAPSED TIME IS $print_diff";


Leap Year and Daylight Savings Time

These are amazingly easy in PHP.  But beware of a bug in DateTime::add(), illustrated in this code.


3

Why snapshot as backup-strategy?

When working with large to huge databases it is critical to take a backup of the database for disaster recovery and data failures that application either cannot recover or regenerate corrected data. For a database of such size it is vital that backup time is as short as possible to minimize application downtime/disruption due to backups. Regular backups can take several hours or more to complete for very large to huge databases.
The fastest way today to take a database backup is using the snapshot technology (sometimes called FlashCopy) either on a filesystem level or if the database has such technology implemented.


ZFS filesystem and snapshots

ZFS uses copy-on-write transactional object model allowing old data to be retained as new data is written and allowing snapshot versions of the filesystem to be maintained.
Here I will demonstrate how you can use the Solaris zfs filesystem (ZFS on FUSE) to backup MySQL/MariaDB database in as short time as possible.


Preparing the VM

For this demonstration I'm using Centos 6.5 64 bit with zfs-fuse in a VirtualBox VM with 1 Core (2.3 MHz AMD A10 ) and 1GB RAM, even though recommended minimum for zfs-fuse is 2 GB.
The VM disks (VDI format) are as follows
  • 20 GB disk with the Centos 6.5 system installed.
  • Three empty 5 GB disks that will be under the zfs filesystem.  
First thing is to make sure the SELINUX is disabled and reboot the VM after editing the config file as root :

Open in new window

0
Introduction: When a new description on subject like subqueries is read, with various types and each with unique features, often readers try to make a mental picture of the process. Such a mental picture as a point of reference becomes very helpful when one attempts to apply the techniques to solve a problem. This is why the old saying "One Picture is Worth a Thousand Words" holds true. 

I have prepared the content in this article with visual presentation followed by descriptions and samples to make the path much easier for those interested in having a good understanding of the world of subqueries and retention of techniques to apply them later. Your comment below and/or indicating if this article was helpful is very much appreciated and could be used to improve my future work.

You may download Northwind.bak to restore in your local SQL Server from https://northwinddatabase.codeplex.com/ to practice or test most of the examples included in this article.  Also, consider printing figure 1 below to keep it handy for reference as you are reading the article.

0x.pngIn T-SQL we can use the results returned from one query in another. The embedded or referenced subquery (inner query) returns required data to enable the outer query to perform more complex operations compared to a simple select clause. Below is the list of different subqueries discussed later in detail:

1) Self-contained subqueries (embedded).
2) Correlated …
5
 

Expert Comment

by:Rayne
Comment Utility
Thank you Mike, this really clears a lot of key concepts :)
0
Dependencies in Software Design
In software development, the idea of dependencies is an issue of some importance. This article seeks to explain what dependencies are and where they can create a problem for growth or an opportunity for excellence.

In a nutshell, if program "A" calls upon program "B" for some data or service, program "A" is said to have a dependency on program "B." If the interface that program "A" uses to call program "B" changes, then program "A" must be changed, too. This sort of situation may be easy to fix if it is an isolated case (perhaps all calls to program "B" are routed through a particular class method in program "A"). However if the coupling is not an isolated interface, the dependency on program "B" can be a real risk to the continued health of program "A."

To try to illustrate this in practical terms, let's consider an issue that many PHP programmers are facing right now. They developed web sites or web applications that are dependent on the familiar but obsolete MySQL extension. Throughout their scripts, we find numerous instances of mysql_connect(), mysql_query(), mysql_fetch_assoc(), etc. There are red warning labels on all of those main pages! It has been a couple of years since PHP deprecated the MySQL extension
1
 

Expert Comment

by:calslim
Comment Utility
Another great article! I have started using DI more and more in my code. I have not as yet done much with phpunit and automated testing, largely because there does not seem to much in the way of real-world, yet easily approachable "getting started" material. For example, many tutorials start with the obligatory test add function. assert that a call to add(1,2) equals 3. This is all well and good, but then they fall off quickly and do a bit of handing waving over mocks, stubs, automation, etc. But I digress.

One question I have about DI, is how to decouple use of global functions (particularly in a framework). After all, PHP is essentially a language and a framework. I total get DI with objects. For example, instantiate an object, then pass that instance into a function or method of another object.

But what about decoupling functions?

A bit of a contrived example:

class Gatekeeper
{
    public $passphrase;
    private $secret;

    function __construct($phrase) {
        $this->passphrase = $phrase;
    }

   private function validate(){
       return $this->secret == md5($this->passphrase);
   }

  public response(){
     if ($this->validate()){
         return 'Welcome. May I take your coat?';
     }
     else{
         return 'Get lost!';
    }
  }
}

Open in new window


// The above code is dependent on MD5. MD5 is a function, not an instantiable class; what is best practice to inject this dependency analogous to:

$JoeTheBouncer =  Gatekeeper('Shazaam', md5);
$JoeTheBouncer->response();

Open in new window

0
Assuming MySQL Tables called "invoices" and "latefees" with the following columns:
 
CREATE TABLE IF NOT EXISTS `invoices`
(
  id BIGINT (255) NOT NULL AUTO-INCREAMENT,
  customer BIGINT (255),
  invoiceno VARCHAR (255),
  startdate DATE,
  isPaidStatus ENUM ("YES,NO") DEFAULT 'NO',
  payment_terms INT (5),
  latefee INT (10),
  subtotal DECIMAL ("6,2"),
  vat DECIMAL ("6,2"),
  total DECIMAL ("6,2"),
  total_addtional DECIMAL ("6,2"),
  PRIMARY KEY ( id)
) ENGINE = InnoDB ;

Open in new window

 

CREATE TABLE IF NOT EXISTS `latefees`
(
  id BIGINT (255) NOT NULL AUTO-INCREAMENT,
  type ENUM("%,Flat Fee") DEFAULT '%',
  frequency INT (5),
  amount DECIMAL ("6,2"),
  PRIMARY KEY (`id`)
) ENGINE = InnoDB ;

Open in new window

respectively.

1. This is the MySQL Code that produces the results:
 
SELECT 
	`A`.customer,`A`.id,`A`.invoiceno,`A`.total,`A`._total,`A`.`latefee`,`A`.startdate,`A`.payment_terms,
	`B`.name,`B`.type,`B`.frequency,`B`.amount,
	((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) - `A`.payment_terms) AS daysElasped,
	MOD(((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) - `A`.payment_terms), `B`.frequency) AS `isBillableDay`,
	IF(`B`.type=1,(`A`.total + `A`.total_addtional)*(`B`.amount / 100),`B`.amount) AS `new_amount`
FROM 
	`invoices` AS `A`
LEFT JOIN 
	`latefees` AS `B` ON `B`.id=`A`.`latefee`
WHERE 
	`latefee` > 0 AND 
	`isPaidStatus` = 'NO' AND
	((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) > `A`.payment_terms) AND 
	(MOD(((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) -`A`.payment_terms),`B`.frequency)=0)

Open in new window

This query basically does the following:
 
    a) Firstly checks if the invoice status is on "not paid yet"

    b) Calculates the number days ellapsed "daysElapsed" by checking invoice start date against the current date and then,

    c) Checks if the resultant "daysElapsed" exceeds the selected invoice terms and these could be "7 => Net 7 Days, 15 => Net 15 Days, 25 => Net 25 Days, 45 => Net 45 Days, 60 => Net 60 Days", and then 

    d) Checks if the remainder of our dividend "daysElapsed" minus invoice terms against our divisor latefee frequency is 0. This tells us that, today is the suitable day for us to pernalize this particular invoice by adding they appropriate latefee amount.

    e) If the invoice late fee selected is set to increament in percentages (%), then our new amount will be the  x % of the invoice's current amount.

    f) Else if the invoice late fee selectedd is set to be a Flate Rate then, our new amount will just be the latefee amount

2. Update the invoice with the new amount just calculated. Lets assume that we have saved our resultant new amount from the query above

i.e. say you are using PHP as your scripting language FIELDS[]"new_amount"]; ?>

 

Open in new window

0
 
LVL 2

Author Comment

by:Eric Mulovhedzi
Comment Utility
With all due respect that's not what i had in mind and that's not what i would like to do in the future. What you are advising me to do is to find a business model first before i contribute an article on your network.

I am a GENIUS but only on Coding and Software Development, i do lots of research and development stuff for Enterprises.

I also have my own business network portal where people can use ERP and CRM for free ( http://1stbn.co.za )

Two, i recently developed an Open Source server configuration utility tool "OVH Server Basement" (http://ovhserverbasement.co.za/) and whole lots of other genius commercial stuff.

The moral of the story is i have lots and lots of knowledge to share more especially on Programming and Development, i just do not have time to think of business models etc. Most of these articles are solutions i derived for problems that i experienced when i develop my own stuff or even clients stuff and i believe that there might be at-least one person out there who is still yet to go through what i went through and perhaps this article might just help them.

ALSO, i selected a tag called MySQL so automatically this article is targeted to someone who is already trying to write an SQL query for what ever reason that prompted them to do so.

Those are the people i was honestly having in mind when i was writing the article,  people who already have a problem or business model and are just searching for solution
0
 
LVL 2

Author Comment

by:Eric Mulovhedzi
Comment Utility
This is also one of the articles i contributed recently:

http://www.experts-exchange.com/Networking/Linux_Networking/A_13635-How-to-setup-a-local-Area-Network-between-two-Ubuntu-servers.html

It was published but to me i do not think has a business model to it because i just went straight to the point
0
I needed to calculate the working days between the date a request was made and today, searching the web I found this was a common request and found several ideas or code but most of them lack the ability to include the holidays.

As we already have something like this working on PHP I grab the idea of using a table containing the holidays as dates. And the code I found this thread appeal me for its simplicity
http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates
And code from
http://stackoverflow.com/users/554795/muhammad-haseeb-khan

However this FUNCTION didn't consider the holidays and for my specific needs it added one more day as my period of time starts the next day after the request is made, so I modify it to cover my needs.

I would like to note that on my first attempt to run this function, MySQL thrown out an error
"#1436 - Thread stack overrun:  6136 bytes used of a 131072 byte stack, and 128000 bytes needed."
So I modify My.CNF and increased the Thread Stack to 256K

Also this function includes a QUERY so it may not work on all versions of MySQL, it was tested and implemented on version 5.1.54-1 running on an UBUNTU box.

This is the CREATE for the holidays table

DROP TABLE IF EXISTS `YourDataBase`.`holidays`;
CREATE TABLE  `YourDataBase`.`holidays` (
  `DATE` DATE DEFAULT NOT NULL,
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT 

Open in new window

0
 
LVL 17

Author Comment

by:xema
Comment Utility
lherrou;
I didn't intended to cheat buy having two accounts, it was a mistake several years ago an the staf of EE was aware of this.
As for the article Y mut said english is not my primary languaje, Y will do some spell check and post it corrected

Thanks
0
Do you have a plan for Continuity?
LVL 2
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

The CRUD Functions
CRUD, meaning "Create, Read, Update, Delete" is a common term to data base developers.  It describes the essential functions of data base table maintenance.  This article lays out a minimum example showing how PHP and MySQLi work together to create a table maintenance script.  Of course you could use phpMyAdmin and get a lot more capability, but this is intended to be a teaching example showing the moving parts.  It can also serve as a model for maintenance of any table, with the classic example being a personal address book.  We will use just the names from our address book in this example, but it would be very easy to add other columns for email, phone numbers, etc.

The CRUD functions map to the SQL query commands more or less like this:
  Create = INSERT
  Read = SELECT
  Update = UPDATE
  Delete = DELETE

The HTTP Requests
In our scripts we will use both GET and POST request methods.  HTTP protocols tell us that GET can only be used when there is no change in the data model, therefore we will only be able to use the GET method for scripts that use SELECT queries.  The other three functions will require us to use POST method requests.  The method we use will be identified in the HTML form tag, via the method attribute.  If the method attribute is omitted, the assumption is GET.  If the action attribute is omitted, browsers will submit the request to the current URL.

GET is considered both safe and idempotent.  POST is neither.

5
Using Quotation Marks in PHP
This question seems to come up a lot for developers who are new to PHP.  And it got me thinking, "How can we explain the rules for quotation marks?"  For better or worse, PHP has so many rules!  This article tries to answer the questions.

First, some mandatory reading.  You need to understand the terms "variable" and "string" as they are used in the context of computer programming.  Take a moment to read these pages.  If it doesn't all "click" at first, don't be too concerned.  We will show some examples and explanation below.
http://php.net/manual/en/language.variables.basics.php
http://php.net/manual/en/language.types.string.php
http://php.net/manual/en/language.operators.string.php

Quotation Marks Used With Strings
A string is nothing more than a few characters strung together.  The code snippet below has examples of perfectly valid strings.  The first string is the single capital letter "A" and it has a length of one character.  The second string is the empty string.  It has a length of zero.  You can probably figure out the lengths of the other two strings.
A

Alphabet Soup
@@@

Open in new window


If you want to assign these literal string values to PHP variables, you must put quotes around them in the assignment statement.  Both of these examples are acceptable.  Note that one uses single quotes and the other uses double quotes.

$x = 'Alphabet Soup';
$y = "Alphabet Soup";

Open in new window

8
Today, we’ll be talking about how to do a Zip Code Radius Search in MySQL; how to set it up, and how to use it.

This has been tested in MySQL 5.1+, so please do not try it in anything lower than this, because it probably will not work.

Now, you may ask… ‘Why do I need a ZipCode Radius Search?’, even though I do not know the answer to your question, I do know why I needed it.  Simply put as a store locator service for a client.  Since there are a few ways to do this, I will only be showing you how I did it (as I found and tested other methods, this method seemed to be the most accurate while being the most efficient in terms of query speed).

First things first, you will need a table to store all 81k+ records from the latest zipcode dump.  We will be using a MyIASM storage system so we can take full advantage of the Spatial Indexing features MySQL has graciously included, but more on that as we go, for now, here is what I did:

Table: ZipCodes


/* Create our table with these basic columns */
CREATE TABLE `zip_codes` (
  `zip` varchar(10) NOT NULL,
  `lat` float NOT NULL DEFAULT '0',
  `lon` float NOT NULL DEFAULT '0',
  `city` varchar(255) NOT NULL DEFAULT '',
  `state` char(2) NOT NULL DEFAULT '',
  `county` varchar(255) NOT NULL DEFAULT '',
  `type` varchar(255) NOT NULL DEFAULT '',
  `Location` point NULL,
  KEY `city` (`city`,`state`),
  KEY `lat` (`lat`),
  KEY `lon` (`lon`),
  KEY `lat_2` (`lat`),
  KEY `lon_2` (`lon`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window


We are using a NULL POINT column temporarily, so we can do our ZipCode data load, once this load is completed, run the following statement against the table to update the Location column:
UPDATE zip_codes SET Location = POINT(lat, lon);

Open in new window


This will get our Geographical POINT data set and ready to create our Spatial Index, now you can change this column back to NOT NULL, and create the Spatial Index on it.

Now, I use procedures for every CRUD operation I need, but for this exercise, I’ll just give you the direct query:

Query:



Open in new window

1
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nice article.  I had a need to do this back in 2001 when designing a client's 'Find your sales rep' functionality in ASP and SQL 7, and remember that it was not possible back then.
0
The canonical version of this article is on my web site here:
http://iconoun.com/articles/collisions/

A companion presentation is available here:
http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
15
 
LVL 110

Author Comment

by:Ray Paseur
Comment Utility
@jason1178: Coming from you that is a great compliment!  Thanks, ~Ray
0
 
LVL 16

Expert Comment

by:DansDadUK
Comment Utility
An excellent article.

I spent years persuading other people (in the Western world) to use the 8-bit ISO-8859-1 coded character set, rather than switching between various National Language Variants of the 7-bit US-ASCII coded character set in order to use (relatively common) accented characters.

Moving to UTF-8 is the way to go now that 256 characters (strictly 191 graphic characters plus 65 control codes) of an 8-bit encoding is no longer sufficient in our increasingly global environment.
0

Foreword (May 2015)

This web page has appeared at Google.  It's definitely worth considering!

https://www.google.com/about/careers/students/guide-to-technical-development.html


How to Know You are Making a Difference at EE

In August, 2013, one of my colleagues posted this, and it made my heart glad.

Grading Comment:
Thanks so much Ray!  I actually haven't posted in a while because at your suggestion I got a few books and spent the last couple of months reading and learning and it's made a world of difference, so thanks a ton for that suggestion as well!

"And by the way,... I am New to PHP"

So many PHP questions at Experts-Exchange include (or should include) that statement that it got me thinking about how to advise new programmers on ways to begin learning PHP.  The popularity of PHP is undeniable; it powers an overwhelming number of web sites, from the very smallest to the giants like Facebook, hundreds of thousands of WordPress blogs, and everything in between.  So it makes sense that any web developer would need to have some foundation in the PHP language.  And for many novice programmers, PHP is the first language they try to learn.


PHP came from humble beginnings; it was originally captioned "Personal Home Page" and was intended to be a language so easy to use, that "even Grandma could learn to use PHP."  And that was fine in the 1990's before the advent of object-oriented programming, hackers, spam, and web services.  But the online environment has grown up and PHP has been forced to grow up, too.  Today, while PHP can still perform simple tasks, PHP can also power enormous database-driven applications that build online communities and analyze mountains of information.


My favorite description of PHP, disarming in its elegance and simplicity, is "What PHP can do is convert a static website that has content that has to be changed by hand into a dynamic one that can display content based on any criteria you can think of."


Getting started with PHP can be daunting, especially if you do not have a background in computer science.  This article will give you some learning resources to get the background you need and will introduce several popular and effective ways to learn PHP.  But there is something you might want to read before you finish this article.  Every professional programmer I know thinks that this is an article of wisdom.  You can take it in if you have seven minutes.  Then come back and finish this article.


Getting the Right Foundation

If you really want to be a professional programmer, you probably want to go to college to major in computer science or electrical engineering.  Almost every major college and university teaches these subject, and the principles are not very hard to learn, but it takes time and practice to turn the learned principles into praxis.  Even if you're not sure about making a career in programming, if you're read this far you probably want to consider taking an Introduction to Computer Science class.  Fortunately, these classes are now available online, for free, using the same syllabus, curriculum and learning materials that the colleges use.  


As but one example, here is what M.I.T. offers:

http://ocw.mit.edu/courses/electrical-engineering-and-computer-science/6-00sc-introduction-to-computer-science-and-programming-spring-2011/Syllabus/


If you're serious about learning programming, you will need a little math (but not very much).  This series of video lectures will give you a good foundation.

http://ocw.mit.edu/courses/electrical-engineering-and-computer-science/6-042j-mathematics-for-computer-science-fall-2010/video-lectures/


Learning to Think Like a Programmer

For a variety of reasons, you're unlikely to find a basic computer science class that teaches PHP.  PHP is "easy" and these classes are for scientists who like things hard (I'm only joking a little bit).  Expect to find C, Python, Java, JavaScript and similar highly structured programming languages in the beginning classes.  Don't worry about that -- every programmer who achieves anything noteworthy has written programs in many different languages.  The ideas from one language or another bring about a level of intellectual cross-pollination that helps you think about problems from different perspectives and map problems onto different computational frameworks.  And at the most basic levels, all programming languages share many common aspects.  The greatest difference is the written syntax we use to express our ideas.


Do Not Waste Time on Non-Working Code

A programming language is a tool that builds a sequence of instructions.  The instructions tell the computer what we want it to do.  For almost everything you might do with PHP, the thing we want the computer to do is manipulate data.  Each programming problem can be defined and understood in terms of an input and an output, with the computer programming being the intermediary that transforms the input into the output.  If you think about that for a little while, you will come to the same conclusion that almost every experienced programmer comes to: it does nobody any good to post code that does not work.  Instead define and post the data and you will be amazed how quickly you can find clear, easy-to-understand answers.


Expect to Spend a Lot of Time Debugging

You will find that you're human and the computer is not.  Problems expressed in human languages are turned into solutions expressed in computer languages.  This process is full of ambiguity, double-meanings, and confusion.  Humans share culture, but computers require precision at a sub-microscopic level.  Even a single character out of place can render a computer program useless (or worse, destructive).  It's very rare that a programmer can write more than a few lines of code without introducing an unwanted element.  The process of correcting and removing these unwanted elements is called "debugging" and until you have several years of programming experience, it will consume most of the time you spend programming.  More about programming and human factors, as well as many interesting observations from the trenches is available in Jeff Atwood's writings.


Deconstruct Problems Until They Are Small Enough to Solve at a Glance

Complex programs are made up of simpler programs, and simpler programs are made up of even simpler programs.  A mental process of deconstructing problems underlies all programming activities.  As you learn PHP, you will find that you deconstruct complex problems into several smaller problems that are readily solved using PHP programming solutions.


You may have heard of the phrase, "Hello World."  It is the name given to the simplest example of the solution to any programming problem, and it demonstrates success in some small aspect of building a more complex program.  In PHP, the "Hello World" exercise looks something like this.


 

<?php echo "Hello World.";

This simple PHP script proves that PHP is installed correctly and that it can produce output that is visible on a web browser.  More importantly, it removes many elements of confusion and ambiguity.  What if this script did not work?  Where would you look for a solution?  What symptoms of failure could you find?  Who can help?  All of these uncertainties and doubts impinge on the process of debugging.  And thus we come to the first and most important part of thinking like a programmer: Make the larger problems smaller by reducing each of the larger problems to a collection of smaller problems.


Do this iteratively until the smaller problems are so small that the solution is obvious.  The small solutions then become the building blocks of the larger solutions.  As you construct larger solutions from smaller building blocks, you will hide the details so that the result works like a vending machine - providing useful services to many clients (either programs or people).  Programmers often refer to such self-contained units as a "black box."


Learning the Language of Programmers

A shared culture has a shared language and many terms of art.  You can learn something of PHP without knowing much about general purpose programming, but your learning will be faster and easier if you understand the language that programmers use.  Many familiar words assume new meanings.  For example, the word class takes on a unique meaning when it's applied in the context of computer programming.


I am not suggesting that you need to read or memorize every term of art (a professional will) but you need some good references that you can use to look up words that are unfamiliar or are used in unfamiliar ways.  You might want to take an afternoon to browse these glossaries.


LabAutopedia

Henson's Glossary

Cal State CS202

WhatIs.com TechTarget Programming Words


Learning the Techniques of Programmers

All good programmers have been forced at one time or another to research a subject that they know nothing about.  They have to find out numerous details (too many to remember) and develop code that handles these details.  It's a huge exercise in short-term memory, and it's a huge investment of time.  You wouldn't want to have to do it over again.


Unfortunately, some programmers are condemned to do it over and over again because they didn't write the code clearly, or they used thoughtless variable names, or they left out the comments.  And when they need to reuse that piece of code, they find themselves looking at it and wondering things like, "Did I test this enough?" or worse, "What was I thinking?"  You can avoid this problem by following a few simple rules.


Before you begin writing code, learn the "PSR" basics.  These are valuable standards.  They will make your programming easy to read and understand, and they will make your code look professional (highly valuable if you want to get a job as a programmer).  Here are the two links you should study.

http://www.php-fig.org/psr/psr-1/

http://www.php-fig.org/psr/psr-2/


1. Whenever you're required to solve a problem that you have never solved before, make a teaching example from the solution.  Collect a library of these.

2. Whenever possible use meaningful variable names, eg, if the value is "Today's date" name the variable $today instead of something useless like $x.

3. Use comments to say what your programming is intended to do.  Often you will find that simply writing the commentsfirst, before you write a single line of code, will help you consolidate your thinking.

4. Test iteratively as you build the code.  Don't write more than a few lines without testing.  Use the principles of Test-Driven Development.

5. Programming is all about the data.  Learn about var_dump() and make regular use of it!

6. Avoid copying code you find on the internet, especially code that has a lot of compound statements.  Instead of copying, deconstruct the code, taking a moment to ask, "why?" and rewriting the code in your own words, with your own explanatory comments.

7. Adopt a coding standard and adhere to it rigidly.  With discipline comes great power.


Learning How to Get Help: the SSCCE

Anyone who says he is a self-taught programmer is missing something.  You cannot make this stuff up - you have to learn it from a variety of sources, and one of the most important sources is the community of programmers who surround you.  Perhaps you're in a university class where you can learn from your colleagues and classmates.  Or perhaps you're a regular at Experts-Exchange and Stack Overflow.  In any learning community you need to know how to share ideas with others.  That's where the SSCCE comes in.  If the only thing you take away from this article is the SSCCE, it will have been worth my time to write it and worth your time to read it.  Follow the link, read the page (there is only one page) and embrace the principles!  The SSCCE is not alone in the universe, other authors have given voice to the same concept: a problem well stated is a problem half solved.


Think about the data - what do you have for input and what do you want for output.  Try to assemble that information before you ask the question.  Then you can say, "Here's what I've got and here's what I want."  That makes for a clear problem definition.  Clear problem definitions will save you an amazing amount of time.  As Charles Kettering said, "A problem well stated is a problem half solved."


Try to avoid complicating the situation with an unnecessary explanation, when an input/output example could be used instead.  This is an actual quote from a question posted here at EE: "take that number, split it into an array. so 25 would become [2,5], or 12 would become [1,2] then use those to determine which image to show in the basket count, then loop the array to determine which images to show"  What's the author trying to say?  Your guess is as good as mine.


Learning PHP from PHP.net

Now that we have introduced the deep background stuff, and shown you how to frame a problem and ask for help, it's time to get into the part of this article that is about learning -- and using -- PHP.


A good place to start is with php.net, where you will find the best online technical documentation in the world.


If you do not read the online documentation, you're robbing yourself, stealing valuable time from learning.  Instead of learning from the experience and writings of others, you'll be learning by trial and error, maybe from reading other people's programs.  That takes longer.  A lot longer.  And it's full of risk.  Trying to learn programming by trial and error is like trying to learn to bake by looking at an apple pie.  Sure you can appreciate the finished product, but looking at a pie will tell you nothing of the other ingredients, the processes and the tools used to make the pie.  Likewise, a finished program tells you nothing of the thought processes of the developers, nor of the assumptions they made or the test data they used.  So don't try to learn that way.  Instead, exploit the php.net web site for all its worth.  Here is how.


New in 2014: PHP has an About the Manual page, including How to Read a Function Definition and links to Support for New Users.


PHP has a "getting started" page.

PHP has an introductory tutorial.  No excuses -- Just Do It!

PHP has an omnibus FAQ page.

PHP has its entire manual online!


PHP has its own Security page and it is required reading, no excuses!


PHP has the language reference online.   No excuses here either.  You must read these sections and every one of the associated links:

Basic Syntax

Types

Variables

Constants

Expressions

Operators

Control Structures

Functions

Predefined Variables


If you want to move beyond basic PHP programming and begin collaboration with others, you want to read these sections, too.

Classes and Objects

Exceptions

References


If you want to interact with software or data on other servers you need to read these sections.

Context Options

Protocols and Wrappers


If you want to learn from the collective wisdom of others, these FAQ pages are very helpful:

Using PHP

PHP and HTML

PHP and Databases


Part of what makes php.net so valuable is the function reference.  PHP has, at this writing, over 1,500 built-in functions.  You can't memorize all of them, so you need a quick way to refer to them.  When I am programming I have a window open to php.net at all times.  If you know the name of the function you want to use, just type a URL like this: http://php.net/date and PHP will find the date() function.  Couldn't be easier!  And the magic doesn't end with the official PHP documentation.  Every function page has a section for user-contributed notes.  Often, seeing the way others have used a function (or learned from an unpleasant surprise) will give you great insights into the richness and power of the PHP language.  For example, see this:

http://php.net/manual/en/function.array-slice.php#112359


If you don't know the name of the function you want to use, you can find the function reference table of contents.   Don't plan on reading all of this.  You might want to bookmark it.


The parts of the function reference that you want to read first are probably these:

Date/Time

Database, especially MySQL

File System

Strings

Arrays

Variables

Sessions

SimpleXML

JSON


Learning PHP from Books

One of the really great things about tech people is that they like tech features, for example, the ability to write book reviews on Amazon.com. If you find a popular and well-reviewed book (and it's not too old) it will probably make a valuable addition to your technical/professional library.  You can't have too many PHP books!  Here are some of the books that I own and recommend.  If you decide to get any of these, be sure to look for the latest editions, because PHP is a living language.


Beginner: Yank Ignore the hokey title -- it's a good book.

Beginner to intermediate: Welling/Thompson

Intermediate to Advanced: Powers

Intermediate to Advanced: Ullman

Advanced: Zandstra


Learning PHP from Online Resources

Most of these resources are 100% free:  Almost anything about PHP from SitePoint is worthwhile.  There are good introductory references at Tizag and W3Schools, however the Tizag PHP web site appears to have gone out of date.  Many of my students swear by CodeAcademy.  And of course there is a Wiki: http://en.wikibooks.org/wiki/PHP_Programming


New in 2017: Stefan Priebsch curates this site on Software Craftsmanship (advanced)

https://php-craftsmanship.info/


New in 2016: Jeffrey Way brings his considerable teaching skills to a set of PHP courses here:

https://laracasts.com/series/php-for-beginners


While you are mastering the basics of PHP you will have many questions that others have had before you.  This is a worthwhile resource for many of those questions.

https://phpbestpractices.org/


Once you have mastered the basics of PHP, there is an excellent online resource from the authors of the Slim Framework.

http://www.phptherightway.com/


A set of paid courses on PHP is available from lynda.com.  It is not clear to me whether these are up-to-date.


A set of paid courses on PHP, from active PHP experts, is available from PHP Architect.  The company also publishes books and newsletters, and runs conferences covering PHP topics from the most introductory "bootcamp" to advanced object-oriented design.  If you want to learn from the best in the business, this is the place to come.


Learning PHP from "That Guy" -- NOT!

You've heard the expression, "Don't be that guy."  I'm going to add a recommendation here: "Don't learn PHP from 'That Guy'."  The internet is littered with examples of terrible PHP code.  Mostly it's code from half-taught programmers who don't understand the principles of computer science, don't understand the principles of programming security and don't take the time to clean up after themselves.  It turns up in forums all the time.  It may be untested.  It's almost always undocumented.  And it's something you would use at your own peril.  It's usually worth exactly what you paid for it!  So just say "no" to that stuff and stick to the links published here.  It will save you a lot of grief.


Summary

Don't feel awkward or embarrassed if you're new to PHP.  Ignorance is no sin and we were all new to the language once.  Instead, embrace the learning resources documented here, build your own personal library of code examples, post plenty of PHP questions at Experts-Exchange and most importantly give yourself time to work with the language, so that you become familiar with the commonly used parts.  The gift of time and study is one that only you can give yourself, and it may be the most valuable gift of all. And if you're doing it right, learning PHP is fun.


Welcome to PHP!


Please give us your feedback!

If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles.  If you have questions or comments, please add them.  Thanks!

 

30
 
LVL 83

Expert Comment

by:Dave Baldwin
Comment Utility
Another thing that I have had to almost beat into some people's heads is that you can't 'logically' figure these things out.  While computers at the lowest levels operate logically, the things that people actually deal with like programming or even just using a program are based on the Choices made by someone somewhere.  You can't logically out-guess the choices made by someone else, you need to look them up in the documentation and find the rules that apply.  

The PHP developers are constantly trying to make function calls as uniform and predictable as they can... but there are a lot of things that have slipped in over the years.  And another truckload of things that are simply non-obvious.  Even those of us who write PHP code virtually every day have to do that.  People pay me to fix and modify other people's PHP code.  Like Ray, I have a browser window open to php.net almost all the time.
1
 
LVL 110

Author Comment

by:Ray Paseur
Comment Utility
@DaveBaldwin: Yes, that is the way!  Just look at an apple pie and see if it can tell you how to bake an apple pie.  It's the same with any recipe and the same with computer code.  There is not always a light unto my feet and a path before me unless I care for the details along  the way.
0
This is a continuation to my previous article "Common pitfalls to avoid in MySQL - Part 1"

This article looks at two "gotchas". One with Join and the other with Check constraint.

For those of you who have come to this article directly read the note below. Others can directly jump to the point. Also note that in addition to the two tables from the previous article I am using one more table "employeeactivity" and have given the details below after the note.

Please Note : All I have written and executed here in this article is performed using MySQL version 5.0.27 with the default settings that comes when you install. People are welcome to comment and suggest if any of these pitfalls can be overcome by any changes in configuration or settings. Also if any of this has changed in the newer versions, please feel free to comment.

I will be using 3 tables for my examples.
First is the employee table as shown below
Employee table all records with data typeThe second is employee communication table which is as follows
EmployeecommunicationInfo tableThe third is the employee activity table which is as follows
Employeeactivity table

1. Join on a comma or a pipe separated value still joins



This is a very strange behavior that I observed, from the above tables, if I execute the query below
SELECT activityName,employeeid,id,firstName,lastName 
FROM employeeactivity 
LEFT JOIN employee ON employeeid = id

Open in new window

You expect the first name, last name for the last row with activity name "Approved Workflow" to be NULL as the value "1,3" does not match any of the values of the primary key in the employee table.

But what actually happens is that it does join
1

MySQL Server

46K

Solutions

22K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.