Displaying information from my Database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

cybastud
Forum Newbie
Posts: 13
Joined: Tue Apr 17, 2012 1:01 pm

Re: Displaying information from my Database

Post 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...
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Displaying information from my Database

Post 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
cybastud
Forum Newbie
Posts: 13
Joined: Tue Apr 17, 2012 1:01 pm

Re: Displaying information from my Database

Post by cybastud »

Sorry Im new to php and I really do not understand what you just done now....
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Displaying information from my Database

Post 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;
}
cybastud
Forum Newbie
Posts: 13
Joined: Tue Apr 17, 2012 1:01 pm

Re: Displaying information from my Database

Post 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?
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: Displaying information from my Database

Post by x_mutatis_mutandis_x »

Append "order by f.user_friend_id" to your query in getSelectedFriends()
cybastud
Forum Newbie
Posts: 13
Joined: Tue Apr 17, 2012 1:01 pm

Re: Displaying information from my Database

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Displaying information from my Database

Post 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';
cybastud
Forum Newbie
Posts: 13
Joined: Tue Apr 17, 2012 1:01 pm

Re: Displaying information from my Database

Post by cybastud »

Works perfectly, thanks a million :)
Post Reply