Union Won't Merge SQL

unemployment · October 17, 2011

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.

fenway · October 17, 2011

One row means JOIN, not UNION.

unemployment · October 17, 2011

One row means JOIN, not UNION.

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

Thanks

fenway · October 17, 2011

Afraid how? If you know for certain that each of the sub-selects will only return a single row, then just switch the word UNION to "JOIN".

unemployment · October 17, 2011

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

fenway · October 17, 2011

You'll need to start a query with SELECT; and then alias those tables.

polycap9 · October 18, 2011

You are appreciated fenway

unemployment · October 18, 2011

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

fenway · October 18, 2011

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

What do you mean "a lot"? I see 2.

unemployment · October 18, 2011

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

fenway · October 18, 2011

Wow, where to begin? There are at least half a dozen things that aren't optimized.

For starters, when you're counting, you're not using GROUP BY, so I don't see how that could be working.

Second, you can could all the different possibilities from each table (e.g. for acccounttype/status and votetype) in a single sub-query.

If you actually want my help, I'll need SQL queries to create & populate these tables, along with the expected output, and current output.

unemployment · October 18, 2011

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

fenway · October 18, 2011

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

Well, the CREATE TABLE stuff isn't secret, and doesn't help anyone else.

And I don't need all of your data, just enough for a sample.

Again, someone else might want to help -- or, better yet, learn from the example by playing around.

Besides, I don't open attachments from online forums.

You're call.

unemployment · October 18, 2011

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.

fenway · October 19, 2011

So, to be clear, those are the correct values for the sample data you provided?

unemployment · October 19, 2011

So, to be clear, those are the correct values for the sample data you provided?

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

fenway · October 19, 2011

Well, if I change the query, how I am supposed to verify that I haven't screwed up the query? You'll need to run the existing query for the sample data, and tell me what I should expect to get -- then I can play with it confidently.

unemployment · October 19, 2011

I've actually decided to set this up differently. All these joins are probably going to lead to locked tables and I will just use an actions table to simplify this whole process.

fenway · October 19, 2011

So you've asked for my help, and I've investing time into getting to the root cause, and you give up?

You wouldn't have anywhere near this many joins -- maybe 2 or 3, maximum.

Instead, you're opting for a flat-flat DB design -- all because you don't want to wait.

And by "wait", I mean give me enough information to proceed.

Remember, you PM'ed me to take an interest -- are you saying that I've wasted all of my time?

unemployment · October 19, 2011

No no no... I honestly didn't realize this could be done efficiently. If you think you can help, then I'll proceed with the process of getting you the information you need. Give me a minute.

fenway · October 19, 2011

Everything can always be done more efficiently -- to a point -- but I didn't see anything wrong with your approach on first glance.

It's true that a summary table that you generate "periodically" with leave you with a proper DB and give you the speed of no joins -- but you can make that choice later.

I'm working towards a Friday deadline, but I should have time over the weekend to play with it.

unemployment · October 19, 2011

Here is the data you should get from the sql query I gave you... ASSUMING $uid = 1

Array

(

[cred_chars] => 4

[spec_chars] => 4

=>

[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] => 7

)

Here is the updated and working SQL

-- 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', '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');

However built on top of this is another method for adding points to your score.

Here is the other function...

public function get_users_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;
	$score 	= 0;

	while (($row = mysql_fetch_assoc($result)) !== false)
	{
		$dynamic_scores[$i] = array(
			'uid' 	=> $row['id'],
			'score' => $this->get_users_primary_edge($row['id']),
		);

		$i++;
	}

	foreach($dynamic_scores as $k => $dynamic_score)
	{
		$score += $dynamic_score['score'];
	}

	return ($score * 0.1);
}

fenway · October 20, 2011

I just tested your query in 5.1.41-community, and I get this error: "column ref_id cannot be null".

Are you sure that query is the one you're running, or that those tables are correct?

unemployment · October 20, 2011

I just tested your query in 5.1.41-community, and I get this error: "column ref_id cannot be null".

Are you sure that query is the one you're running, or that those tables are correct?

It should be.. I even tested it out. Here is exactly what I tested. The Create SQL that I had two posts above is the SQL I used.


<?php

mysql_connect('127.0.0.1', '', '') or die(mysql_error());
mysql_select_db('test') or die(mysql_error());

class edge
{
public function get_users_edge($uid)
{
	$users_primary_edge = $this->get_users_primary_edge($uid);
	$users_dynamic_edge = $this->get_users_dynamic_edge($uid);

	return $users_primary_edge + $users_dynamic_edge;
}

public function get_users_primary_edge($uid)
{
	$uid = (int)$uid;

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

	$result = mysql_query($sql) or die(mysql_error());
	$row = mysql_fetch_assoc($result);

	print_array($row);
	$score = 0;

	if((!empty($row['cred_chars'])) && (!empty($row['spec_chars'])))
	{
		$score += 200;
	}

	if(!empty($row['video']))
	{
		$score += 50;
	}

	if($row['accredited'] == 1)
	{
		$score += 100;
	}

	$score += (50 * $row['post_count']);
	$score += (5 * $row['vote_sent_count']);
	$score += (3 * $row['inv_vote_received_count']);
	$score += (1 * $row['pub_vote_received_count']);
	$score += (200 * $row['joined_count']);
	$score += (40 * $row['ent_count']);
	$score += (200 * $row['inv_count']);
	$score += (10 * $row['company_fans_count']);
	$score += (30 * $row['recommendation_count']);
	$score += (20 * $row['associate_count']);

	return $score;
}

public function get_users_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;
	$score 	= 0;

	while (($row = mysql_fetch_assoc($result)) !== false)
	{
		$dynamic_scores[$i] = array(
			'uid' 	=> $row['id'],
			'score' => $this->get_users_primary_edge($row['id']),
		);

		$i++;
	}

	foreach($dynamic_scores as $k => $dynamic_score)
	{
		$score += $dynamic_score['score'];
	}

	return ($score * 0.1);
}
}

$edge = new edge;

print_r($edge->get_users_primary_edge(1)); 

function print_array($array)
{
echo '<pre>', ((is_array($array)) ?  print_r($array, true): $array), '</pre>';
}

?>

fenway · October 20, 2011

OK, I'll play with it over the next few days.

Sign In

Union Won't Merge SQL

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information