unemployment

Sounds a lot more complicated than I realized. It's not just grouping functions and calling them in a specified group?

No those are not the correct values. Those are sample values as to what my score actually gets calculated from.

I will. I just made my first OOP class and I would really like to learn more about it, because I feel that it is the next step for me as a coder. Just because your using classes does not mean your coding in OOP. It's a first step, but it's not the entire picture. Hmm Interesting. Care to explain?

Sample SQL code is attached... -- phpMyAdmin SQL Dump -- version 3.3.8.1 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: Oct 18, 2011 at 03:29 PM -- Server version: 5.1.49 -- PHP Version: 5.3.8 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `test_db` -- -- -------------------------------------------------------- -- -- Table structure for table `beta_keys` -- CREATE TABLE IF NOT EXISTS `beta_keys` ( `key_id` int(11) NOT NULL AUTO_INCREMENT, `key` varbinary(35) NOT NULL, `mail` varchar(50) NOT NULL, `userid` int(11) DEFAULT NULL, `active` tinyint(1) NOT NULL DEFAULT '1', `gen` int(1) NOT NULL DEFAULT '1', `ref_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`key_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=104 ; -- -- Dumping data for table `beta_keys` -- INSERT INTO `beta_keys` (`key_id`, `key`, `mail`, `userid`, `active`, `gen`, `ref_id`) VALUES (1, 'NZQVGRf3yTqhLpI80Udl1Xb9JMznj2oFmeD', 'jason@test.com', 1, 0, 1, 0), (2, 'WzJgQ3PDx10hyFwrl9biscnCf4SRkA7detK', 'davey@test.com', 4, 0, 1, 0), (3, 'lazDTbPYoQFRVELn3jHgys9xw1rdk8qOZWA', 'jacek@test.com', 13, 0, 1, 0), (4, 'KO0AVetF95LkfXDTcowS2ImspWiEjrRBqNQ', 'yellow44@test.com', 2, 0, 1, 0), (5, 'UEv3Rt2pbyksA7u1FSZ0JNfq5zmWGhKMxdH', 'johntllant@test.com', 11, 0, 1, 0); -- -------------------------------------------------------- -- -- Table structure for table `blog_posts` -- CREATE TABLE IF NOT EXISTS `blog_posts` ( `post_id` int( NOT NULL AUTO_INCREMENT, `user_id` int( NOT NULL, `posttitle` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `postdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `postimage` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `postcontent` text COLLATE utf8_unicode_ci NOT NULL, `posttags` varchar(90) COLLATE utf8_unicode_ci NOT NULL, `approved` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`post_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ; -- -- Dumping data for table `blog_posts` -- INSERT INTO `blog_posts` (`post_id`, `user_id`, `posttitle`, `postdate`, `postimage`, `postcontent`, `posttags`, `approved`) VALUES (1, 1, 'Welcome to the Beta!', '2011-09-19 09:09:16', '', 'Please let us know what you enjoy about our software and how we can improve.<br />\r\n', '', 1), (2, 1, 'New Page Released: Known Bugs', '2011-09-21 14:20:02', '', 'We hope you get more involved and vote for issues that you want fixed more promptly. Please continue to submit feedback as it is helping us make drastic feature enhancements.', '', 1); -- -------------------------------------------------------- -- -- Table structure for table `companies` -- CREATE TABLE IF NOT EXISTS `companies` ( `companyid` int(11) NOT NULL AUTO_INCREMENT, `companyname` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `companytag` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `companywebsite` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `companyvideo` varchar(14) NOT NULL, `companyvideotitle` varchar(128) NOT NULL, `phone` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `country` int(11) NOT NULL, `state` int(11) NOT NULL, `city` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `industry` int(11) NOT NULL, `stage` int(11) NOT NULL, `guestviews` int(11) NOT NULL DEFAULT '0', `eviews` int(11) NOT NULL DEFAULT '0', `iviews` int(11) NOT NULL DEFAULT '0', `capital` int(12) NOT NULL, `companytype` int(1) NOT NULL, `ckey` varbinary(35) NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`companyid`), UNIQUE KEY `companytag` (`companytag`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ; -- -- Dumping data for table `companies` -- INSERT INTO `companies` (`companyid`, `companyname`, `companytag`, `companywebsite`, `companyvideo`, `companyvideotitle`, `phone`, `country`, `state`, `city`, `industry`, `stage`, `guestviews`, `eviews`, `iviews`, `capital`, `companytype`, `ckey`, `date_created`) VALUES (1, 'test', 'testt', 'http://test.com', '', '', '860-555-5555', 231, 7, 'East', 7, 2, 84, 14, 13, 100000, 1, 'wSVgTlAntKB5NURbEoLyHxZfqazCF3h9rk8', '2011-09-19 09:02:51'), (4, 'Nicole test', 'entrepreneurista', 'http://www.test.com', 'JMmmUWloh9o', 'entrepreneurista: Legal Shield', '312-555-1010', 231, 10, 'Tampa', 5, 2, 150, 25, 0, 10000, 1, 'tFGnUP0kL7NHOAxypYXC4oh96lfuR8esa2r', '2011-09-22 00:14:09'), (3, 'UPtest', 'theUPstest', 'http://www.test.com', '', '', '646-555-5555', 231, 33, 'New York', 7, 7, 86, 145, 3, 400000, 1, 'FZ9rp3jbfnPKBw0CeWx1cVivSXQ7NIDtqlH', '2011-09-21 15:35:56'), (8, 'Omega Storehouse', 'Alphatest', 'http://www.test.com', '', '', '+1 508 555-1463', 231, 22, 'Middleborough', 16, 4, 43, 10, 1, 500000, 1, '7EyGtZqp1k6Vwu4fJAbca9KSgIr3hOHdNMv', '2011-09-24 18:56:09'), -- -------------------------------------------------------- -- -- Table structure for table `company_fans` -- CREATE TABLE IF NOT EXISTS `company_fans` ( `fan_id` int(11) NOT NULL AUTO_INCREMENT, `company_id` int( NOT NULL, `user_id` int( NOT NULL, `fan_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`fan_id`), UNIQUE KEY `unique` (`company_id`,`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12 ; -- -- Dumping data for table `company_fans` -- INSERT INTO `company_fans` (`fan_id`, `company_id`, `user_id`, `fan_date`) VALUES (9, 8, 1, '2011-09-24 23:12:16'), (2, 1, 5, '2011-09-19 10:24:36'), (3, 1, 6, '2011-09-19 10:32:28'), (4, 1, 2, '2011-09-19 17:10:38'), (5, 3, 5, '2011-09-21 15:46:09'), (6, 3, 1, '2011-09-21 15:49:39'), (7, 3, 10, '2011-09-21 15:50:29'), (8, 3, 4, '2011-09-21 16:06:29'), (10, 1, 17, '2011-09-25 11:08:58'), (11, 3, 17, '2011-09-25 16:22:44'); -- -------------------------------------------------------- -- -- Table structure for table `employees` -- CREATE TABLE IF NOT EXISTS `employees` ( `employeeid` int( NOT NULL AUTO_INCREMENT, `userid` int( NOT NULL, `companyid` int( NOT NULL, `admin` tinyint(1) NOT NULL, `function` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `date_employed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`employeeid`), UNIQUE KEY `unique` (`userid`,`companyid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16 ; -- -- Dumping data for table `employees` -- INSERT INTO `employees` (`employeeid`, `userid`, `companyid`, `admin`, `function`, `date_employed`) VALUES (1, 1, 1, 2, 'CEO & Co-Founder', '2011-09-19 09:02:51'), (13, 2, 10, 2, 'Founder', '2011-09-28 05:01:26'), (3, 4, 1, 2, 'Developer & Co-Founder', '2011-09-19 14:24:49'), (7, 15, 4, 2, 'Owner/Founder', '2011-09-22 00:14:09'); -- -------------------------------------------------------- -- -- Table structure for table `investor_info` -- CREATE TABLE IF NOT EXISTS `investor_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `investor_type` int(11) NOT NULL DEFAULT '0', `uid` int(11) NOT NULL, `capital_available` bigint(30) NOT NULL, `accredited` int(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uid` (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ; -- -- Dumping data for table `investor_info` -- INSERT INTO `investor_info` (`id`, `investor_type`, `uid`, `capital_available`, `accredited`) VALUES (1, 0, 3, 0, 1), (2, 0, 6, 100, 1), (3, 0, 7, 0, 1), (4, 0, 16, 0, 2), (5, 0, 17, 3750, 2), (6, 0, 21, 0, 1), (7, 0, 22, 0, 2), (8, 0, 29, 0, 3), (9, 0, 39, 0, 1); -- -------------------------------------------------------- -- -- Table structure for table `partners` -- CREATE TABLE IF NOT EXISTS `partners` ( `partner_id` int( NOT NULL AUTO_INCREMENT, `user_id` int( NOT NULL, `friend_id` int( NOT NULL, `approved` tinyint(1) NOT NULL DEFAULT '0', `approved_date` datetime NOT NULL, `message` text COLLATE utf8_unicode_ci, PRIMARY KEY (`partner_id`), KEY `user_id` (`user_id`,`friend_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=60 ; -- -- Dumping data for table `partners` -- INSERT INTO `partners` (`partner_id`, `user_id`, `friend_id`, `approved`, `approved_date`, `message`) VALUES (2, 1, 3, 0, '0000-00-00 00:00:00', ''), (30, 1, 19, 1, '2011-09-24 21:13:39', ''), (3, 1, 5, 1, '2011-09-19 14:57:45', ''), (4, 4, 5, 1, '2011-09-19 16:01:58', ''), (5, 5, 6, 1, '2011-09-20 12:42:33', 'Hey I know you ... '), (6, 2, 6, 1, '2011-09-20 12:42:35', ''), (7, 1, 8, 0, '0000-00-00 00:00:00', ''), (8, 1, 6, 1, '2011-09-20 12:42:35', ''), (9, 10, 5, 1, '2011-09-20 09:58:48', ''), (10, 1, 2, 1, '2011-09-20 13:51:52', ''), (11, 1, 10, 1, '2011-09-20 12:05:03', ''), (12, 1, 9, 1, '2011-09-20 21:05:29', ''), (13, 1, 11, 1, '2011-09-20 13:54:44', ''), (14, 1, 12, 0, '0000-00-00 00:00:00', ''); -- -------------------------------------------------------- -- -- Table structure for table `recommendations` -- CREATE TABLE IF NOT EXISTS `recommendations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `rec_id` int(11) NOT NULL, `recommendation` text COLLATE utf8_unicode_ci NOT NULL, `approved` int(11) NOT NULL, `approved_date` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ; -- -- Dumping data for table `recommendations` -- INSERT INTO `recommendations` (`id`, `user_id`, `rec_id`, `recommendation`, `approved`, `approved_date`) VALUES (2, 1, 3, 't2 yo', 0, '0000-00-00 00:00:00'), (6, 2, 1, 'another test', 2, '0000-00-00 00:00:00'), (7, 1, 4, 'Nice guy. Cant explain code but hes cool ^^', 2, '2011-10-14 11:15:03'), (8, 4, 1, 'testing', 2, '2011-10-18 13:22:04'), (10, 2, 1, 'test', 0, '0000-00-00 00:00:00'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXISTS `users` ( `id` int( NOT NULL AUTO_INCREMENT, `firstname` varchar(35) COLLATE utf8_unicode_ci NOT NULL, `lastname` varchar(35) COLLATE utf8_unicode_ci NOT NULL, `username` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(35) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `dateofbirth` date NOT NULL, `gender` tinyint(1) NOT NULL, `accounttype` tinyint(1) NOT NULL, `signupdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `personalweb` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `country` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `state` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `city` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `phonenumber` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `code` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `guestviews` int(11) NOT NULL DEFAULT '0', `eviews` int(11) NOT NULL DEFAULT '0', `iviews` int(11) NOT NULL DEFAULT '0', `credentials` mediumtext COLLATE utf8_unicode_ci, `specialties` mediumtext COLLATE utf8_unicode_ci, `mail_status` int(1) NOT NULL DEFAULT '1', `status` tinyint(1) NOT NULL DEFAULT '2', `deleted_time` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `firstname` (`firstname`), KEY `lastname` (`lastname`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=41 ; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `email`, `password`, `dateofbirth`, `gender`, `accounttype`, `signupdate`, `personalweb`, `country`, `state`, `city`, `phonenumber`, `code`, `guestviews`, `eviews`, `iviews`, `credentials`, `specialties`, `mail_status`, `status`, `deleted_time`) VALUES (1, 'Jason', 'test', 'jasontest', 'jason@test.com', 'd0d170def09345353d23cac6fdfd551', '1987-04-27', 1, 1, '2011-09-19 08:51:31', 'http://test.com', '231', '7', 'hartford', '860-555-5555', '67265973', 90, 97, 0, 'test', 0, 3, NULL), (39, 'Rick', 'snitch', 'ricksnitch', 'ricksnitch@ben.edu', '85bd1462f0646535353adbc09428042', '1960-01-01', 1, 0, '2011-10-12 17:31:23', '', '231', '22', 'Place', '9785005537', '19781606', 2, 4, 0, NULL, NULL, 1, 3, NULL), (2, 'sent', 'bird', 'lovebug', 'test@aol.com', '1e21afeba935353ae1c97c88418', '1988-11-02', 0, 1, '2011-09-19 09:05:59', '', '231', '7', 'test', '860-555-5591', '25971497', 47, 74, 0, 'going to be a nurse soon!', 'not computer stuff', 1, 3, NULL), (3, 'Woody', 'bens', 'ben', 'sgs@sg.com', 'b5ab56bd8b5f53534e9870688', '1958-10-22', 1, 0, '2011-09-19 09:14:14', '', '231', '22', 'test', '7813555000', '97424199', 16, 13, 0, NULL, NULL, 1, 3, NULL), (4, 'Davey', 'test', 'test', 'davey@test.com', 'ace0f9fbbe83535d90763fa02', '1992-06-09', 1, 1, '2011-09-19 09:14:30', '', '152', '', 'test', '0035555180728', '23161251', 44, 157, 0, '', 'Webdevelopment: PHP, AJAX, xHTML, JavaScript and CSS. Learning Java and XML.', 1, 3, NULL); -- -------------------------------------------------------- -- -- Table structure for table `votes` -- CREATE TABLE IF NOT EXISTS `votes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `ip_address` text CHARACTER SET utf8 NOT NULL, `company_id` int(11) NOT NULL, `vote_type` int(11) NOT NULL, `time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=13 ; -- -- Dumping data for table `votes` -- INSERT INTO `votes` (`id`, `uid`, `ip_address`, `company_id`, `vote_type`, `time`) VALUES (1, 6, '67.86.141.160', 1, 1, '2011-09-20 12:41:23'), (4, 0, '12.196.12.162', 3, 0, '2011-09-21 15:50:07'), (9, 0, '208.66.27.18', 3, 0, '2011-09-23 20:06:52'), (10, 17, '66.212.204.160', 1, 0, '2011-09-25 11:09:06'), (11, 17, '66.212.204.160', 3, 1, '2011-09-25 16:22:53'), (12, 0, '12.196.12.162', 11, 0, '2011-09-29 08:11:46'); The results I want will return an array like this... Array ( [cred_chars] => 1143 [spec_chars] => 34 => [accredited] => [post_count] => 2 [vote_sent_count] => [inv_vote_received_count] => 1 [pub_vote_received_count] => 1 [joined_count] => 1 [ent_count] => [inv_count] => [company_fans_count] => 4 [recommendation_count] => 2 [associate_count] => 17 ) That data eventually will be used to calculate scores. So far the correct data is being pull but the sql is not optimized for maximum performance and I could use some help with understanding how to improve this.

I will. I just made my first OOP class and I would really like to learn more about it, because I feel that it is the next step for me as a coder.

I think I need to use $this, but I need to be able to add the two values returned from both of my functions. How would I do that? public function get_users_edge($uid) { $users_primary->get_users_primary_edge($uid); $users_dynamic->get_users_dynamic_edge($uid); echo $users_primary + $users_dynamic; }

That works thanks. What is the point of $this->?

How can I call a public function inside of another public function? The get_primary_edge() says it's undefined, but it's just another public function in the class so shouldn't this work? public function get_dynamic_edge($uid) { $uid = (int)$uid; $sql = "( SELECT `users`.`id` FROM partners INNER JOIN `users` ON `partners`.`user_id` = `users`.`id` WHERE partners.friend_id = '${uid}' AND `approved` = 1 ) UNION ALL ( SELECT `users`.`id` FROM `partners` INNER JOIN `users` ON `partners`.`friend_id` = `users`.`id` WHERE `partners`.`user_id` = '${uid}' AND `approved` = 1 )"; $result = mysql_query($sql) or die(mysql_error()); $i = 0; while (($row = mysql_fetch_assoc($result)) !== false) { $dynamic[$i] = array( 'uid' => $row['id'], 'score' => get_primary_edge($row['id']), ); $i++; } print_array($dynamic); }

Can this be done off the board? I don't want to expose my entire database to the phpfreaks community.

Those two were only a sample... Yesterday I was able to almost fully build it out. Here is what I currently have and would appreciate it if you could help me optimize it. $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`, `blog_post`.`post_count`, `vote_sent`.`vote_sent_count`, `inv_vote_received`.`inv_vote_received_count`, `pub_vote_received`.`pub_vote_received_count`, `joined_comps`.`joined_count`, `ent_ref`.`ent_count`, `inv_ref`.`inv_count`, `company_fans`.`company_fans_count`, `recommendations`.`recommendation_count`, `associates`.`associate_count` FROM `users` 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 ( SELECT `user_id`, COUNT(`post_id`) AS `post_count` FROM `blog_posts` ) AS `blog_post` ON `blog_post`.`user_id` = `users`.`id` LEFT JOIN ( SELECT `uid`, COUNT(`uid`) AS `vote_sent_count` FROM `votes` ) AS `vote_sent` ON `vote_sent`.`uid` = `users`.`id` LEFT JOIN ( SELECT `employees`.`userid`, `votes`.`company_id`, COUNT(`votes`.`company_id`) AS `inv_vote_received_count` FROM `votes` LEFT JOIN `companies` ON `votes`.`company_id` = `companies`.`companyid` LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid` WHERE `employees`.`userid` = '${uid}' AND `votes`.`vote_type` = 1 ) AS `inv_vote_received` ON `inv_vote_received`.`userid` = `users`.`id` LEFT JOIN ( SELECT `employees`.`userid`, `votes`.`company_id`, COUNT(`votes`.`company_id`) AS `pub_vote_received_count` FROM `votes` LEFT JOIN `companies` ON `votes`.`company_id` = `companies`.`companyid` LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid` WHERE `employees`.`userid` = '${uid}' AND `votes`.`vote_type` = 0 ) AS `pub_vote_received` ON `pub_vote_received`.`userid` = `users`.`id` LEFT JOIN ( SELECT `userid`, COUNT(`userid`) AS `joined_count` FROM `employees` WHERE `userid` = '${uid}' ) AS `joined_comps` ON `joined_comps`.`userid` = `users`.`id` LEFT JOIN ( SELECT `beta_keys`.`ref_id`, COUNT(`beta_keys`.`ref_id`) AS `ent_count` FROM `beta_keys` LEFT JOIN `users` ON `users`.`id` = `beta_keys`.`userid` WHERE `beta_keys`.`ref_id` = '${uid}' AND `users`.`accounttype` = 1 AND `users`.`status` = 3 ) AS `ent_ref` ON `ent_ref`.`ref_id` = `users`.`id` LEFT JOIN ( SELECT `beta_keys`.`ref_id`, COUNT(`beta_keys`.`ref_id`) AS `inv_count` FROM `beta_keys` LEFT JOIN `users` ON `users`.`id` = `beta_keys`.`userid` WHERE `beta_keys`.`ref_id` = '${uid}' AND `users`.`accounttype` = 0 AND `users`.`status` = 3 ) AS `inv_ref` ON `inv_ref`.`ref_id` = `users`.`id` LEFT JOIN ( SELECT `employees`.`userid`, COUNT(`company_id`) AS `company_fans_count` FROM `company_fans` LEFT JOIN `companies` ON `company_fans`.`company_id` = `companies`.`companyid` LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid` WHERE `userid` = '${uid}' ) AS `company_fans` ON `company_fans`.`userid` = `users`.`id` LEFT JOIN ( SELECT `user_id`, COUNT(`user_id`) AS `recommendation_count` FROM `recommendations` WHERE `user_id` = '${uid}' ) AS `recommendations` ON `recommendations`.`user_id` = `users`.`id` LEFT JOIN ( SELECT `user_id`, COUNT(`partner_id`) AS `associate_count` FROM `partners` WHERE (`user_id` = '${uid}' OR `friend_id` = '${uid}') AND `approved` = 1 ) AS `associates` ON `associates`.`user_id` = `users`.`id` WHERE `users`.`id` = '${uid}' ";

Yes he is... I've actually got something working now using a lot of sub-queries but isn't that inefficient?

I'm not sure what you mean. ( SELECT CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`, CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`, '' AS `video` FROM `users` WHERE `users`.`id` = '15' GROUP BY `cred_chars`, `spec_chars`, `video` ) JOIN ( SELECT '' AS `cred_chars`, '' AS `spec_chars`, `companies`.`companyvideo` AS `video` FROM `users` LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id` LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid` WHERE `users`.`id` = '15' GROUP BY `cred_chars`, `spec_chars`, `video` ) That doesn't work

That's what I was afraid of.... Thanks

I have... ( SELECT CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`, CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`, '' AS `video` FROM `users` WHERE `users`.`id` = '15' GROUP BY `cred_chars`, `spec_chars`, `video` ) UNION ALL ( SELECT '' AS `cred_chars`, '' AS `spec_chars`, `companies`.`companyvideo` AS `video` FROM `users` LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id` LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid` WHERE `users`.`id` = '15' GROUP BY `cred_chars`, `spec_chars`, `video` ) Together they produce... Array ( [cred_chars] => 161 [spec_chars] => 59 => ) Individually they produce... Array 1 cred_chars spec_chars video 161 59 Array 2 cred_chars spec_chars video JMmmUWloh9o Why aren't these merging? I want all the data in one row. How can I achieve this result.

Wow... never knew that. What is the benefit of doing the indexes for the ON condition?

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?

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.

No need for the union really it was just old code. This score will be calculated every time you visit a users profile.

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.

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.

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?

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}'";

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}'

Maybe the question is better asked this way... Can you set a cookie in PHP in response to an AJAX request?

Can you send a cookie through ajax? Will it still be read by the browser?