PHP, MySQL, PostgreSQL, etc.

Well, I’m currently writing a PHP and SQL application at school, so here are some of my thoughts on the problems I encounter.

PHP is crap

Many PHP applications are low quality applications. The problem is that PHP makes it hard for developers to write good applications. PHP developers tend to use many global variables, global code, and not many classes or functions. And when writing applications for the web, PHP makes it easy to mix PHP and HTML, in my opinion even too easy.

Well-written applications follow some basic rules. First, separate your code into functions and classes, and (2) split your code over multiple files. And there is one rule which is especially important for non-commandline applications: (3) seperate logic and presentation. This means if you write a web application, use a template engine.

PHP error and exception handling is also an interesting topic. The problem here lies in the distinction between the both. In my opinion, there should only be one way to handle such stuff: Using exceptions. Exceptions are a very widespread way to program today, and are used in languages like Java and Python for cases where PHP uses errors. The concept of PHP’s errors results in code like @do_something or die("blah"); being written in libraries which is a problem as it clearly violates rule (3) I wrote above, because there is no way to catch this die() error.

Furthemore, PHP has no clear concept of namespaces in the current stable versions (< 5.3). PHP 5.3 finally gets namespaces, but using a backslash character, which looks really bad and makes everyone think of eithers Windows systems or escape characters.

There are even more problems with PHP. From time to time, PHP just crashes with a segmentation fault.

MySQL vs. PostgreSQL

MySQL seems to have many problems with checking data, which can confuse me. It got better in MySQL 5 with the introduction of the new sql_modes like STRICT_ALL_TABLES and TRADITIONAL. And using storage engines like InnoDB also allows you to take advantage of stuff like foreign keys and transactions. But MySQL is still problematic.

For example, in an application I wrote I had multiple entries identified by a numeric id (an INTEGER). One day, I wanted to check whether my error handling was OK and requested an entry with the invalid ID ‘1X’, which resulted in SELECT * from A where id='1X';. Now I expected to get an error or at least no result, but MySQL happily proceeded with the query and returned the entry with the ID 1. I tried the same thing on PostgreSQL and got an error message that ‘1X’ is not integer, as I expected it.

As another example, when I run the same PHP application using PDO on a MySQL database and a PostgreSQL database, I always get strings from the MySQL database whereas I get integers from PostgreSQL if the columns are integers.

Another thing which PostgreSQL does better than MySQL is password encryption. MySQL only provides access to the standard crypt() function and by default only generates DES passwords. It allows other methods to be used (although not all are available everywhere and therefore not documented), but it provides no way to create a salt for them, which makes them not very useful. PostgreSQL in contrast provides less encryption methods but it provides the gen_salt() function which can be used to generate salts for blowfish encryption (gen_salt('bf')).

Another problem is that MySQL simply ignores references when they are added to the column definition instead of a separate ‘FOREIGN KEY’. Because MySQL gives no errors when a feature like REFERENCES is not supported, developers may think that the feature is supported and write code using this feature just to realize later that what they wrote does not what they want.

And you can’t write AGPL applications using MySQL client libraries, because MySQL is GPL-2 which is incompatible with the AGPL. You might think that the “FOSS License Exception” helps here, but this only lists (among others) the GPL-3. Or am I wrong?

But PostgreSQL has other problems, at least when it comes to the performance of the information_schema.columns view. This may be related to the fact that PostgreSQL has > 1000 rows whereas MySQL only has 250. And PostgreSQL may be a bit harder to configure.

Final words

All in all, PHP and MySQL are not the best choices for writing applications. They are to tolerant, and do not treat errors in an acceptable way — by aborting the current action. I can get around some problems in PHP by using error handlers which raise exceptions and setting error_reporting to E_ALL, but in fact this should be the default. MySQL problems can be solved to a large extend by setting sql_mode to TRADTIONAL and using InnoDB databases, which should in my opinion be the default and not an option.

My recommendation is to use PostgreSQL servers and for the applications: use frameworks (if you are allowed to, which may not be always the case [e.g. when you are learning SQL, using a ORM is clearly the wrong way]), document your code, separate your code into classes and functions, and make sure you are not using to many global variables. And use a SCM to track your changes.


23 thoughts on “PHP, MySQL, PostgreSQL, etc.

  1. Man, the “PHP is crap” heading was perfectly placed. I fired up the planet, and boom there it was. I got a really good chuckle out of it. I wouldn’t personall call PHP crap, but it is close. It has it uses, though I haven’t figured them all out, plus I have never really taken the time to properly learn PHP.

    Another thing that PostgeSQL does better is the GEO stuff, especially with Django and the like. I have also noticed that it is relatively lighter on the resources as I actually run it on my laptop constantly when working on code that utilizes it.

  2. i don’t agree. developers make mess, not languages.
    php is a web-oriented language, of course it has different structure from java or python, but people is working in the right direction (namespaces, classes, etc…). not everyone is able to write his own framework from scratch: php was a procedurallanguage now it’s quite “object oriented”. a good framework (e.g. zend, cakephp or codeigniter) can help people to write less code separating it from presentation. but it’s always developers fault, not the language one!
    i studied quite deep into wordpress, phpbb and mediawiki code: they are a real-enormous-mess. not a single class (except mediawiki, it has only classes), queries inside presentation 😦 but they are stable. even with crapy code these application rule. yeah, authors could write these apps in a cleaner way using patterns and a lot of classes, but the choose that way.
    which is my final thought?

    1. developers make mess, not php (remember, it’s web-oriented language, born as procedural, growing as object oriented!!!)
    2. even with messy code php application stands
    3. obviously using frameworks could help developers working better and more efficiently.

    about databases i shut up, i know only mysql, can’t compare it with postgresql.


    1. I also wrote a lot of PHP applications, they ran my website in 2005. This blog is run by WordPress, which is a PHP application as well.

      And you are right that developers make the mess but there a languages that make this easy and languages making it harder.

      But anyway, I do not like PHP (some might say that I hate PHP). I don’t like WordPress either, but I use it because I have no server where I can run something which suits me better.

      1. i began using php in far-away 2004 of course with procedural and ugly-unreusable style. i think his “weak” variable type dependence is its strength. “hello word” is

        echo ‘hello world’;

        not (fake code style)

        include textClass
        var hello=new String(‘hello world’);
        print hello;

        of course it could be even his weakness, but don’t forget we are talking about web oriented language, it’s essentially text!!! i think it’s hard to compare java (or python) strength with php simplicity and flexibility (also including classes or namespaces). i agree with “some dude” comment: php is (intended to be) flexible but it depends on what kind of developers we are.

    2. I totally agree with davide. PHP is intended to be flexible. Developers tends to abuse of this. Period.

      1. But “PHP is crap” just sound better than “PHP allows you to write crap”. Of course, there is the possibility to write good applications with PHP but there is a tendency to write bad ones.

  3. I guess it’s an open secret that the MyISAM table is just something like a binary CSV file with a lot of overhead. Maybe they’ll push InnoDB now that everything is under one roof. Donno what the state of the Falcon project is but now that the main people pushing that project left Mysql/SUN I’d bet on the return of InnoDB.

    1. maybe you’re right from a certain point of view. but which template engine can have multiple database access? ldap? filesystem access? ftp? template engine (“weakness” following your thought) and filesystem/network/database access isn’t enough to build websites or web applications?

  4. Full ack. I ran into many of these problems, you listed.

    After 3 years of web developing with PHP, I finally switched to Ruby on Rails and am a little bit more happy now. There are problems as well, but it’s better structured.

    PostgreSQL really lacks hobby-developer-easyness, MySQL offers. If more common web-apps (WordPress, etc…) would rely on postgres, this issue will be fixed.


  5. I agree that PHP and MySQL are not perfect but with decent framework like Code Igniter and free tools that MySQL offers (MySQL Workbench, MySQL Query Browser, MySQL Administrator) I think you can create really decent web applications. It’s not without the reason that they became so popular.

  6. Julian, with the greatest of respect you need to actually read the documentation and understand what you are doing before writing stuff like this. I agree on one point – I’ve seen heaps of crap applications written in PHP (wordpress is one) but that’s because the developers make the crap applications, not the language itself. A lot of developers started with PHP 4 or 3 which didn’t have a good OOP model.

    Lets take a look:
    “Many PHP applications are low quality applications” – this dogs smells therefore most dogs smell… interesting logic.

    “The problem is that PHP makes it hard for developers to write good applications” – no it doesn’t. Read the docs.

    “PHP developers tend to use many global variables, global code, and not many classes or functions” – no, PHP developers ‘who don’t know what they are doing’ tend to use…

    “PHP makes it easy to mix PHP and HTML” – ummm yeah, because it’s a web oriented language. But then again, you can write shell scripts in PHP sending the write content type header and not touch any HTML at all.

    “The concept of PHP’s errors results in code like @do_something or die(“blah”); being written in libraries which is a problem as it clearly violates rule (3) I wrote above, because there is no way to catch this die() error” – If you are using @, which is the error suppression operator then you are turning the error reporting level down every time you use it. die() is the equiv of exit() (see which terminates script execution. It’s like saying “I called exit(), and it terminated my script – WTF???”

    Do you really think that calling exit() is a problem with the language or an issue with the developer not understanding how to handle errors and providing a better UI for their website users?
    Look up try and catch on the PHP docs and revisit your opinion.

    “Furthemore, PHP has no clear concept of namespaces in the current stable versions” – yes that’s because it’s introduced in PHP 5.3. That’s a bit like saying XYZ2.9 doesn’t have “FooBar” (which is in XYZ3 BTW) therefore it’s crap.

    “There are even more problems with PHP. From time to time, PHP just crashes with a segmentation fault” – well, write a descriptive bug report. Is the issue in the web server you are using?

    “Use frameworks” – ummm no – use frameworks (even those written in PHP!) when they are relevant to your use case. Don’t use one for the sake of using one.

    Everything you write in your final summary can actually be done with PHP5

    So, I suggest re-read the docs, look up some information on writing apps in PHP5, research your opinion further and try again.

    “After 3 years of web developing with PHP, I finally switched to Ruby on Rails and am a little bit more happy now. There are problems as well, but it’s better structured.”

    Yes, because PHP is a programming language. Rails is a framework written in a programming language. You are comparing apples and oranges. If you want to compare Rails with anything in PHP then you need to compare it with a PHP framework.
    Try comparing PHP with Ruby then you are on the right track.

    1. 1. I wrote that PHP 5.3 includes namespaces.
      2. The segmentation fault occurs in the command line version, and there already is a bug report in the Debian BTS.
      3. All I wrote was related to large applications, especially the recommendation to use frameworks.
      4. Yes, writing good applications in PHP is possible, I try to it. But it’s a bit harder to convince yourself to write good applications, when you can write applications simply printing stuff or mixing PHP&HTML very easy (which is good for small applications)

      There are things I like about PHP, like JSON support, PDO, and that it’s easy to write small sites. But PHP 5.2 has its limits. PHP 5.3 seems to solve many of them (e.g. static:: and namespaces), but it’s not finished yet.

  7. Yep, I totaly agree the php is crap. it is popular because it is easy to learn and start program in it. I do use it since PHP3 time and never liked it. I think it is just popular for all the wrong reason. !

  8. This is a truely ignorant point of view. You can’t blame a developer’s (willful?) ignorance of well establisted programming paradigms (separation of logic and presentation etc.) on the programming language they use — This is akin to blaming a database system for allowing a user to store unnormalised data, I don’t know any database that prevents people from doing this.

    Errors and exceptions have 2 separate uses and it is often that exceptions are the ones that are misused. An exception should be used for something that you have no control over: the database connection dies in the middle of a transfer, the filesystem becomes unmounted. Errors are used correctly in PHP where it is the application’s responsibility to avoid errors i.e. passing in an empty array to a function when it should contain elements. It is the developer’s responsibility to do validation of data. So again, ignorance of the developer is at fault here, not the language.

    PHP/Python/Java/… isn’t crap, lazy developers are crap. If you need a language so restrictive to prevent yourself from writing crappy code then you shouldn’t be writing code at all. Languages require flexibility to make themselves useful — and yes they need to be flexible enough to all you to write useless code. I’ll leave you with a well known quote:

    “C gives you enough rope to hang yourself”

    I don’t know anyone who would deny C’s usefulness.

    1. I know that Python is no crap, because I love Python. I know that Java is no crap, because I write Java programs at school.

      The problem with PHP lies in fact in the combination of a non-statically typed language and no tools to check the code without executing them.

      My experience in Python programming is that it helps you a lot to have tools like pylint,pychecker and pyflakes. I don’t know of any similar tool for PHP.

      You said that exceptions should only be used for unexpected errors, which means applications should validate the data before they pass it. While this is a common way to handle problems, there are other ways to do it. For example, in Python exceptions are used for stuff like when a list is empty (ValueError).

      The problem with validating the data before you pass it to the function is that you have a duplication of code, and it takes time which could be used to just try the action.

      For me the only reason to validate data before I pass it to a function is security, e.g. validating the names of tables and columns before I pass a query to the SQL server. I don’t check the values inserted, I just quote them correctly and let the server handle the problems.

      I’m not blaming the PHP language as much as I am blaming the (parts of the) PHP community creating bad code.

      But I think it’s clear that there are many things that just seem wrong to me compared to the Python way of programming (i.e. it’s “easier to ask forgiveness than permission”).

      1. I have to agree with you; there are a mountains of awful PHP code out there. PHP has a very low barrier to entry and as a result there is a lot of amateurish code. But professionals can use PHP to produce some excellent products. I would recommend reading “Advanced PHP Programming” by George Schlossnagle to see some examples of beautiful PHP code.

        PHP was born in the then brand new public internet age, and it certainly picked up some bad habits. But it has grown up into a mature, full-featured language. I have my gripes with it aswell (the non-standard function naming being number 1) but I still believe it is the best-suited language for web development because that is what it was designed for.

  9. Dear, Julian Andres Klode

    You are totally right PHP is CRAP.

    Therefore webapps like FACEBOOK are CRAP and are crashing every time!!!

  10. With respect Julian I believe you miss the point.

    Flexible loosely-typed languages do not write bad code – bad coders do! I do agree error_reporting(E_ALL) is a must to avoid chasing variable typos for hours.

    Its a bit like saying Porsches should be banned because they allow people to drive too fast!

    Everyone has a choice, and the fact that PHP offers more choices than most languages is a great strength.

    I don’t dispute that this choice also allows people to write unsecure unmaintanable and generally crap code, but caveat emptor!

    One genuine annoyance is the mixture of legacy straight and under_score function naming – for example:

    html_entity_decode() vs.


    str_split() vs.

    ..but every language has its inconsistencies.


  11. Am I right in that the rise of MySQL was because cheap web hosts were charging “per database” for them, while Postgresql wasn’t so easy to limit at the time?

  12. Although PHP is clearly not a beautiful language, it just suits perfectly well for web development! This being said, I’ve seen horrible things written in PHP… But, if you know how to code, PHP is production ready, fast and benefits from a wonderful documentation.

    Concerning MySQL vs PostgreSQL however, I totally agree with you. I find MyISAM table lack of support for foreign keys just astounding… MySQL support for fulltext search is also largely perfectible.

    I wrote some blog posts about it a while ago here and here.

  13. “…I know that Python is no crap, because I love Python. I know that Java is no crap, because I write Java programs at school…”
    Enough, that`s ridiculous argumentation. One concept is only good if you know/learned it?
    Stop PHP-bashing, write a better blog software in your beloved Ruby or Java and stoip using WordPress immediatley, cause it is written in the worst language in the world.
    Go to school and if you mature enough to understand business cases than talk again.

Comments are closed.