How to show Data from various table on one page?

Yii 1.1 General Discussion
1

Hi,

I’m currently learning Yii and trying to create a time tracking application where users can track their working time spent on different projects.

I now managed to change the default application so that users and project data are managed in a MySQL database.

What I would like to create is some kind of “dashboard” page that is shown when a user logs on where he can see all the projects assigned to him in one table but also a list of tracked entries… but I’m not sure how to start.

Is there any sample on how to show data from various databse tables (of course as results of specific queries)?

The second thing I’m looking for is how to create data entry forms that have drop-down fields which are filled with data from a table (e.g. a list of projects that are available to the currently logged on user)

Despite the really good documentation, the learning curve is still quite steep for me…

thanks & regards

Tom

2

About the 2nd thing, you can do it how is described here

About the 1st question, first of all you need to create a model for each table

after taht you can display it in many ways … some of them are CGridView and CListView

You can also create a crud using Gii to see how its done

Also, if you decide to use CGridView you can add the functionalities you want by customizing CButtonColumn

Thats a good start point

Any other doubt just ask

3

Ah ok - but that is not what I’m after (maybe I might use this later sometimes…)

Imagine a "edit time entry" form where a use can change his personal time records… one of the form elements would be a dropdown that contains all possible projects with the specific project selected that was used to create this time entry.

So most of the forms controls come from the "times" table.

The possible selections of the dropdown comes from the "projects" table and the number/id of the currently selected project would come from the "times" table.

In the sample you show, the dropdown items are in a literal array, but I need them to be filled by the result of a query (since not all projetcs are allowed for every user)

One of my problems is that I’m not only new to Yii… but also relatively new to PHP…

Tom

4

The best way to start is to get a good understanding of CActiveRecord. If your tables are already created, use ‘gii’ to generate your models and to generate CRUD operations. Looking at the generated code will certainly inspire you a lot.

If your mysql db is not with an engine allowing foreign keys, finish your model generation manually by adding ‘HAS_MANY’ and ‘BELONGS_TO’ relations (and a few rules like ‘exist’…)

I am just throwing few ideas for you to start spinning the wheels (from my understanding of what you’re after).

Do it step by step, start easy. Start by being able to generate a form for saving/updating a single entry, then by being able to list available time records for your current user. And then more complex…

  • Generating a form is easy with CActiveForm widget => look at what CRUD can do for you and then adjust. You may want to use CHtml::listData() for your dropdown list.

For grabbing all the times for the current user. Considering ‘Time’ and ‘Project’ are your models with a time record belonging to a User and a Project.


$times = Time::model()->with('project')->findAll('user_id=:uid', array(':uid'=>Yii::app()->user->id);

OR (User has many times which belong to project).


$times = User::model()->with('times')->with('project')->findAll('user_id=:uid', array(':uid'=>Yii::app()->user->id);

OR you may prefer to use raw queries with DAO.

Then You can list them in a view with a foreach or more appropriate you need a CDataProvider if you want to pass it to a CListView widget


$dataProvider=new CActiveDataProvider('Time', /*conditions, parameters, with...*/);

And with a CListView you generate a stack of update forms (for each record) plus a create new at the end…

5

Thanks for the time taken to answer me… I think I now have enough to look up in the documentation for the beginning.

Tom

6

Hi,

I have now played a little bit… but I’m stuck at the beginning :angry:

I added the following code to my \protected\views\site\index.php page:




   $dataProvider=new CActiveDataProvider('projects');


   $this->widget('zii.widgets.CListView', array(

       'dataProvider'=>$dataProvider,

       'itemView'=>'_view',   

       ),

   ));

… but all I get is an error message:

SiteController cannot find the requested view "_view".

How do I tell Yii to use the already existing _view.php from the protected\views\projects folder?

Of course I can copy the _view files from the other directories, but I don’t think this is the way it is meant to be?

7

For now is better for you to copy the file.

If you want to share code between controllers, you should use Widgets, but this is part of advanced course of yii. ;)

Copy the file and move forward, this is my advice.

About widgets, you can read a lot in the forum.

P.s: for include a view of another controller use ‘application.views.project._view’, but is not the right way to proceed.

8

Thanks - I have now also found this by myself :)

But I don’t understand why this is not the correct way.

I also have a problem with CActiveDataProvider and SQL joins.

The following query works without problems in EMS SQLManager:


select tbl_user.username, tbl_projects.projectname, tbl_times.start_time, tbl_times.stop_time 

from tbl_user,tbl_projects,tbl_times 

where tbl_times.id_user = `tbl_user`.id and `tbl_times`.id_project = tbl_projects.id and isnull(tbl_times.stop_time)

But I’m lost on how to do this with the CActiveDataProvider… I always get ‘unknown column in where clause’…

The biggest problem I see with the API documentation is that there are no comprehensive code samples for the methods and attributes… so the beginner has to jump around in circles, searching forum, sample codes, tutorials etc. - thank god for tabbed browsers :D