Pulling and sorting MySQL data

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

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Pulling and sorting MySQL data

Post by evilmonkey »

I decided to post this here instead of the databases section because I think the solution requires more PHP than MySQL. Basically, I want to pull data from one table and organize it using the data from another table. I have a table called users which holds all the info about every user of my site. I also have a table called favourites which is basically two columns: user_id and fav_id. The person is fav_id is one user_id's favourites list. If I am pulling all the info about a user,I also want to pullhis favourites list, so I use the following code:

Code: Select all

mysql_query("SELECT * FROM `favourites` WHERE `user_id` = $this->userid", $this->db);
The complication comes from the fact that every user on the site has a popuarity. When I pull the favourites for a given user, I want the 10 most popular users shown first. How can I do this? Is there a query that can do itfor me?

Thanks. :)
Last edited by evilmonkey on Sun Dec 18, 2005 5:06 pm, edited 1 time in total.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Code: Select all

select * from users, favourites where users.user_id = favourites.user_id order by favourites.fav_id limit 10;
I almost certainly am useing the wrong column to order by but what you need to do is join the tables, this should help you grasp the basics.

And this thread should be in the database forum.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Okay, I really don't get this. I'll provide a little more information in hopes that someone can help me. The "favourites" table has two feilds: "id" and "fav_id". The person identified by "id" has the person identified by "fav_id" on thier favourites list. Niether feild is unique because person identified by "id" may have more than one favourite on his list, just like the person identified by "fav_id" may be on more than one person's favourites list. The table "users" contains all the user information(username, password, location, etc), and one the feilds is called "popularity". The highesr than number is, the better.

Now, a hypothetical situation: Someone vistits A's profile, and A has B, C, D, and E on his favourites list. B has a popularity of 10, C has a popularity of 50, D has a popularity of 2, and E has a popularity of 14. When the favourites are being listed, I want to display C first, followed by E, then B, and finally D.

Structuring the query like the following

Code: Select all

SELECT * FROM `users`, `favourites` WHERE `favourites`.`id`="1" ORDER BY `users`.`popularity` DESC
gives me a myriad of irrelevant results, and I can see why. MySQL has no way of knowing whose popularity I'm referring to. I don't understand how to fix that. I read that article about the JOIN sytax, but I'm still quite confused.

And yeah, on second though, this should probably be in Databases.

Thanks for the help.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Anyone? (sorry for bumping, but I need an answer).
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post by trukfixer »

Code: Select all

$sql = "select users.* from users,favorites where users.fav_id = favorites.fav_id and favorites.user_id = '$user_id' order by users.popularity desc";
In other words
select all from user table where the user table user_id is in favorites table fav_id *and* favorites table user_id =(the specific user whose page is being looked at) user_id order by popularity desc

do dat help? :)
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Yup, that did it. Thank you very much, I didn't realize you can equate feilds in one table to feilds in another for a query.

Thanks very much. :)
Post Reply