How implement a table_info of another table

Question

I'm trying to create a system more dynamic for my users, implementing a way of they create each necessary information when they needed. (Sorry if my english is weak)

First, I have a table named User, like this:

User:

id   |   name   |    login   |     password

1    |   Rose   |  rose.120  |     897763
2    |   John   |  john.red  |     120347
3    |   Mark   |  mark.foo  |     385598

and the other table with some info of each user:

User_info:

id  |  user_id  |  info_name      |   value          |  required  |  order

6   |   1       | Telephone       |  555-4083        |   yes      |   2 
7   |   1       | Email           |  rose.120@g.com  |   yes      |   1 
8   |   1       | Another E-mail  |  rose.mg@fc.com  |   no       |   3

When I get this values from the database, how make an efficient way to set a PHP Object, or an Array of this values.
I tried to make a mysql_query and with this result make a loop in the Object, and for any Row make a WHERE clause to the user id. (I'm using CodeIgniter)

    if( $user != false ) $this->getUser();
    foreach( $this->user->result() as $row ) {
        $this->db->where('user_id', $row->id);
    }
    $this->db->from('user_info');
     ...

and when I have this Object I will need to put the User_info rows in the User Object. Correct?
I'm just curious if this is a good way of doing this, or I'm in the wrong way.
I hope that's clear.
Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 12:21

Basically, yes. You could set up your model to define a method for retrieving information from both your user_info and user tables, however. Something along these lines:

// in user_model.php
function get_user_with_info() {
  $user = $this->getUser();
  $user['info'] = $this->db->where('user_id', $row->id)->from('user_info')->row();

  return $user;
}

If you are planning to make this query often, you will quickly run into the N+1 problem. A better option in Codeigniter might be to use a table join in a get_users function:

function get_users() {
  $this->db->select('*')
  $this->db->from('user');
  $this->db->join('user_info', 'user.id = user_info.user_id');

  $results = $this->db->result();
  $users = [];
  foreach ($results as $result) {
    $user_id = $result['user_id'];
    if (!isset($users[$user_id])) {
      $users[$user_id] = array(
        'id' => $user_id,
        'info' => array(
          // add info fields from first result
        )
      );
    } else {
      $users[$user_id]['info'][] = array( 
        // add additional info fields
      );
    }
  }
  return $users;
}

You will need to adjust the specifics, but hopefully this gives somewhere to start.

Yes, exactly what I want, but when I try to list more than one User (row), I need to implement a loop right? And for every row of the User query sended for the mysql. — Guilherme, Apr 25 '12 at 12:21
Yes. I've updated with an example of how to retrieve the rows with a single query and clean up the results. Hopefully this helps! — rjz, Apr 25 '12 at 16:16

score 0 · Answer 2 · edited Aug 07 '12 at 00:10

0

While using code codeIgniter, make a function in your model file and put your code. Then, in the controller, call this function in the model and send your required parameter. You are using codeigniter then you have to follow the architecture too.

edited Aug 07 '12 at 00:10

Shog9

156,901
35
231
235

answered Apr 25 '12 at 04:47

I'm using, this function is in the User_model. And I'm using the Active record of the Database Class! – Guilherme Apr 25 '12 at 12:08

score 0 · Answer 3 · answered Apr 25 '12 at 04:55

0

Don't forget, in this way you must working with dynamic arrays in user object. Instead of fields named as "Telephone", "Email" and other, which you can access as $user->Telephone, $user->Email and other (this is a simple and I think a good way), you must create dynamic array with pairs $info_name->$value. And in "User" object you must create additional functionality, which parse each property $info_name. This is more flexible, but more complex.

So I think, you must find golden mean for you between flexibility and complexity.

answered Apr 25 '12 at 04:55

Valeriy Gorbatikov

3,459
1
15
9

Thanks, is a good ideia, but remove all the "dynamic" idea of the user_info, because the admin user can change the field Info Name of each value. And thats why I placed another field called Order, to sort this information, as it can be defined by the admin user. – Guilherme Apr 25 '12 at 12:15

How implement a table_info of another table

3 Answers3