Page 1 of 1

Pulling and sorting MySQL data

Posted: Sun Dec 18, 2005 2:26 pm
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. :)

Posted: Sun Dec 18, 2005 2:59 pm
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.

Posted: Sun Dec 18, 2005 5:26 pm
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.

Posted: Mon Dec 19, 2005 8:16 am
by evilmonkey
Anyone? (sorry for bumping, but I need an answer).

Posted: Mon Dec 19, 2005 9:13 am
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? :)

Posted: Mon Dec 19, 2005 10:58 am
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. :)