Mysql query generation
This is my user table schema
CREATE TABLE IF NOT EXISTS `ehobe_user` (
`user_id` bigint(20) NOT NULL,
`user_email` varchar(80) NOT NULL,
`user_password` varchar(50) NOT NULL,
`user_fname` varchar(255) NOT NULL,
`user_lname` varchar(255) N开发者_如何学GoOT NULL,
`user_terms` enum('yes','no') NOT NULL DEFAULT 'yes',
`is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
And this is my friends table schema
CREATE TABLE IF NOT EXISTS `ehobe_friends` (
`user_id1` bigint(20) NOT NULL,
`user_id2` bigint(20) NOT NULL,
`relationship_id` int(1) NOT NULL COMMENT '1 - user1 request, 2- user2 request, 3 - friends, 4- user1 blocked, 5 - user2 blocked'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
So i need to select the user first name and last name who are my frinds in the friends table.
Suppose you want friends of USER_ID #67.
Try:
select u.user_id, u.user_fname, u.user_lname
from ehobe_user u
inner join ehobe_friends f1 on (u.user_id = f1.user_id1)
where f1.user_id2 = 67
union
select u.user_id, u.user_fname, u.user_lname
from ehobe_user u
inner join ehobe_friends f2 on (u.user_id = f2.user_id2)
where f2.user_id1 = 67
Logically, you want to get all of the friends in an array and check each user for matching info. I would give you code, but I don't know what language you're writing this in.
精彩评论