Welcome Guest, Not a member yet? Register   Sign In
Display related data from user profile
#1

[eluser]doubleplusgood[/eluser]
Hi there,

My site gives users their own profile, with some URL routing, for example: www.mywebsite.com/profile/hanabi

I'm trying to grab information from other tables in the database, based on the user's id for the current profile (as above).

I have a profile_model that has the following function;

Code:
function get_single_profile($username)
        {
            $this->db->where('username', $username);
            $query = $this->db->get('users', 1);
            
            if ( $query->num_rows() == 1 )
            {
                return $query->row();
            }
            
            return FALSE;
        }

So as you can see, each user is stored in the users table with an id. What I would now like to do is get rows of data from another table (vehicles) where the user id matches that of the current profile.

I was wondering if anyone could tell me how I could accomplish this? I'm sure it would be much easier if i was passing the id in the profile function rather than the username, but this is needed for the URL routing. :S

Thank you for any help you might be able to pass on. Smile
#2

[eluser]Narkboy[/eluser]
Two methods spring to mind:

1) If users have a profile, then they login. If they login, you're using sessions - simply store the user_id in the session. Not the worlds most secure idea, but the easiest for you. If people put in credit card numbers or anything important, then this one ain't great. If security is an issue, you've covered it I'm sure.

2) Simply chain the db queries. After your call for the user profile data above, drop in another one for the veichle data using the id that you get from the first db call. Then drop each database table into an array, lump them together and return them.

Smile
#3

[eluser]doubleplusgood[/eluser]
Hey,

Thanks for the reply. I guess option 2 would be better to use. I will have to take a look at how I can do this.

Would it be something like this?

function get_single_profile($username)
{
$this->db->where('username', $username);
$query1 = $this->db->get('users', 1);

$this->db->where('userId', $id);
$query2 = $this->db->get('vehicles', 1);

if ( $query->num_rows() == 1 )
{
return $query->row();
}

return FALSE;
}

? Confused
#4

[eluser]mddd[/eluser]
Narkboy's method #1 will work, but only for people who are looking at their own profile while they are logged in. I guess the profile pages will also be looked at by other people, and of course THEY will not have the right user id in their session. So this will not work for that case.

#2 is better of course. Get the info from the database. In the interest of database speed, I would use a JOIN statement to get the information all in one go. Like so:
Code:
$this->db->select('users.*, extrainfo.someinfo');
$this->db->where('users.username', $username);
$this->db->from('users');
$this->db->join('extrainfo', 'extrainfo.userid = users.id', 'left');
$this->db->get();
#5

[eluser]doubleplusgood[/eluser]
Thanks man. I've just quickly put together this query;

Code:
public function getFeaturedVehiclesByUser(){
            $this->db->select('users.*, vehicles.*');
            $this->db->where('users.username', $username);
            $this->db->from('users');
            $this->db->join('vehicles', 'vehicles.userId = users.id', 'left');
            $this->db->where('vehicles', 'vehicles.featured = 1')
            $this->db->get();

            if ( $query->num_rows() == 1 )
            {
                return $query->row();
            }

            return FALSE;
        }

This should (in theory) find all vehicles where the userId of the profile being viewed matches and also where rows in the vehicles table are marked as featured. Hopefully this looks about right. Big Grin
#6

[eluser]mddd[/eluser]
Yes, looks right to me. You could put the 'where' clause in the 'join' but I don't think that makes much difference in the end.
Code:
// this:
$this->db->join('vehicles', 'vehicles.userId = users.id', 'left');
$this->db->where('vehicles', 'vehicles.featured = 1');
// can be written as:
$this->db->join('vehicles', 'vehicles.userId = users.id AND vehicles.featured=1', 'left');

But remember: if a user has more than 1 vehicle, you will get more than 1 row from this query!
So your check 'if num_rows is 1' is no longer right. You should just check if there are any rows found.
The result might look like this:
Code:
For a person with one vehicle:

users.id   users.username   vehicles.userId   vehicles.featured   vehicles.name
-------------------------------------------------------------------------------
1          mike             1                 1                   vehicleX


For a person with 3 vehicles:

users.id   users.username   vehicles.userId   vehicles.featured   vehicles.name
-------------------------------------------------------------------------------
2          pete             2                 1                   vehicleX
2          pete             2                 1                   vehicleY
2          pete             2                 1                   vehicleZ


For a person with 0 vehicles:

users.id   users.username   vehicles.userId   vehicles.featured   vehicles.name
-------------------------------------------------------------------------------
3          chris            null              null                null
#7

[eluser]doubleplusgood[/eluser]
Thanks man,

I now have this;

Code:
function get_single_profile($username)
        {
            $this->db->where('username', $username);
            $query = $this->db->get('users', 1);
            
            if ( $query->num_rows() == 1 )
            {
                return $query->row();
            }
            
            return FALSE;
        }

        public function getFeaturedVehiclesByUser(){
            $this->db->select('users.*, vehicles.*');
            $this->db->where('users.username', $username);
            $this->db->from('users');
            $this->db->join('vehicles', 'vehicles.userId = users.id AND vehicles.userFeatured=1', 'left');
            $this->db->get();

            if ( $query->num_rows()>0)
            {
                return $query->row();
            }

            return FALSE;
        }

But it gives me the following error;

Code:
Severity: Notice

Message: Undefined variable: username

Filename: models/profile_model.php

Line Number: 48

I guess I still need to pass that username somehow?
#8

[eluser]mddd[/eluser]
You are still using $username in your new method, just like you were before. So yes, you must pass $username to your method!
#9

[eluser]doubleplusgood[/eluser]
Hey,

Unfortunately I still get the same error when I do this;

Quote: public function getFeaturedVehiclesByUser($username){
$this->db->select('users.*, vehicles.*');
$this->db->where('users.username', $username);
$this->db->from('users');
$this->db->join('vehicles', 'vehicles.userId = users.id AND vehicles.userFeatured=1', 'left');
$this->db->get();

if ( $query->num_rows()>0)
{
return $query->row();
}

return FALSE;
}
#10

[eluser]mddd[/eluser]
Check the rest of your code; are you calling the right functions. Are you using the right variables?
From here you'll have to check it out yourself, I'm afraid..

One thing I do notice: you are using $query->num_rows and $query->row.
But $query is not set anywhere. You should use: $query = $this->db->get();

Also, if there are results, there may be more than 1 row so you should not return $query->row() but $query->result() or $query->result_array() !




Theme © iAndrew 2016 - Forum software by © MyBB