开发者

Relational MySQL - fetched properties?

I'm currently using the following PHP code:

    // Get all subordinates
    $subords = array();
    $supervisorID = $this->session->userdata('supervisor_id');
    $result = $this->db->query(sprintf("SELECT * FROM users WHERE supervisor_id=%d AND id!=%d",$supervisorID, $supervisorID));
    $user_list_query = 'user_id='.$supervisorID;
    foreach($result->result() as $user){
     开发者_JS百科   $user_list_query .= ' OR user_id='.$user->id;
        $subords[$user->id] = $user;
    }

    // Get Submissions
    $submissionsResult = $this->db->query(sprintf("SELECT * FROM submissions WHERE %s", $user_list_query));
    $submissions = array();
    foreach($submissionsResult->result() as $submission){
        $entriesResult = $this->db->query(sprintf("SELECT * FROM submittedentries WHERE timestamp=%d", $submission->timestamp));
        $entries = array();
        foreach($entriesResult->result() as $entries)   $entries[] = $entry;
        $submissions[] = array(
            'user' => $subords[$submission->user_id],
            'entries' => $entries
        );
        $entriesResult->free_result();
    }

Basically I'm getting a list of users that are subordinates of a given supervisor_id (every user entry has a supervisor_id field), then grabbing entries belonging to any of those users.

I can't help but think there is a more elegant way of doing this, like SELECT FROM tablename where user->supervisor_id=2222

Is there something like this with PHP/MySQL?

Should probably learn relational databases properly sometime. :(

EDIT: here is the relevant schema

submissions
===============================
id, user_id, timestamp

submittedentries
===============================
id, user_id, timestamp

users
===============================
id, supervisor_id, email

one submission has many submittedentries, and currently I'm referencing this by using the timestamp. I'd be more than willing to alter this if someone can suggest a more efficient way. (and yes, there are more fields that I'm omitting)


This should, if I got the column names correct, get a list of submissions from users who have the specified supervisor.

SELECT * FROM users, submissions
WHERE users.supervisor_id = $supervisorID
AND submissions.user_id = users.id

This version attempts to combine the timestamp checking as well.

SELECT * FROM users, submissions, submittedentries
WHERE users.supervisor_id = $supervisorID
AND submissions.user_id = users.id
AND submittedentries.timestamp = submissions.timestamp

Edit: Updated to match the additional table info. I'm still not 100% sure that the second version is correct, will need to be tested against the database to find out :)

Oh, and in practice you should probably replace the asterisk with the names of the actual columns you want to retrieve.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜