Monday, 21 May 2018

Working With PHP Arrays in the Right Way

In this tutorial, I am going to make a list of common PHP array functions with examples of usage and best practices. Every PHP developer must know how to use them and how to combine array functions to make code readable and short.
Also, there is a presentation with given code examples, so you can download it from the related links and show it to your colleagues to build a stronger team.

The Basics

Let's start with the basic functions that work with array keys and values. One of them is array_combine(), which creates an array using one array for keys and another for its values:

You should know, that the function array_values() returns an indexed array of values, array_keys() returns an array of keys of a given array, and array_flip() exchanges keys with values:

Make Your Code Shorter

The function list(), which is not really a function, but a language construction, is designed to assign variables in a short way. For example, here is a basic example of using the list() function:
This construction works perfectly with functions like preg_slit() or  explode() . Also, you can skip some parameters, if you don't need them to be defined:
Also, list() can be used with foreach, which makes this construction even better:

With the extract() function, you can export an associative array to variables. For every element of an array, a variable will be created with the name of a key and value as a value of the element:
Be aware that extract() is not safe if you are working with user data (like results of requests), so it is better to use this function with the flags EXTR_IF_EXISTS and EXTR_PREFIX_ALL.
The opposite of the previous function is the compact() function, which makes an associative array from variables:

Filtering Functions

There is a great function for array filtering, and it is called array_filter(). Pass the array as the first param and an anonymous function as the second param. Return true in a callback function if you want to leave this element in the array, and false if you don't:

There is a way to filter not only by the values. You can use ARRAY_FILTER_USE_KEY or ARRAY_FILTER_USE_BOTH as a third parameter to pass the key or both value and key to the callback function.
Also, you can call array_filter() without a callback to remove all empty values:
You can get only unique values from an array using the array_unique() function. Notice that the function will preserve the keys of the first unique elements:
With array_column(), you can get a list of column values from a multi-dimensional array, like an answer from a SQL database or an import from a CSV file. Just pass an array and column name:
Starting from PHP 7, array_column() becomes even more powerful, because it is now allowed to work with an array of objects. So working with an array of models just became easier:

Walking Through the Arrays

Using array_map(), you can apply a callback to every element of an array. You can pass a function name or anonymous function to get a new array based on the given array:

There is a myth that there is no way to pass values and keys of an array to a callback, but we can bust it:
But this looks dirty. It is better to use array_walk() instead. This function looks the same as array_map(), but it works differently. First of all, an array is passed by a reference, so array_walk() doesn't create a new array, but changes a given array. So as a source array, you can pass the array value by a reference in a callback. Array keys can also be passed easily:

Joining the Arrays

The best way to merge two or more arrays in PHP is to use the array_merge() function. Items of arrays will be merged together, and values with the same string keys will be overwritten with the last value:
To remove array values from another array (or arrays), use array_diff(). To get values which are present in given arrays, use array_intersect(). The next examples will show how it works:

Do the Math With Array Values

Use array_sum() to get a sum of array values, array_product() to multiply them, or create your own formula with array_reduce():

To count all the values of an array, use array_count_values(). It will give all unique values of a given array as keys and a count of these values as a value:

Generating Arrays

To generate an array with a given size and the same value, use array_fill():

To generate an array with a range in of keys and values, like day hours or letters, use range():
To get a part of an array—for example, just the first three elements—use array_slice():

Sorting Arrays

It is good to remember that every sorting function in PHP works with arrays by a reference and returns trueon success or false on failure. There's a basic sorting function called sort(), and it sorts values in ascending order without preserving keys. The sorting function can be prepended by the following letters:
  • a, sort preserving keys
  • k, sort by keys
  • r, sort in reverse/descending order
  • u, sort with a user function
You can see the combinations of these letters in the following table:

Array Functions

Array Functions
  • array_change_key_case — Changes the case of all keys in an array
  • array_chunk — Split an array into chunks
  • array_column — Return the values from a single column in the input array
  • array_combine — Creates an array by using one array for keys and another for its values
  • array_count_values — Counts all the values of an array
  • array_diff_assoc — Computes the difference of arrays with additional index check
  • array_diff_key — Computes the difference of arrays using keys for comparison
  • array_diff_uassoc — Computes the difference of arrays with additional index check which is performed by a user supplied callback function
  • array_diff_ukey — Computes the difference of arrays using a callback function on the keys for comparison
  • array_diff — Computes the difference of arrays
  • array_fill_keys — Fill an array with values, specifying keys
  • array_fill — Fill an array with values
  • array_filter — Filters elements of an array using a callback function
  • array_flip — Exchanges all keys with their associated values in an array
  • array_intersect_assoc — Computes the intersection of arrays with additional index check
  • array_intersect_key — Computes the intersection of arrays using keys for comparison
  • array_intersect_uassoc — Computes the intersection of arrays with additional index check, compares indexes by a callback function
  • array_intersect_ukey — Computes the intersection of arrays using a callback function on the keys for comparison
  • array_intersect — Computes the intersection of arrays
  • array_key_exists — Checks if the given key or index exists in the array
  • array_keys — Return all the keys or a subset of the keys of an array
  • array_map — Applies the callback to the elements of the given arrays
  • array_merge_recursive — Merge two or more arrays recursively
  • array_merge — Merge one or more arrays
  • array_multisort — Sort multiple or multi-dimensional arrays
  • array_pad — Pad array to the specified length with a value
  • array_pop — Pop the element off the end of array
  • array_product — Calculate the product of values in an array
  • array_push — Push one or more elements onto the end of array
  • array_rand — Pick one or more random keys out of an array
  • array_reduce — Iteratively reduce the array to a single value using a callback function
  • array_replace_recursive — Replaces elements from passed arrays into the first array recursively
  • array_replace — Replaces elements from passed arrays into the first array
  • array_reverse — Return an array with elements in reverse order
  • array_search — Searches the array for a given value and returns the first corresponding key if successful
  • array_shift — Shift an element off the beginning of array
  • array_slice — Extract a slice of the array
  • array_splice — Remove a portion of the array and replace it with something else
  • array_sum — Calculate the sum of values in an array
  • array_udiff_assoc — Computes the difference of arrays with additional index check, compares data by a callback function
  • array_udiff_uassoc — Computes the difference of arrays with additional index check, compares data and indexes by a callback function
  • array_udiff — Computes the difference of arrays by using a callback function for data comparison
  • array_uintersect_assoc — Computes the intersection of arrays with additional index check, compares data by a callback function
  • array_uintersect_uassoc — Computes the intersection of arrays with additional index check, compares data and indexes by separate callback functions
  • array_uintersect — Computes the intersection of arrays, compares data by a callback function
  • array_unique — Removes duplicate values from an array
  • array_unshift — Prepend one or more elements to the beginning of an array
  • array_values — Return all the values of an array
  • array_walk_recursive — Apply a user function recursively to every member of an array
  • array_walk — Apply a user supplied function to every member of an array
  • array — Create an array
  • arsort — Sort an array in reverse order and maintain index association
  • asort — Sort an array and maintain index association
  • compact — Create array containing variables and their values
  • count — Count all elements in an array, or something in an object
  • current — Return the current element in an array
  • each — Return the current key and value pair from an array and advance the array cursor
  • end — Set the internal pointer of an array to its last element
  • extract — Import variables into the current symbol table from an array
  • in_array — Checks if a value exists in an array
  • key_exists — Alias of array_key_exists
  • key — Fetch a key from an array
  • krsort — Sort an array by key in reverse order
  • ksort — Sort an array by key
  • list — Assign variables as if they were an array
  • natcasesort — Sort an array using a case insensitive "natural order" algorithm
  • natsort — Sort an array using a "natural order" algorithm
  • next — Advance the internal pointer of an array
  • pos — Alias of current
  • prev — Rewind the internal array pointer
  • range — Create an array containing a range of elements
  • reset — Set the internal pointer of an array to its first element
  • rsort — Sort an array in reverse order
  • shuffle — Shuffle an array
  • sizeof — Alias of count
  • sort — Sort an array
  • uasort — Sort an array with a user-defined comparison function and maintain index association
  • uksort — Sort an array by keys using a user-defined comparison function
  • usort — Sort an array by values using a user-defined comparison function

Wednesday, 18 April 2018

Top 10 MySQL Best Practices

Many groups and individuals are involved in the field of data management, from MySQL administrators, architects, developers, as well as infrastructure support people. Each of these plays a part in the security, maintenance, and performance of a MySQL installation. Therefore, when speaking of best practices, one has to consider which of these functions that specific practice pertains to. In this top 10 list, I will try to include a bit from each discipline. I have considered my own experiences as well as consulted with numerous other sources to compile this final list. Whether or not you agree with each and every item and their ordering, the important thing is to at least consider each of the points raised here today. So, without further ado, here is my personal top 10 list.

1. Index Search Fields

You should always index columns that you plan on searching on. Creating an index on a field in a table creates another data structure which holds the field value, and pointer to the record it relates to. This index structure is then sorted, allowing binary searches to be performed on it. An index can be defined for a single column or multiple columns of a given table.
This rule also applies to fields where partial string searches will be performed on the start of the field. For instance the phrase "last_name LIKE 'rob%'" will use the index, whereas "WHERE last_name LIKE '%ert%'" will not.
This does not imply that the more indexes you have, the better. While that is true to a point, keep in mind that every index takes up disk space. In a MyISAM database, the index file can quickly reach the size limits of the underlying file system if many fields within the same table are indexed.

2. Avoid Using "SELECT *" in your Queries

As a general rule, the more data is read from the tables, the slower a query becomes. Considering that some production tables may contains dozens of columns, some of which comprises of huge data types, it would be foolhardy to select all of them. A database server which is separate from the web server will only aggravate this issue, due to the data having to be transferred across the network.
To reiterate, it is a good habit to always specify which columns you need when writing your SELECT statements.

3. Set a password for the "root" user and then rename the user.

Here's a security tip. Much like with UNIX, the first thing you should do with a clean MySQL install is set a password for the root user:
$ mysqladmin -u root password NEWPASSWORD
Even better, once you've set the password, change the name of the "root" user to something else. A hacker on a MySQL server will likely target the root, both for its superuser status and because it is a known user. By changing the name of the root user, you make it all the more difficult for hackers to succeed using a brute-force attack. Use the following series of commands to rename the "root" user:
$ mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where 
User='<userid>';
mysql> flush privileges;
mysql> quit

4. Tune your Queries with EXPLAIN

The EXPLAIN keyword is undoubtedly the most instructive analytical tool in the MySQL arsenal. Using it can give you valuable insight on the steps that MySQL is taking to execute your query. This can help you spot the bottlenecks and other problems with your query or table structures.
In my Optimizing MySQL Query Retrieval Speed through Table Joins article, I included how to use EXPLAIN to ascertain the efficiency of the table joins in the following query statement:
explain
select a.au_lname,
a.au_fname, 
ta.royaltyper, 
t.title, 
t.royalty
from authors a,
titleauthor ta,
titles t
where a.au_id = ta.au_id
and ta.title_id = t.title_id
EXPLAIN produced the following results:
id  select_type table   type    possible_keys   key         key_len  ref                             rows    Extra
1   SIMPLE      ta      ALL     (NULL)          (NULL)      (NULL)   (NULL)                          800 
1   SIMPLE      t       ref     NewIndex        NewIndex     23      crosstab_article.ta.title_id    100     Using where
1   SIMPLE      a       ALL     (NULL)          (NULL)      (NULL)   (NULL)                          1000    Using where; 
                                                                                                             Using join buffer
The results of an EXPLAIN query will show you which indexes are being utilized, how the table is being scanned and sorted, and other useful information. At the very least, you can be sure that the lower the numbers appear in the rows column, the faster the query should run.

5. Index and Use Same Column Types for Joins

If your query contains many joins, you need to make sure that the columns that make up a join are indexed on both tables. This will allow MySQL to better optimize the join operation.
Likewise, the columns that are joined must share the same type. For instance, if you join a DECIMAL column to one of the type INT, MySQL will be unable to use at least one of the indexes. String type columns must also use the same character encoding.

6. LIMIT 1 When Getting a Unique Row

There are some queries that are meant to only return one row, such as those which fetch a unique record, or that verify whether or not there are any records that satisfy the WHERE clause. In such cases, adding LIMIT 1 to your query can increase performance. This reduces execution time because the database engine will stop scanning for records after it finds the first matching record, instead of going through the whole table or index.
A second popular usage is in subqueries. In the following SELECT statement, we want to retrieve the first s2 field value sorted by the s1 column. We can then match it against the outer query values:
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);

7. Hide MySQL from the Internet

Most experienced database administrators and security personnel know to never host the database under the Web server's root. For Web-enabled applications, MySQL should be hidden behind a firewall and communication should only be enabled between application servers and your Web servers. Another option is to use MySQL's skip-networking option. When it is enabled, MySQL only listens for local socket connections and ignores all TCP ports.

8. Use the Smallest Data Types Possible

This would seem to be common sense to me, because of my programming background. When I was attending college, there was still some of the "memory is scarce" philosophy carried over from the days of 256 MB hard drives. Nowadays, no one seems to care one iota about memory or hard drive space. "Memory is cheap!" is the new adage. While it is true in dollar terms, it still takes longer to read in large data types than smaller ones, as the former require more disk sectors to be read into memory.
The moral of the story is to ignore the temptation to immediately jump the largest data type when designing your tables. Consider using an int rather than a bigint. You should also avoid large char(255)text fields when a varchar or smaller char() will suffice. Using the right data type will fit more records in memory or index key block, meaning fewer reads, ergo faster performance.

9. Create Views to Simplify Commonly-used Table Joins

As discussed in my Writing Reusable Queries in MySQL article, views help to both simplify complex schema and to implement security. One way that views contribute to security is to hide auditing fields from developers. They can also be used to filter out unindexed columns, leaving only fields that are fastest to search on. The only caveat to using this technique is that you must be fairly sure that you won't need to access one of the hidden table columns in the future; not an easy thing to do!

10. Take Advantage of Query Caching

The query cache stores the text of a SELECT statement together with the corresponding result set. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client. Most MySQL servers have query caching enabled by default. It's one of the most effective methods of improving performance.
It's a beautiful thing, but query caching isn't without limitations. Take the following statement:
SELECT emp_id, 
    bonus_id 
FROM  bonuses 
WHERE YEAR(award_date) = Year(CURDATE());
The problem here is that queries, which contain certain non-deterministic functions - that is those which MySQL cannot calculate in advance - like NOW() and RAND() are not cached. Fortunately, there is an easy fix to prevent this from happening. That is to store the function results to a variable:
SET @year = Year(CURDATE());
SELECT emp_id, 
    bonus_id 
FROM  bonuses 
WHERE YEAR(award_date) = @year;
And that's my personal top 10 best practices in MySQL. One thing that I noticed while conducting my research for this article is that there is a wide range of opinions as to which practices should be worthy of a spot in the top 10. In my estimation, there are many factors that may affect an item's weight for you, including your particular role, experience, line of business, software versions, and hardware configuration, to name but a few. I would welcome you to add your own contributions as comments. Every tip helps!

MySQL INSERT Statement Variations

The Data Manipulation Language (DML) SELECT...INTO command is unsupported in MySQL. However, MySQL does provide the INSERT...SELECT statement. Rob Gravelle discusses this, and other variations of the INSERT statement.
In the MySQL Data Manipulation and Query Statements article, we looked at two variations of the INSERT INTO statement. If you recall, we utilized the INSERT statement to populate tables, rather than the Data Manipulation Language (DML) SELECT...INTO command, which is unsupported in MySQL. However, MySQL does provide the INSERT...SELECT statement. This, and other variations of the INSERT statement will be the topic of today’s article.

Inserting Multiple Rows

The INSERT INTO syntax that we used came in two forms – one with column names and one in which they were omitted:
Form 1:
INSERT INTO table_name
VALUES (value1, value2, value3,...)
Form 2:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Here are some examples of both forms:
INSERT INTO "employees" ("id", "shop_id", "gender", "name", "salary") 
VALUES (1,1,'m','Jon Simpson',4500);
INSERT INTO "employees" VALUES (1,1,'m','Jon Simpson',4500);
The above “VALUES“ syntax can also be used to insert multiple rows by including lists of column values, each enclosed within parentheses and separated by commas. Here’s the syntax for that along with an example:
INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
 
INSERT INTO 'employees' 
       ('id', 'shop_id', 'gender', 'name',                 'salary') 
VALUES (1,    1,         'm',      'Jon Simpson',          4500),
       (2,    1,         'f',      'Barbara Breitenmoser', 4700),
       (3,    2,         'f',      'Kirsten Ruegg',        5600),
       (4,    3,         'm',      'Ralph Teller',         5100),
       (5,    3,         'm',      'Peter Jonson',         5200);

Populating Specific Columns

Sometimes, you just want to populate the minimum number of mandatory fields to create a record. The VALUES syntax is not ideally suited for this purpose because the number and ordering of supplied values must exactly match the number of table columns. A better way is to use the SET statement. It allows you to specify the column names you want to populate for one row. Here is what the INSERT INTO...SET syntax looks like:
INSERT INTO table_name 
SET column_name1 ={expr | DEFAULT}[,
    column_name2 ={expr | DEFAULT},
    ...];
If a table column is not specified in an INSERT, a couple of things can happen, depending on whether or not strict mode is enabled. In strict mode, an error occurs if any column doesn't have a default value. Otherwise, MySQL uses the implicit default value for any column that does not have an explicitly defined default. The value of course is dependent on the data type. Strings default to an empty string (“”) and integers default to zero (0). Enums default to the first item.
Should both the column list and the VALUES list be empty, INSERT creates a row with each column set according to the above rules.
mysql>INSERT INTO employee () VALUES();
 
mysql>SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)
Here is the employee table with the new row. Notice that the numeric shop_id was defaulted to zero. All the other fields were set to NULL.
idshop_idgendernamesalary
11mJon Simpson4500
21fBarbara Breitenmoser(NULL)
32fKirsten Ruegg5600
43mRalph Teller5100
53mPeter Jonson5200
60(NULL)(NULL)(NULL)
The following table definition is similar to the one used in the last article, but with a couple of different column attributes.
  • The shop_id field has been changed from NULL to NOT NULL.
  • The gender field has also been changed from NULL to NOT NULL and includes a default value.
CREATE TABLE "employees" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "shop_id" INT(11) NOT NULL,
  "gender" enum('m','f') DEFAULT 'm' NOT NULL,
  "name" varchar(32) DEFAULT NULL,
  "salary" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
);
Use the INSERT INTO...VALUES example above for multiple rows to populate the table.
Here is an example that sets the mandatory shop_id column, using the INSERT INTO...SET statement.
CREATE TABLE "employees" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "shop_id" INT(11) NOT NULL,
  "gender" enum('m','f') DEFAULT 'm' NOT NULL,
  "name" varchar(32) DEFAULT NULL,
  "salary" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
);
We don’t need to include the row id column because it’s an auto_increment field. The gender will default to its default value of ‘m’, but the remaining two fields will remain NULL as no defaults have been assigned for those.
idshop_idgendernamesalary
11MJon Simpson4500
21FBarbara Breitenmoser(NULL)
32FKirsten Ruegg5600
43MRalph Teller5100
53MPeter Jonson5200
62M(NULL)(NULL)
The following statement would fail in strict mode because the shop_id column was omitted and does not have a default value assigned.
mysql>INSERT INTO 'employees' SET name='Jon Simpson';
 
mysql>SQL Error: Field 'shop_id' doesn't have a default value.

Setting Column Values Using a SELECT Statement

There are many instances where it makes more sense to fetch column data from another source, such as another table. The INSERT...SELECT statement serves that purpose by combining the INSERT INTO...VALUES and SELECT statements. Here’s some SQL that populates the employee table with data from another table.
INSERT INTO 'employees' ('shop_id', 'gender', 'name', 'salary') 
SELECT 3,
       LEFT(gender, 1),
       CONCAT_WS(' ', first_name, last_name),
       salary
FROM   transferred_ employees
WHERE  transfer_date > '2008-01-01';
As is often the case with data transfers, some data massaging must be done to make it conform to the structure of the target table. In this case I used the LEFT() function to retrieve the first letter of the source gender field and CONCAT() to build the name column from the first and last name fields. In fact, all of the INSERT statement variations that we’ve looked at here today accept expressions as well as data literals. The expression can be the result of a function or a literal expression such as “3 * 12 – (6 + 1)”.
If you use an INSERT...VALUES statement with multiple value lists or INSERT ... SELECT, the statement returns an information string in this format:
Records: 3 Duplicates: 0 Warnings: 0

Handling Duplicates

MySQL provides a way to deal with duplicate keys using the ON DUPLICATE KEY UPDATE statement. Whenever a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the existing row is performed.
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
Here’s an example to illustrate.

INSERT INTO mytable (primary_id, count) VALUES(5, 1) 
  ON DUPLICATE KEY UPDATE count = count + 1;
In the above example, if the value 5 doesn't exist in the primary_id column, it will be inserted. However, if a value of 5 already exists, an update will be made to the existing column. Note that, in the latter case, affected rows will return 2, which may not be what you would expect.
You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT...ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts.
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
If a table contains an auto_increment column and INSERT...ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the auto_increment value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by passing an expression to the LAST_INSERT_ID() function. Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful when an update is performed, use the following code.
INSERT INTO table (email) VALUES (email_address) 
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
There are other uses for the INSERT statement that you may be interested in, such as importing data from an XML document. The Working with XML Data in MySQL article contains some helpful information on using the INSERT statement for that purpose.

Subscribe to: Posts (Atom)