Help "Many-to-many relationships for newcomer

CREATE TABLE project (

project_id INTEGER NOT NULL auto_increment,

name varchar(128) NOT NULL,

`user_id’ int(11),

PRIMARY KEY (project_id)

) ENGINE = InnoDB

;

CREATE TABLE user

(

user_id INTEGER NOT NULL AUTO_INCREMENT,

username Varchar(256),

teacher_id Int(11),

PRIMARY KEY (user_id)

) ENGINE = InnoDB

;

CREATE TABLE people

(

id INTEGER NOT NULL AUTO_INCREMENT,

project_id Varchar(256),

teacher_id varchar(256),

name varchar(256),

phone varchar(256),

PRIMARY KEY (id)

) ENGINE = InnoDB

;

I have 3 tables as above. I want to use relation to get "name" and "phone" in table "people" by "project_id" in table "project" and "teacher_id" in table "user".

Could you help me?

Many thanks,

Nguyen

Have you read this?

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#relational-data

Hi Patrick,

Thank you for your reply. But I’m using Yii 1.1.x not 2.0

Why project_id and teacher_id are varchar? What do you mean by that? Do you want to store multiple ids in them?

Not to worry: http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Hi Softark,

It was a typo. ‘project_id’ and ‘teacher_id’ are INT(11)

Thank you for your link Patrick. I read it many times but I cannot apply it in this case. Could you help me? (I’m a newbie)

What do you have so far? Did you use Gii to generate models?

I don’t understand the relations among the involved models(tables).

What I see is:

1. Project has one User / User may have many Projects

2. User has one Teacher / Teacher may have many Users

3A. People has one Project / Project may have many Peoples

3B. People has one Teacher / Teacher may have many Peoples

What are User, Teacher and People in the first place?

And I really don’t understand what 3A and 3B means in your business.

Hi Patrick,

It just be only a sample code. I did not use Gii.

Hi Soft,

It just be only a sample (not real project). My question is "I want to get "name" and "phone" in table "people" by "project_id" in table "project" and "teacher_id" in table "user".

The former is "Project has many Peoples".

By defining a HAS_MANY relation called "peoples" in Project model, you can write like the following:

$project = Project::model()->findByPk($id);

foreach ($project->peoples as $people) {

    echo "Name = " . $people->name . "\n";

}

And the latter is "User has a Teacher and a Teacher has many Peoples".

By defining a BELONGS_TO relation called "teacher" in User model and a HAS_MANY relation called "peoples" in Teacher model, you can write like this:

$user = User::model()->findByPk($id);

foreach ($user->teacher->peoples as $people) {

    echo "Phone = " . $people->phone . "\n";

}

There’s no “many-to-many” relation involved here in your sample.