Page 2 of 2

Re: Displaying information from my Database

Posted: Wed Apr 18, 2012 9:56 am
by cybastud
Have a look at the user_friends table above

If you look at the user_frieds table user 1 is friends with user 3 and 5, user 2 is friends with users 5 and 1 and so on.

This is how I got the user's friends(but just the id's of them as in the users table) on my other page using the getFriends() method

Code: Select all

$friends = $user->getFriends();
	echo "<p>Friends with: ".implode(',',$friends). "</p>"; 
This works correctly but only displays the user's friends id as in the user table as it should. eg If I select Peter Pan it would display Friends with: 3, 5

My problem is I need to display all the friends by there display name, I hope Im making sense...

Re: Displaying information from my Database

Posted: Wed Apr 18, 2012 9:58 am
by Celauran
Change your query.

Code: Select all

SELECT f.friend_id, u.first_name, u.last_name
FROM user_friends AS f
JOIN user AS u ON f.friend_id = u.user_id
WHERE f.user_id = whatever

Re: Displaying information from my Database

Posted: Wed Apr 18, 2012 12:41 pm
by cybastud
Sorry Im new to php and I really do not understand what you just done now....

Re: Displaying information from my Database

Posted: Wed Apr 18, 2012 1:37 pm
by Celauran
Your getFriends() method doesn't do what you want because of the query you're using.

Code: Select all

public function getFriends(){ // method getFriends()
                $sql = 'select friend_id from user_friends where user_id = '.$this->user_id; // select freind id from users_friends table
                $results = mysql_query($sql); // assign query to variable $results
                $friends = array(); // array
                while($row = mysql_fetch_array($results)){ // loop through the array
                                $friends[] = $row['friend_id']; // place all 'friend id' values in an array called $friends
                }
                return $friends; // return users friends
}
So fix it

Code: Select all

public function getFriends()
{
    // $sql = 'select friend_id from user_friends where user_id = '.$this->user_id;
    $sql = "SELECT f.friend_id, u.first_name, u.last_name
            FROM user_friends AS f
            JOIN user AS u ON f.friend_id = u.user_id
            WHERE f.user_id = {$this->user_id}";
    $results = mysql_query($sql);
    $friends = array();
    while($row = mysql_fetch_array($results))
    {
        $friends[] = $row;
    }
    return $friends;
}

Re: Displaying information from my Database

Posted: Wed Apr 18, 2012 2:02 pm
by cybastud
ok the3 above does not work, gives me the following error "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:...."

However I got it run with:

Code: Select all

public function getSelectedFriends(){
                //$sql = 'select display_name from users where user_id = '.$this->user_id;
				$sql = 'SELECT f.friend_id, u.first_name, u.last_name
				        FROM user_friends AS f
						JOIN users AS u ON f.friend_id = u.user_id
						WHERE f.user_id = '. $this->user_id;
                $results = mysql_query($sql);
                $friends = array(); // array
                while($row = mysql_fetch_array($results)){
                                $friends[] = $row['first_name']. ' '. $row['last_name'];
                }
                return $friends; 
}
This runs fine and it gives the correct information just not in the correct order. Its displaying the data in numerical order

eg if I select Peter Pan I get the following output:

User known as: Peter Pan

Friends with Pollen Ndlanya // user_id 3

Friends with Kaizer Motaung // user_5

Return

which is correct, however when I select Elvis Presley I get the following:

User known as: King of Rock

Friends with Peter Pan // user_id 1

Friends with Kaizer Motaung // user_id 5

Return

which is the right information but its not in the required order. I want it to look like this:

User known as: King of Rock

Friends with Kaizer Motaung // user_id 5

Friends with Peter Pan // user_id 1

Return

I do not want it to display in numerical order but rather the order in which is set in the DB in the user_friends table...can you help with this please?

Re: Displaying information from my Database

Posted: Fri Apr 20, 2012 10:28 am
by x_mutatis_mutandis_x
Append "order by f.user_friend_id" to your query in getSelectedFriends()

Re: Displaying information from my Database

Posted: Mon Apr 23, 2012 8:59 am
by cybastud
ok i've added the order by as follows:

Code: Select all

public function getSelectedFriends(){
                $sql = 'SELECT f.friend_id, u.first_name, u.last_name 
				        FROM user_friends AS f
						JOIN users AS u ON f.friend_id = u.user_id
						WHERE f.user_id = . $this->user_id
						ORDER BY f.user_friend_id';
						 // select the friend_id first_name, last_name from user_friends table, join to users table according to user_id selected
                $results = mysql_query($sql); // assign query to variable $results
                $friends = array(); // array
                while($row = mysql_fetch_array($results)){ // loop through the array
                                $friends[] = $row['first_name']. ' '. $row['last_name']; // place all first_name and last_name of the selected users friends in an array called friends
                }
                return $friends; // return users friends
But I'm now getting the following error: "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:....."

Any ideas?

Re: Displaying information from my Database

Posted: Mon Apr 23, 2012 9:09 am
by Celauran
If your query is returning FALSE, then there's an error in the query itself. Check the output of mysql_error()

EDIT: Nevermind. I just saw it. You need to close quotes before $this-> and open them again after.

Code: Select all

$sql = 'SELECT f.friend_id, u.first_name, u.last_name 
        FROM user_friends AS f
        JOIN users AS u ON f.friend_id = u.user_id
        WHERE f.user_id = ' . $this->user_id . '
        ORDER BY f.user_friend_id';

Re: Displaying information from my Database

Posted: Mon Apr 23, 2012 10:08 am
by cybastud
Works perfectly, thanks a million :)