Help me improve my PHP and MySQL skills with useful resources on the web.

September 25, 2006 3:20 PM

Help me improve my PHP and MySQL skills with useful resources on the web.

I've taught myself a fair amount of MySQL and PHP, but now I'm getting more advanced I'm needing guidance beyond my Googling abilities to advance my general knowledge.

I'm looking for forums for finding solutions, or asking questions, tutorials/general resources for problem solving and advancing my knowledge, and advice on best practises (and anything else that may be useful).

For example, for PHP finding solutions to common problems, guides to learning new tricks (for example, I'm trying to get my head round streams), and most importantly improving my programming methods and performance, juggling and manipulating multi-dimensional arrays. For MySQL I'm looking to improve my knowledge of efficient query building, DB optimization and indexing.

Recommendations for excellent books would also be welcome.

All suggestions are much appreciated.

posted by MetaMonkey to computers & internet (10 comments total)

Recommendation #1: forget MySQL and PHP.

No, I'm not suggesting taking up another RDBMS and another 3GL. What I am suggesting is learning standard ANSI SQL and programming principles applicable to all 3GL languages. I can get around in -- and optimize -- MySQL, but I spent only a few hours on the MySQL docs. I only had to spend a few hours because I only had to learn where MySQL differs from standard ANSI SQL and optimization principles that apply to any RDBMS.

Similarly, I can code in PHP (slowly and no doubt badly) because I know C++ and general programming principles. Knowing C++, I got Java certified without ever compiling any Java programs (really). Reading the GOF book and Coplien's Idioms book, even though they are ostensibly about C++ and smalltalk gave me a "tool kit" I can use in any 3GL language. And the GOF patterns will appear again and again, in your own work and also in the standard libraries of your language of choice.

And even though you don't care about C++, read Stroustrup's D&E, because it teaches you the considerations and trade-offs involved in designing a general-purpose language.
posted by orthogonality at 3:31 PM on September 25, 2006

Orthogonality - Could you please use titles for the books, not acronyms? I'd like to read them, but am having a hard time googling the titles...
posted by SpecialK at 3:42 PM on September 25, 2006

"For MySQL I'm looking to improve my knowledge of efficient query building, DB optimization and indexing."

Run explain on your queries, and understand the output. Explain the difference between table scans and index scans. Understand n-tuple indices, and when it's better not to have an index. Understand why and when a join implies a sort. Understand the difference between MyISAM and InnoDB tables. Read up on MySQL internals, and understand what B-trees and R-trees are. Write your own B-Tree classes in PHP, and some Visitors that perform Pre-, Post- and In-Order traversals.

Read up on normal form, Boyce-Codd, and relational algebra. Know what a predicate is, and what a tuple is. Understand why and how M-M relations are implemented, and how an M-M is like two 1-Ms. Understand what null means, and the different meanings of null, and how the SQL standard fails to distinguish them.
posted by orthogonality at 3:44 PM on September 25, 2006

I think orthogonality's probably talking sense, but I thought I'd add a glossary for his comment above (apologies if you know all this already; future Askers may not):

3GL: 3rd-generation language. Basically any language that a human can understand, as opposed to a bunch of 1 and 0's or processor instructions.

RDBMS: Relational database management system. Does exactly what it says on the tin.

GOF: The "Gang of Four" were authors of a book about design patterns - a bunch of abstract, reusable solutions to common problems in architecture/programming. Book

Stroustroup: Invented C++. Wrote D&E, the Design and Evolution of C++.
posted by matthewr at 3:48 PM on September 25, 2006

"PHP 5 in Practice" or the "PHP Cookbook" are both cookbooks of PHP code - have a problem, look it up, find a solution. Reading through them is a good way to see snippets of efficient code written by competent programmers that address commonly-seen problems.

There's also a MySQL cookbook. But I don't know if that would help you as much as just reading a bit about database normalization. Once you "get it", you get it forever, at least as far as SQL and relational databases go.

Free bonus: Lerdorf discusses neat tricks you can do with PHP 5.
posted by jellicle at 3:48 PM on September 25, 2006

SpecialK writes "Orthogonality - Could you please use titles for the books, not acronyms? I'd like to read them, but am having a hard time googling the titles..."


I googled on each title before I posted, to ensure they could be googled. ;)

GOF book: Design Patterns: Elements of Reusable Object-Oriented Software, Erich Gamma, Richard Helm, Ralph Johnson, John M. Vlissides. Published by Addison Wesley

Coplien's Idioms: Advanced C++ Programming Styles and Idioms, James O. Coplien

Stroustrup's D&E: The Design and Evolution of C++, eritten by Bjarne Stroustrup, the designer of C++.
posted by orthogonality at 3:48 PM on September 25, 2006

Commenting on the previous comments:

--The GOF book is vaguely useful, inasmuch as PHP 5 is now fully object-oriented. However, there are other books, such as "PHP 5 Objects, Patterns and Practice", which will cover OO patterns with respect to PHP.

--Coplien's Idioms - I don't see how this applies to the submitter's question. The idioms of C++ are not, in fact, the idioms of PHP. It's as if you replied to the submitter's question about his cat by saying, "Get a dog - and here's a book about dog grooming once you've got the dog."

--Stroustrup's D&E - again, while this book is certainly interesting on its own, it has almost exactly nothing to do with PHP or THE SUBMITTER'S QUESTION.
posted by jellicle at 4:04 PM on September 25, 2006

jellicle writes "Coplien's Idioms - I don't see how this applies to the submitter's question. The idioms of C++ are not, in fact, the idioms of PHP. "


Understand that Coplien's Handle-Body "Idiom" is applicable to any object oriented language, and in fact it's pretty much the GOF Bridge Pattern. More important, as Coplien builds it up chapter by chapter, he introduces a lot of OO principles.

Patterns are applicable to any OO language, and the general principles (motivations and consequences, in GOF speak) in any language; I've made good use of Decorator and Chain of Responsibility in SQL. (Indeed, the whole point of a SQL view is to use it as a Decorator or a Facade.)

"Stroustrup's D&E - again, while this book is certainly interesting on its own, it has almost exactly nothing to do with PHP or THE SUBMITTER'S QUESTION."

D&E explains the trade-offs involved in building any large-scale system, and the design compromises attendant to any language ((including PHP). Yes, it's about C++, but any OO language user will learn a lot from it.
posted by orthogonality at 4:22 PM on September 25, 2006

W3Schools has tutorials on PHP and SQL (and tons of other tutorials).

SitePoint has lots of PHP/MySQL tutorials, including a recent series (parts two, three, and four are especially helpful).

The O'Reilly Networks PHP DevCenter has some good articles; they also have MySQL articles.
posted by kirkaracha at 4:28 PM on September 25, 2006

Many thanks to all for the useful answers. I think I'll take the advice and lay out for some books, to thoroughly understand what I'm doing. Though I would still appreciate suggestions for the best PHP and SQL forums, and any other useful links about improving programming proficiency.
posted by MetaMonkey at 3:50 PM on September 26, 2006