Fog Creek Software
Discussion Board




PHP + MySql

It looks to me like PHP + MySql is the best way currently to develop moderately robust web apps on a budget. Low cost, good documentation, widely supported by hosting providers, widely used relational database, decent tools, etc. thots?

pb
Friday, May 10, 2002

Having done a site in PHP/MySQL that's still around 2 years later, my thoughts are:

1) MySQL is simple and fast, great for database-driven sites that are primarily read-only. However, you'll start to miss the kinds of things you can do with "real" database abilities (like subselects and transactions). I recently started using Postgresql and am impressed.

I also had some data corruption issues with mysql. Turned out to be an issue with linux 2.2.14 and mysql, but it involved rebuilding the db once a week (and the db was 5-6gb) which wasn't fun.

2) PHP was simple. However, it was real hard to make a well-designed site with in (meaning good abstractions) because of the nature of the language itself -- embedding script on pages. It also, at the time, was not cached, which meant that the more complex your app was in lines of code, the longer each page request would take.

So, if it's a simple project, PHP/MySQL is a good way to go. I am far happier, however, having moved to a Java approach using taglibs -- the maintainability is so much higher.

Anyway, my 2E-2 c

Matthew Christensen
Friday, May 10, 2002

At the risk of starting a religious war Perl plus MYSQL would be better. ;)

The amount of Perl modules available at CPAN (http://www.cpan.org) make it more productive I think that PHP.

Check out the modules available http://search.cpan.org/ Saves you from reinventing the wheel all the time.

Matthew Lock
Friday, May 10, 2002

I agree with either PHP or JSP/servlets as a free way to do websites. Perl is great for programming (parsing , quickies) ; it is a pain to do CGIs . Modperl is great though (fast, easy)

jeb
Friday, May 10, 2002

Let me be the one to suggest Python + MySQL as an alternative too (though I'm specifically not talking about ZOPE, as I haven't looked into it much.)

I've done some multi-programmer web work in Python and I've found that the language very much encourages code readability. I can look into some code another person has written for the side and understand it much sooner than it would take for code written in, say, VBScript/JScript for ASP (from experience) or Perl (from looking at various similiar Perl code.)

There is no standard Python text template module, but they aren't too hard to do depending on your needs. I did a quick and dirty one to separate out code and HTML for a project. I wouldn't trust it for *commercial* production servers, but it works well enough for my hobby sites.

Adam Vandenberg
Saturday, May 11, 2002

How much does not having transactions affect database programming?  I'm just getting started playing around with MySql (downloaded it this afternoon before I even saw this timely thread), and I was interested to notice that mySql does not support transactions.  Are they ever really necessary, or even useful -- do they justify the performance hit mySql claims they impose?

Alyosha`
Saturday, May 11, 2002

Alyosha,
Transactions: use them whenever you have a series of distinct actions (UPDATEs DELETEs INSERTs) - but you want either: all of them happen, or none at all if anything goes wrong.
Philip Greenspun's book, specifically chapter 12 explains their importance nicely: http://www.arsdigita.com/books/panda/databases-choosing.html

Duncan Smart
Saturday, May 11, 2002

News update: As of more than a year now, MySQL does support transactions and full ACID semantics. You have to create Inno tables rather than MyISAM tables to have this ability, but it's there, it's stable, and the performance hit is surprisingly low; You can freely mix all kinds of tables in the same database and in the same query, include "heap" tables (memory based tables), which means that, with properly selected table types, no other database can probably offer the same speed.

MySQL does have it's limitations, though - no subselects (I never found them too useful, but some people swear by them so I suppose there are occasions in which they're essential); No 'stored procedures' (which also means no triggers, etc) which sometimes simplify system design immensely. No referential integrity, which is extremely useful for debugging and - although entails a tremendous performance price, and though turned off in many production systems - provides a good safeguard for systems that must maintain integrity at all costs.

My advice: If your system involves financial transactions or otherwise mission critical data, stay away from MySQL - it does the job well, but it provides too little automated safeguards in case YOU don't do YOUR job well; PgSQL, Oracle and MSSQL all do better in this respect (and worse in others).

Ori Berger
Saturday, May 11, 2002

I think that PHPNuke and PostNuke prove the worth of PHP and MysQL (or rarther ADODB - a database abstraction)

http://www.phpnuke.org

http://www.postnuke.com

Once you have these tools, you can probably equal most of Yahoo's functanility with very little effort.

Other combinations may have more benefit, but what is the associated price tag????? 

Ged Byrne
Saturday, May 11, 2002

I've heard good things about Python but I don't believe it is offered very widely by hosting services which I think makes it not as attractive as PHP or PERL.

PERL is obviously a very good environment to consider but I've found PHP-developed solutions to be more impressive (phpbb, phplib, phpshop, etc.). I don't think this is necessarily because PHP is "better".  I think it's more beacuse there are fewer PHP solutions in each category and the good ones are really good. And that there's a certain amount of "quick and dirty"-ness in the PERL world.

I think it is perfectly fine that PHP + MySQL may not be right for "mission critical" stuff since that is such a small percentage of what's being developed.

I think it is important to go ahead and pick a database (MySQL) and that the ADO stuff isn't that important and may actually detract because it adds complexity. Again, you're going to find much more hosting support for MySQL.

For some reason, I just can't get into Java servlets. Too scary. On the Microsoft side of things, there's not as clear a choice on either the scripting language (VB or C#) or the DB (SQL Server or Access). And the cross platform support is mor significant in web dev since the platform break-down is much different from the 90/6/4 (Win/Mac/UX) seen on the deskptop.

pb
Saturday, May 11, 2002

The moral of the story here is that you use the correct tool for the right job. For dishing out data to a web site, MySql is ideal.

However, with no Referential Integrity (RI), then it is not a viable platform for software development. If you are writing a data centric application, then MySql is not ready for prime time. . I mean, with no cascade deletes, then how can you possibly delete a invoice?

My recent application is a small application and now has about 50 tables. Those tables are related to each other like a big spider web. As I designed this application, I have added many tables, and the vast majority of them are related to each other. Some relations go several tables deep. That means that deletion of one parent record can cause a good many records SEVERAL tables deep to be also deleted. With out RI, then you have to write code to do this! Man, that is what we had to do way back in the old FoxPro/dbaseII days. Thus, there is WAY TOO much extra code required when using a database with out basic database features. MySql is missing these features.

Any software developer will find that over time that a good application design requires a LOT of data normalizing. A good normalized application is the only way to provide a rich feature set, and have any kind of flexibility for the end user. Strong ability for data normalizing is probably the #1 thing I look for in a developer! Ask ANY developer what they prefer:

1) Good code, and a crappy data design
2) Ugly code, and a good data design

9 out of ten developers will choose #2. With a good data design, an application practically writes it self. Ask any developer what they need to start work on a application. They will simply say show me the data. The understanding of the data structures is absolutely critical before a developer can work on someone else’s code.

If you are good at data normalizing, then this type of design approach is standard fair for any type of application. However, without RI, you now have to write code to delete the child records. This is too much work. What becomes even worse if another developer must now maintain the application. How the heck is that developer going to know what child records are to be deleted in a complex application? With RI, you don’t have know, or even care. You use one line code to delete the record, and you are done!

Joel used the JET engine from Microsoft to create CityDesk. This basic engine has RI built in. Not only that, the little engine also has replication built in.  Joel has mentioned he has some neat ideas that can be accomplished using replication (such as updating web sites between pc’s).

Other commercial products like Simply Accounting also use the ms-access/JET data engine format.

If a basic little desktop engine has all these great features, it shows that MySql is not ready yet.

At the end of the day you better develop with a good database engine. At the end of the day, it is all about turning code into dollars. Without a good database, I can’t deliver anything of decent quality to my clients.


Albert D. kallal
Edmonton, Alberta Canada
kallal@msn.com

Albert D. Kallal
Sunday, May 12, 2002

on the "abstraction" issue with PHP-it's not totally natural, but you can develop object-oriented sites on PHP.  just use the "include" function and create your methods as functions in the include file.

razib
Sunday, May 12, 2002

How effectively does InnoDB address the transaction issue?

pb
Sunday, May 12, 2002

Yet another news update:

" MySQL/InnoDB-3.23.50 is released. This version features an auto-extending data file, better support for InnoDB Hot Backup, and better support of foreign key constraints, including ON DELETE CASCADE and ON DELETE SET NULL clauses. " [1]

The innobase tables look promising, however I've not used it for serious applications yet.

[1] http://www.innodb.com/

Henning Spjelkavik
Sunday, May 12, 2002

PostgreSQL Goo.  We use it here and have really come to depend on it.  It is a full scale database.  Yes, it has it's problems, like eveything else.

If you are going 3 tier, go with J2EE.  Tomcat + JBoss is a powerful combination.

I can't stand HTML: scripting languges.  But thne I hate UI design in general.

Adam
Monday, May 13, 2002

I'm suprised so many people are recommending Java for a
"cheap" solution.  I myself have never coded a web application in java, but I've done b2b transactions with sites that were using java, and it is PAINFULLY slow.  From what I've heard, java is only a viable solution if a company has plenty of cash to spend on hardware.   

    Language of choice for me? definatly mod Perl.  Fast, easy to code small stuff, powerful enough to do big stuff.     
    MySql is a pretty decent DB, but it really doesn't even compare to the big commercial guys (DB2, MS SQL, Oracle).  It IS fast with simple selects, but once you get going with lots of grouping and joins and whatnot, it can definatly become a headache. 

Vincent Marquez
Monday, May 13, 2002

Just because you've used slow java sites, doesn't mean Java is "slow."

At my last job I wrote a web application in mod_perl. Worked great. But we started having performance problems. Migrated it to java, and it ran faster on the same hardware. Java and the servlet framework allowed us to do a bunch of sophisticated caching that was hard to do in mod_perl.

The biggest problem with java is that it can be a memory hog. Mod_perl was a memory hog too; anyway, memory's not that expensive these days.

But YMMV.

Matt Christensen
Tuesday, May 14, 2002

I have used both PHP and Perl for developing web-apps.

Over the time I realised that PHP code is more readable then Perl one.

I love PHP for its readability of code. Its similarity with C.
It's support for various platforms + web servers. Good support for various DBs and other tools like image manipulation/ PDF file creation.

But there are some areas where you simply can't beat Perl. Just recently I had to make inteface to cable modem/cnr/cmts which is accessible through Web. You have no option other than to use Perl. Huge amount of reusable code for Perl. Really superb !!

Conclusion : Pick up the right tool for your problem.
JD

JD
Saturday, May 18, 2002

Just to echo previous posts - although MySQL is getting better, PostgreSQL is supposed to be more ACID-compliant and has more features. There's no real downside to using Postgres as far as I know.

MySQL seems to be vastly more popular for some reason, I'm not sure why. I guess that's "mindshare" for ya.

BH
Sunday, May 19, 2002

Postgresql is going to be a non-starter for most people beacuse virtually no hosting providers offer it.

pb
Sunday, May 19, 2002

*  Recent Topics

*  Fog Creek Home