PHP: require and include

Another simple feature that’s good to pick up on!

When you want to run the same functions from several pages, but don’t want to copy/paste (because that’s BAD), you do this.

Usually, you’d use require or include to include the code from one PHP script in another.

If you hadn’t done this before, or designed it in, you’ll probably need to extract the functions from your script(s), place them in a central file, and include that in all scripts that need it (including, of course, the one they were originally in!). Remember that includeing a file in another causes its embedded HTML to be included too, so you’ll typically include PHP files that have functions only, to achieve the desired result.

Then you just call the function, as if it were written in the script in the first place 🙂

SQL Server: Database Diagrams

A little feature that some people don’t know about but would find very very useful: database diagrams in SQL Server. If a picture paints a thousand words, then this feature will help you pick up on the structure of data much more easily.

Within SQL Server, there’s the “Database Diagram” container in each database, in Management Studio.

You can create a new diagram in there, and include all (or some) of your tables, so showing where the relationships are, and what the tables are.

You could also create several different diagrams, each showing a subset of tables and relationships useful to a particular use case.

Quality of Service: Hosting

There are several useful QoS parameters, although not all are as easily measured as e.g. 99.98% uptime, which is easily derived – 99.98% of the time, your server is available.

Some other useful metrics:

What sort of bandwidth do they support? How about if it’s a shared pipe and they’ve multiple high-volume clients? You’re unlikely to get an honest answer to this from the vendor.
If you’re on a shared host, what sort of competition for resources will you have on the box? Again, this is competitive information, so you’re unlikely to get an honest response.
How independent is each application? i.e. if another application on a co-hosted box loops MySQL, will your app suffer?
How often do they upgrade dependencies, and does that affect their 99.98% uptime?

Then there are the fluffy not-entirely-QoS related hosting problems:

Will they do your backups etc.?
How responsive are they to queries? Do they have a guaranteed response time e.g. 3 hours from first email to first response, 24/7?
Are they efficient? Have they resolved issues within a useful timeframe? A quick response-time is nice and feelgood, but if they don’t fix the problem, that’s not quite so good!
Are they patient with you when you suggest issues, but due to your own technical background are slightly off-base?

Of course you could always do your own backups, with external drives like this, or caddies (2.5″, 3.5″) for old spare hard drives you have lying around, but that doesn’t substitute for the technical support you get from a good host.

GROUP BY and single-row values

So, you’ve constructed a SQL statement with a GROUP BY clause, and you’re getting this message:

Column col is invalid in the select list because it is not
contained in either an aggregate function or the
GROUP BY clause.

Bear in mind that the result of a GROUP BY statement, or of a statement where one or columns uses an aggregate function, has each row containing a summary of other rows.

This means that if you try to include a column in your select clause that isn’t a summary (this includes values by which you’re grouping), then the server is going to have difficulties returning it; remember, it’s going to return one row per group, and any value that can’t be reduced to a single row per group will fail.

This gets interesting even when you’re hoping to return a single value per group, for example with CASE.

If the CASE expression you’re using is dependent on values of individual rows rather than summaries, you’ll also get the above message; you may only reference non-aggregate values (i.e. single-row values) in an aggregate function, or in the WHERE clause, so the solution would involve placing your CASE inside an aggregate function, in this case your SUM.

The same applies for the HAVING clause, as it is effectively selecting rows in the grouped resultset based on values in each group.

Returning a copy of an object

Consider an object passed (by reference) into a method, which then modifies the object. Should the method return the object on completion?

Well, it’s redundant. Here’s why.

There are two approaches to doing this – the classic way (caller chooses what to manipulate/change), and callee changes (which is what happens when you pass in an object by reference:

If the caller chooses what to manipulate, then your method will operate on a copy of the dataset, and return that copy, leaving the original untouched. Then the caller may overwrite the original, if desired. Many array operations in Perl work like this.
The alternative lets the called method do the in-place operation, in which case the return value is more typically a boolean (“Yes, I’ve succeeded!”) or an int containing the number of values affected. This is how database updates work.

Of course, if the object is not passed by reference (i.e. a copy is passed into the method) or if the method explicitly returns a modified copy, then returning an object is the right way to do it.