Large custom survey / reporting applications - best practice

Question

The situation

When you study social sciences, you are frequently confronted with the need for online surveys (scientific data collection online). That's one of the main reasons why I started with PHP in the first place.

Recently these survey applications have grown and grown. A lot of complexity has been added: reporting (flash charts, PDF generation), data aggregation, different levels of aggregation (e.g. company units), questionnaire module selection for the companies involved, etc.

Now I see myself confronted with a complex data gathering and reporting application which is getting slow and unmaintainable (running on the basis of Joomla). In the meantime I'm not studying anymore but still developing online research applications.

One of my platforms has up to 100 new users per day and aggregates / reports data of several thousend users times hundreds of data items times dozens of hierarchy levels.

My questions

• is PHP/mysql still a way to go?
• could a framework like codeIgnitor be a basis for such an application or should I develop everything from the scratch?
• do you know any kind of survey specific framework I could use as a basis?

Answer 1

Have you looked at tools like SurveyMonkey, or are they not suitable for your needs?

Answer 2

PHP/mysql should be fine for this scale, but you must tune it and give it sufficient resources.

For example, if your schema is not thought out, you'll hit all sorts of performance walls. How your data is stored and indexed is probably the most important factor for the performance of reports. I've had 60-100gb of data in mysql with sub-second response times for mildly complex queries. The important factors were:

• my data was indexed for the ways I used it
• the queries I used were thought out, tested and optimized

Next up, you must give your MySQL server sufficient resources. If you're running your app on a shared hosted server and you're using the out of the box settings, mysql probably won't run well with more than a few hundred mb's of data. Make sure your caches are tuned, your table types make sense for your application and you have enough memory and fast enough disks to meet your performance needs.

And finally, there's a trick we all use when our data sets get big: generate your reports on a cron instead of on demand. If it takes 2 minutes to generate a flash graph, have a cron run every 5 minutes to generate the data. Stick it in a file somewhere and spit that out to the graphing software instead of querying the database in real time.

Answer 3

is PHP/mysql still a way to go?

Yes, PHP/MYSQL (LAMP) has been successfully used in hundreds of sites with exponentially larger user bases than yours.

could a framework like codeIgnitor be a basis for such an application or should I develop everything from the scratch?

Nontrivial PHP sites built without a framework (or a custom built framework) tends to get sloppy fast. PHP frameworks are now the norm for getting started, I would personally recommend the Zend Framework. It's a very robust framework, providing tools for many common PHP tasks (replacement/enhancements for Database, Date, JSON, RPC, REST) and an organized methodology for web application development: MVC using Zend_Controller.

do you know any kind of survey specific framework I could use as a basis?

None that I know of, but you may want to try using Zend_Form to automatically generate form elements (type, filters, sanitizers, validators) from configuration files.