Displaying multiple values from same name MySQL database field in Codeigniter & Dwoo

Question

I am building a web application and have opted to use similar table and field names to Wordpress in my application. By that I mean I have a users table and a usermeta table similar to how Wordpress works.

Forgive me for making this question so big, I opted to be as descriptive as possible in this question.

The usermeta table has the following table values;

umeta_id
user_id
meta_key
meta_val

As an example I have the following data in my usermeta table;

user_id = 1
meta_key = first_name
meta_value = 'dwayne

user_id = 1
meta_key = last_name
meta_value = charrington

In my users table I have the following example data;

ID = 1
user_login = admin
user_pass = ui3h423h4o82374
user_email = [email protected]

Basically what I want to do is using the following join which seems to kind of work, sort of:

$db = $this->db;
$db->select('wolf_users.ID, wolf_usermeta.user_id AS ID', FALSE);
$db->select('wolf_users.user_login AS user_login', FALSE);
$db->select('wolf_users.user_email AS user_email', FALSE);
$db->select('wolf_users.user_status AS status', FALSE);
$db->select('wolf_usermeta.meta_key AS meta_key', FALSE);
$db->select('wolf_usermeta.meta_value AS meta_value', FALSE);
$db->from('wolf_users');
$db->join('wolf_usermeta', 'wolf_users.ID = wolf_usermeta.user_id', 'left');
$db->where('wolf_usermeta.user_id = wolf_users.ID');
$q = $db->get();
return $q->result_array();

Firstly: result_array seems to be returning duplicate data from my database (which is a bit strange) have I formatted my join incorrectly or is there something I am missing to prevent this? returning results just using $q->result() seems to stop duplicate data being returned.

I am using Dwoo for my templating and when I echo out the variable that is holding the data using print_r it shows no duplicate query content when using query, but shows duplicate content when using result_array (almost like it looped over the usermeta table a few times).

One of the pages I am using this database query on is a page that lists all clients in a database and then fetches matching data from the usermeta table. So my question is how do I access data from both tables and then display it in my application using a foreach in my view template?

Answer 1

You would expect two rows to appear given your sample data; I don't know if that's what you mean by 'duplicate data'. These rows should differ in that they'd have different values for meta_key and meta_value.

If rows are appearing with the same values in these columns, have you checked the user_meta table or user tables to check there aren't duplicate rows in either of them. Do you have unique key constraints on your tables?

I can't see that the where clause you are specifying affects the query at all, as it is the same as the join condition.

Answer 2

I've checked both database tables and there are no duplicate rows to be found in either table. I removed the where clause because I noticed that the join effectively does the same thing anyway and it was redundant to have it there.

I can't possible fathom what is going on here. From what I can see I've done everything right in my joins, and there should be no reason that rows are being displayed in the query output multiple times (despite the fact there are no duplicate rows in the database). Is there perhaps a better way I can structure my tables to make this easier on myself?

The application I am building kind of needs some kind of custom field functionality like my current usermeta table allows me to do in a separate table to keep everything clean and separated.

I have no unique key constraints either, could setting a field to be a unique key cause problems though? I was thinking of making my user_login (the username field) a unique key to prevent duplicate names.

Answer 3

You understand how joins work right?

By joining meta data to a user, the user will be repeated for each meta data entry you pull out.

This could be avoided with a foreach after pulling data out of the database or by splitting your query into two queries.

$user = $this->db
    ->select('ID, user_login, user_email, user_status AS status')
    ->where('ID', $id)
    ->get('wolf_users')
    ->row_array();

if(!$user)
{
    return FALSE;
}

$user['meta'] = $this->db
    ->select('meta_key, meta_value')
    ->where('user_id', $id)
    ->get('wolf_usermeta')
    ->result_array();

return $user;

A few general MySQL points and tips:

wolf_users.ID, wolf_usermeta.user_id AS ID

No need, you only need to select the user ID once. The second select will override the first, which is of course the same.

$db->select('wolf_usermeta.meta_value AS meta_value', FALSE);

You don't need to do that, the table name will be removed from the result, so renaming them is pointless.

Good luck!