Jump to content

unemployment

Members
  • Posts

    746
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

unemployment's Achievements

Advanced Member

Advanced Member (4/5)

0

Reputation

  1. So... I am much closer now with the code you provided. I put in the appropriate column names below. The problem with your output above is that Jeffrey shows up TWICE in the last column. I need the column to exclude duplicates. Then I also need to replicate this code for if my id was the friend_id and create a union for both queries. One more question: Why am I included in the last row? How do I remove that in the WHERE clause? Hopefully we can sort this out soon. Thanks. SELECT a.user_id, x.username, a.friend_id, y.username, b.friend_id, z.username FROM partners a JOIN partners b ON a.friend_id = b.user_id AND b.friend_id NOT IN ( SELECT m.friend_id FROM partners m JOIN partners n ON m.friend_id = n.user_id WHERE m.user_id = 1) JOIN users x ON a.user_id = x.id JOIN users y ON a.friend_id = y.id JOIN users z ON b.friend_id = z.id WHERE a.user_id =1;
  2. I'm not sure I follow this. You have uname multiple times in the select. I'll give it a shot though.
  3. Well the first query I posted will grab my friends friends. I just need to compare those against my friends and exclude my friends.
  4. I'll look into it. I also tried a few other combinations but all I got were my friends and not my friends friends.
  5. This unfortunately doesn't work. I just get an awkward list of my username over and over with a list of a few other peoples which surely this can't be correct. I also don't think the NOT IN in the where clause makes sense because there is another side to this. Your id can also be the friend_id. Your id is the user_id in the partners table if you sent the request. If you are receiving the request then your id is the friend_id. Any other thoughts?
  6. users table id firstname lastname partners table partner_id user_id friend_id approved Basically I need to create aliases to be able to get my friends friends. I just don't know how to restrict the results by removing people i'm already friends with.
  7. I'm in the process of making a people you may know feature. To do this I am trying to grab my friends friends that I am not friends with. I need the SQL to say... If my friends friends ID matches any of my friends Id then exclude it from the select. I'm not sure how to do this or at least how to do it efficiently. Query: SELECT users.id, users.firstname, users.lastname, myfriend.id, myfriend.firstname, myfriend.lastname, theirfriend.id, theirfriend.firstname, theirfriend.lastname FROM users INNER JOIN partners ON partners.user_id = users.id AND partners.approved = 1 INNER JOIN users myfriend ON myfriend.id = partners.friend_id INNER JOIN partners partners2 ON partners2.user_id = myfriend.id INNER JOIN users theirfriend ON theirfriend.id = partners2.friend_id WHERE users.id = 1 Current Results My ID My Name My Friends Id My Friends Name My Friends Friends ID My Friends Friends Name 1 Jason 2 Chelsea 4 Davey 1 Jason 2 Chelsea 6 Jim 1 Jason 2 Chelsea 12 Peter 1 Jason 2 Chelsea 16 Cameron 1 Jason 2 Chelsea 38 Felicia 1 Jason 4 Davey 5 Adam 1 Jason 4 Davey 14 Jeffrey 1 Jason 5 Adam 6 Jim 1 Jason 5 Adam 14 Jeffrey 1 Jason 5 Adam 17 Dan 1 Jason 10 Michael 5 Adam 1 Jason 13 Jacek 4 Davey 1 Jason 20 Victor 1 Jason
  8. I guess I'll start with the location based implementation first and work my way towards a more advanced approach.
  9. This is more of a logic question. I have built a beta version of my site and I am currently working on getting beta V2 out, but I want to implement location based searching of users (particularly with a google api integration). Aside from the API stuff, how do I perform location based searches on the backend. For instance, I have all of my users locations (city, state, country), but how can I say to grab all users by that state when that state is typed in or grab all users by that city when the city is typed in and the same thing for country? Is there anyway to set this up with a default radius too? So if I search for a town, I get a 20 Mile radius result? I really have no idea how to go about the dynamic look up match. Any good tutorials on this?
  10. Wow... never knew that. What is the benefit of doing the indexes for the ON condition?
  11. please explain... I don't have a clue what that means. How do I go about altering these indexs and what should I do to them to improve the joins?
  12. Essentially it is going to calculate one person score. You view their profile, you see their score. However, that score might ultimately be compared across everyone score to determine what percentile they fall in.
  13. No need for the union really it was just old code. This score will be calculated every time you visit a users profile.
  14. The end goal is to calculate the users reputation versus other users on the site. I imagine a scale on user profile that will display the percentile that the user is in compared to the rest of the user base. So if you are in the 90th percentile then you would have a high reputation.
  15. Essentially, I am making a game for entrepreneurs and investors. The game is simple... do certain tasks in my app and build your reputation by gaining points. Here is a list of some of the requirements: Personal Profile completion = 200 points Having a company video = 50 points If the user is an investor and is accredited add 100 points Blog Post = 50 Points Number of votes submitted = 5 points each Created or joined a verified company profile = 200 points This helps build credibility in the community. My problem is that I need to join so many tables to build the point system and it's terribly confusing for me. Here is the current code and yes I know it's a bit of a mess and probably doesn't need a union. function get_score($uid) { $sql = array(); $sql[] = "SELECT CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`, CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`, `companies`.`companyvideo` AS `video`, `investor_info`.`accredited` AS `accredited`, COUNT(`blog_posts`.`post_id`) AS `post_count`, COUNT(`votes`.`uid`) AS `votes_sent`, COUNT(`votes2`.`company_id`) AS `votes_received`, COUNT(DISTINCT `employees`.`userid`) AS `joined_company` FROM `users` LEFT JOIN `blog_posts` ON `blog_posts`.`user_id` = `users`.`id` LEFT JOIN `votes` ON `votes`.`uid` = `users`.`id` LEFT JOIN `investor_info` ON `investor_info`.`uid` = `users`.`id` LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id` LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid` LEFT JOIN `votes` `votes2` ON `companies`.`companyid` = `votes2`.`company_id` WHERE `users`.`id` = '${uid}' "; $sql = "(\n" . implode("\n)\nUNION ALL\n(\n", $sql) . "\n)"; echo '<pre>', $sql, '</pre><br />';// die($sql); $result = mysql_query($sql) or die(mysql_error()); $row = mysql_fetch_assoc($result); return $row; } $profile_complete = 0; $company_video = 0; $accredited_investor = 0; $joined_company = 0; $info = get_score(2); print_array($info); if((!empty($info['cred_chars'])) && (!empty($info['spec_chars']))) { $profile_complete = 200; } if(!empty($info['video'])) { $company_video = 50; } if($info['accredited'] == 1) { $accredited_investor = 100; } $blog_posts = (50 * $info['post_count']); $votes_submitted = (5 * $info['votes_sent']); if($info['joined_company'] > 0) { $joined_company = 200; } $score = $profile_complete + $company_video + $accredited_investor + $blog_posts + $votes_submitted + $joined_company; echo $score; MYSQL Version: 5.1.49 Here is the Explain: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE users const PRIMARY PRIMARY 4 const 1 1 SIMPLE blog_posts ALL NULL NULL NULL NULL 2 1 SIMPLE votes ALL NULL NULL NULL NULL 6 1 SIMPLE investor_info eq_ref uid uid 4 const 1 1 SIMPLE employees ref unique unique 4 const 1 Using index 1 SIMPLE companies eq_ref PRIMARY PRIMARY 4 db.employees.companyid 1 1 SIMPLE votes2 ALL NULL NULL NULL NULL 6 Things I have attempted: A mysql union, but I don't think that makes sense because I'm not dealing with multiple rows. I really just need one row with a bunch of data. I would like to join a lot of SELECTS, but putting them together in one query is incredibly challenging. Please give some advice.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.