Permabanned
- Joined
- 3 Jul 2008
- Posts
- 3,762
- Location
- My fabulous ship
Hey guys, I got a problem and I was hoping you could help me get my thinking on the right path...
I am making a notice board. Similar to facebooks notice board where you see updates of your friends/groups etc.
at present I have the following tables:
user table (all their info in here)
user_favourite_artists table (id, artist id, user id, to link the artist and the user)
- id, user_id, favband_id, date
user_friends table (a link between users to indicate they are friends)
- id, user1_id, user2_id, date
user_noticeboard (a table to log all action by users, friends and bands)
- id, user_id, band_id, notice_id, date
Im not using foreign keys nor combined keys, combined keys work for some functionality that I want (like showing someone adding a new band to their favourites list) but that is only because my user id is part of the key which isnt the case for some of the updates I want displayed (artists adding new tracks for example)
I want the results in order of newest update to oldest update which means that I cant have a query before the user_noticeboard table because I would require a while loop which would not give me the results in order of user_noticeboard date.
so from what I know:
- The user_noticeboard table has to be in the first query to get the ordering correct.
- The results need to be relevant to myself, my friends, and my favourite artists
I was thinking of a joined query that includes my userid, my friends userid and my artists id. Im not sure if this is right or even remotely close to what I need but would love some clarification:
etc etc
now would this output data or give some error and what would be a better way to approach this? because Im running out of ideas!?
I am making a notice board. Similar to facebooks notice board where you see updates of your friends/groups etc.
at present I have the following tables:
user table (all their info in here)
user_favourite_artists table (id, artist id, user id, to link the artist and the user)
- id, user_id, favband_id, date
user_friends table (a link between users to indicate they are friends)
- id, user1_id, user2_id, date
user_noticeboard (a table to log all action by users, friends and bands)
- id, user_id, band_id, notice_id, date
Im not using foreign keys nor combined keys, combined keys work for some functionality that I want (like showing someone adding a new band to their favourites list) but that is only because my user id is part of the key which isnt the case for some of the updates I want displayed (artists adding new tracks for example)
I want the results in order of newest update to oldest update which means that I cant have a query before the user_noticeboard table because I would require a while loop which would not give me the results in order of user_noticeboard date.
so from what I know:
- The user_noticeboard table has to be in the first query to get the ordering correct.
- The results need to be relevant to myself, my friends, and my favourite artists
I was thinking of a joined query that includes my userid, my friends userid and my artists id. Im not sure if this is right or even remotely close to what I need but would love some clarification:
PHP:
<?php
$id = 9134;
$query1 = ("select * from user_friends, user_favourite_artists, user_noticeboard where user_friends.user1_id like '$id' or user_friends.user2_id like '$id' and user_favourite_artists.user_id like '$id' order by user_noticeboard desc");
$result1 = mysql_query($query1) or die ("youuuuu son of a....");
while ($r1 = mysql_fetch_array($result1))
{
$nbdate = $r1['date'];
$user1_id = $r1['user1_id'];
$user2_id = $r1['user2_id'];
}
?>
etc etc
now would this output data or give some error and what would be a better way to approach this? because Im running out of ideas!?