PDA

View Full Version : PHP reports


cocoonfx
05-03-2009, 11:46 PM
Hello chaps


I am about to undertake some work which involves members taking a test and then recording them into a table. (thats the easy part) i then want to be able to say in 6 weeks be able to download the previous test results by user.

Would it be sensible to just have one table i.e stats_table

And then every time a user enters there results a new data row is added to the table.

Then from there i can then write a variable to look up all user names and pull all the records from a date range??

Also is it possible then to press a button and the a report is created as excel or a csv file?

So many questions i know and i hope this makes sense...

davidj
05-04-2009, 07:43 AM
what you need is 2 tables

User & Results (1 to many)

You can create these tables and then create a view in MYSQL which joins them virtually. You can query the view as if it were a actual table.

////

With PHP you can write to any file and format.

cocoonfx
05-04-2009, 08:54 AM
Hello DJ thanks for the quick response.

I never used the VIEW statement before in MySQL

So i imagine when i create the table i would do the following SQL:

Create Table Results Name
`contact_id` int(10) default NULL,
`username` varchar(200) default NULL,
`companyname` varchar(200) default NULL,
VIEW User_Table User_Id
etc etc etc

And then in PHP

Select View User Table From User Id

Etc..

davidj
05-04-2009, 09:36 AM
using EMS MYSQL Manager create a view.

just open a script window...

syntax...


CREATE VIEW view_name
AS SELECT * FROM table1 LEFT JOIN TABLE1 ON TABLE1.FIELD1 = TABLE2.FIELD2 etc...


If you called your view 'MYVIEW' and ran the above then a View will be created called MYVIEW. You just reference in your PHP this as if it were a table in its own right.

* Me and Corrosive found an issue with views and its this... Even though we have a different table defined you cannot have fields with the same name in the join!

Corrosive
05-04-2009, 10:14 AM
Just to add as well...

If you are using PHP myadmin on your server/hosts then you need your primary account details for it to create a view. Don't know why though!