In today’s world with so many third-party integrations and content-sharing, it’s important to understand and make use of protocols like SCP and SFTP. PHP’s SSH2 extension, a wrapper for libssh2 which implements the SSH2 protocol, provides several functions you can use to securely transfer files.
To begin leveraging these functions, it’s obvious the SSH2 package needs Read More →

Introduction
In software engineering, a design patterns are general reusable solutions to a software design problems you find again and again in a real life software development. Patterns are not a finished design that can be transformed directly into your code. They are only formal description or template how to solve specific problem. Patterns are best Read More →

MySQL implements UNION, but does not directly implement INTERSECTION or DIFFERENCE.
INTERSECTION is just an INNER JOIN on all columns:
drop table if exists a,b;
create table a(i int,j int);
create table b like a;
insert into a values(1,1),(2,2);
insert into b values(1,1),(3,3);
select * from a join b using(i,j);
+------+------+
| i    |    j |
+------+------+
| 1    |    1  Read More →

I've had a couple of emails recently about the excellent Stanford Machine Learning and AI online classes, so I thought I'd put up the odd post or two on some of the techniques they cover, and what they might look like in PHP.
The second lecture of the ML class jumps into a simple, but often Read More →

We’re told that foreign key constraints are the best way to enforce logical referential integrity (RI) in SQL, preventing rows from becoming orphaned.  But then we learn that the enforcement of foreign keys incurs a significant performance overhead.1,2
MySQL allows us to set FOREIGN_KEY_CHECKS=0 to disable enforcement of RI when the overhead is too high.  But if Read More →


Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 47

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 48

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 47

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 48

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 47

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 48

I have a very simple table with three data columns: Date1, Report_#, Name. I would like to pivot it around Date and the various names as the header tab, and have all the report numbers appear below.

So it would LOOK like this:
Report | Date | Name Read More →

From table tbl( class, member ), you want to cross-tabulate all classes with their members. In SQL terms, you aggregate members over classes. In MySQL:

SELECT class,GROUP_CONCAT(member)
FROM tbl
GROUP BY class;

With that simple query, you're halfway toward cross-tabulation, halfway to implementing a simple CUBE, and halfway to basic entity-attribute-value (EAV) logic. This is easier to see if we have two columns, Read More →

About the Builder
In the Builder Pattern a director and a builder work together to build an object. The director controls the building and specifies what parts and variations will go into an object. The builder knows how to assemble the object given specification.In this example we have a director, HTMLPageDirector, which is given a builder, Read More →

OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor Read More →

There is usually more than one way to write a given query, but not all ways are created equal. Some mathematically equivalent queries can have drastically different performance. This article examines one of the motivations for inventing LEFT OUTER join and including it in the SQL standard: improved performance through exclusion joins.
LEFT OUTER join syntax Read More →

Here’s a way to write join clauses so they are more compact, more readable, and less confusing.
Standard SQL:2003 defines a USING clause that can serve the same function as the ON clause in the familiar JOIN syntax. For example, the following join
select a.col1, b.col2
from a
inner join b on a.col3 = b.col3
May be Read More →

In this article I’ll show several ways to emulate a FULL OUTER join on a RDBMS that doesn’t support it, as is the case with even the most recent versions of MySQL. This useful query is surprisingly tricky to get right.
Introduction
A standard SQL FULL OUTER join is like a LEFT or RIGHT join, except that Read More →

Event delegation is an advanced technique for event-driven programming. The idea: instead of attaching one listener onto each element in a group, attach one listener onto an ancester shared by all the elements in that group. Then, when the listener is triggered, determine whether to act by looking at which element originally received the action.
The Read More →

Introduction to JSON
JSON (JavaScript Object Notation) is a lightweight data-interchange format (if you are new to JSON, you can read more about it on the JSON website). It is notably used by APIs all over the web and is a fast alternative to XML in Ajax requests. Prototype 1.5.1 finally features JSON encoding and parsing Read More →

Prototype enables you to deal with Ajax calls in a very easy and fun way that is also safe (cross-browser). Besides simple requests, this module also deals in a smart way with JavaScript code returned from a server and provides helper classes for polling.
Ajax functionality is contained in the global Ajax object. The transport for Read More →

Prototype's DOM extensions set it apart from other JavaScript libraries. Prototype adds many convenience methods to elements returned by the $() function: for instance, you can write $('comments').addClassName('active').show() to get the element with the ID 'comments', add a class name to it and show it (if it was previously hidden). The 'comments' element didn't have Read More →

In early versions of Prototype, the framework came with basic support for class creation: the Class.create() method. Until now the only feature of classes defined this way was that the constructor called a method called initialize automatically.
Prototype 1.6.0 now features a richer class system that's backward-compatible and adds some new features.
The cornerstone of class creation Read More →

Prototype takes the complexity out of client-side web programming. Built to solve real-world problems, it adds useful extensions to the browser scripting environment and provides elegant APIs around the clumsy interfaces of Ajax and the Document Object Model.
Getting started: Defining classes and inheritance • How Prototype extends the DOM • Introduction to Ajax • Using Read More →

Approximate joins
from the Artful Common Queries page

There are two main ways to reconcile payments against charges:

Open Item: match payments against individual charges, typically by carrying the charge number in the payments table
Statement: list and sum all charges and all payments, and show the difference as the outstanding balance.

The Open Item method needs a foolproof way Read More →

Parents without children
Given tables parent(id INT), child(id INT,parent_id INT), how do we find parents with no children? It's the All X for which there is no Y pattern, which can be written as an exclusion join...

SELECT parent.id
FROM parent
LEFT JOIN child ON parent.id = child.parent_id
WHERE child.parent_id IS NULL;

or with a NOT EXISTS subquery, which is logically equivalent to the exclusion join, but usually performs much slower:

SELECT parent.id AS ParentID
FROM parent
WHERE NOT EXISTS (
SELECT parent.id
FROM parent
JOIN child ON parent.ID = child.parent_id
);

Parties who Read More →