Jump to content


  • Posts

  • Joined

  • Last visited

Everything posted by unemployment

  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.
  16. I'm in the process of building a gaming engine. Essentially people will be able to earn points in my app for accomplishing certain tasks. The goal of this query is to pull in a bunch of data and have php create an algorithm that provides each user with a score. Ultimately, this will end up being a half dynamic and half static point system. The queries above are actually unioned using... $sql = "(\n" . implode("\n)\nUNION ALL\n(\n", $sql) . "\n)"; But I don't know if that's the best way to do this. I'm going to need to pull in a lot more data into that query to get all the point information I'm looking for. Maybe I need to structure this differently?
  17. Thanks ManiaDan! That fixed the first issue... now I have a new one. I would like to combine these two sql selects. I was using a union, but that doesn't really seem to merge the data sets. Do I need to use a subquery or do I need change the way I am joining things? I'm not sure how to handle this. There will likely be more data added to this query too. $sql[] = " SELECT `users`.`id` AS '0', CHAR_LENGTH(`users`.`credentials`) AS '1', CHAR_LENGTH(`users`.`specialties`) AS '2', `companies`.`companyvideo` AS '3', `investor_info`.`accredited` AS '4', COUNT(DISTINCT `blog_posts`.`post_id`) AS '5', COUNT(DISTINCT `votes`.`uid`) AS '6', COUNT(DISTINCT `employees`.`userid`) AS '7' '' AS '8', 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` WHERE `users`.`id` = '${uid}'"; $sql[] = "SELECT '' AS '0', '' AS '1', '' AS '2', '' AS '3', '' AS '4', '' AS '5', '' AS '6', '' AS '7', COUNT(`votes`.`company_id`) AS '8' FROM `employees` LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid` LEFT JOIN `votes` ON `votes`.`company_id` = `companies`.`companyid` WHERE `employees`.`userid` = '${uid}'";
  18. If I remove the blog post join and count then the employees count works correctly. If I don't the count is inaccurate. How can I have both counts work together? SELECT `users`.`id` AS '0', CHAR_LENGTH(`users`.`credentials`) AS '1', CHAR_LENGTH(`users`.`specialties`) AS '2', `companies`.`companyvideo` AS '3', `investor_info`.`accredited` AS '4', COUNT(`blog_posts`.`post_id`) AS '5', COUNT(`votes`.`uid`) AS '6', COUNT(`employees`.`userid`) AS '7', '' AS '8' 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` WHERE `users`.`id` = '${uid}'
  19. Can I receive any more help on this? I'd really rather not create another row in mysql.
  20. Thank you for the explanation and yes, strings solved my problem.
  21. Turning the text based words into a string is no big deal, but how would I turn this into a string? if (($info[0] == 'companywebsite') || ($info[0] == 'personalwebsite')) { ?> website to <a href="<?php $info[1]; ?>"><?php echo substr($info[1], 7);?></a> <?php } By the way, putting rtrim() around the echo didn't work for me.
  22. I would do that but it puts a space between the last word and the period like this . Which is pretty annoying. Any ideas on how to remove the space that an echo adds in?
  23. indeed, but this way you can alter the output.... which is what you wanted (a string with a dot at the end) I can't see that working with what I have. If it does please teach me.
  24. Even if I assign it to a string it will eventually be echoed anyway.
  25. The sentence would say something like.. you have updated you specialties, credentials and stage of development. I need to add in the period at the end. Keep in mind that the sentence could also say something like. The sentence would say something like.. you have updated you specialties and credentials.
  • 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.