MYSQL problem is bamboozling me.....

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:

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!?
 
I think you should have a rethink about your field names!
Also this is assuming that the user_ID(1,2) in all tables mean the same thing and favband_id is a user_id as well.


Some rubbish SQL but:

Code:
select distinct noticeID 
from user_noticeboard
where user_id = '$id' --my notices
or user_id in (select user_id2 from user_friends where userid1 = '$id') --any friends notices
or user_id in (select favband_id from user_favourite_artists where user_id = '$id') --any of my favourite band's notices
or user_id in (select a.favband_id from user_favourite_artists where user_id in (select user_id2 from user_friends where userid1 = '$id')) --any of my friend's favourite band's notices.
order by date
 
Back
Top Bottom