building database with automatic report sending
 

Hi all,

I have started working for a small training provider. Apart from my tutor responsibilities, I help with the IT site. I was asked to look into a possibility of creating some database system. I know that what I'm going to describe can be done in PHP but my php skills are not that great (well, I know the basics only, LOL) so my question is whether you know of any product/ that would do the following:

1. Some tutors/assessors will input some student details on one of the subpages. The details of the form will be recorded in a database (this bit I could do myself)
2. Tutors/assessors should be able to view the database on one of the subpages (in a form of a table). Ideally there should be a possibility of exporting the table (and/or particular columns to an .xls file (I know, MS:( )
3. At the end of the week the system should produce a report, ie. send an email containing certain rows of the table (based on the value of one of the columns)

I hope it makes sense. I'd be more than happy to do it myself (especially that I'm slowly learning php) but the system should be up and running within 3-4 weeks and with my current php skills and lack of free time there's no way I could create a reliable and secure system within this timeframe. For that reason, I'd probably be looking at some ready-made solution out there.

thank you.

Don't overthink the simplicit of this task. Do this:

Set up cron job as a user such as root

Create a program using Php (server program -not a web page) tha retrieves the results from the database when cron executes it. Once it retrieves the data, use Php's mail function.

The php functions for this are as follows:

Name the page something like report.php and cron can execute the script as this:

Export to a CSV file, which Excel can load. A CSV is much easier to create programmatically.

BTW, PHP is not the only choice. There's also Python (Django) and Ruby (on Rails).

SQLite would be a simpler alternative since it doesn't require setting up a DBMS server.
Kevin Barry

Please concentrate on validating the inputs since it will be used by others who dont know your thought process.

I would go with a sophisticated - free for production and not just development (but not open source) - DBMS (like Oracle Express or SQL Express) that allows back end validations and consistency checks in case your programme missed some checks, coupled with proven interfaces to PHP, and generic report writer and export options.

The learning curve is not steep and with these features, you should be able to hit the ground running.

If the mind balks at using billy boy's or bully boy's products, then mySql can be used but AFAIK, there is no free version.

OK

Thanks a lot for all your suggestions/advice.
It seems I'll probably have to create it after all. At least I'll learn something new, but no way I'm going to do it within 3 weeks. I've got other responsibilities at work as well.

Well let me know if you need any assistance with it if you decide to use Php and MySQL. Send me a PM if you want and from there, we can go by email.

PostgreSQL is a decent FOSS DBMS. Why the bias against open-source DBMSes?
Kevin Barry

Thanks a lot for your offer of help. I do appreciate it and might need it soon:)

I could probably help with PostgreSQL too - it's just I have always used MySQL (since about 2003 or 2004) and it's what I have stuck to. The reason for this is because, as I understand, PostgreSQL is difficult to set up with multiple virtual nameservers and those are what I always have set up on my servers.

Otherwise, pg_query, pg_fetch_array, etc are really no big deal.

Oops sorry. No bias. Didn't think about Postgres.