Jump to content

unemployment

Members
 View Profile  See their activity

  • Posts

    746

  • Joined

  • Last visited

 Content Type 

Posts posted by unemployment

    Call Public Function in Another Public Function

    in PHP Coding Help

    Posted

    It's the critical variable in object oriented programming.  It defines the current class scope and allows you access to the current class's methods and variables (and any inherited ones).  I recommend reading that entire manual chapter that's linked above.

     

    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?

    Union Won't Merge SQL

    in MySQL Help

    Posted

    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.

    Call Public Function in Another Public Function

    in PHP Coding Help

    Posted

    It's the critical variable in object oriented programming.  It defines the current class scope and allows you access to the current class's methods and variables (and any inherited ones).  I recommend reading that entire manual chapter that's linked above.

     

    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.

    Call Public Function in Another Public Function

    in PHP Coding Help

    Posted

    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);
}

    Union Won't Merge SQL

    in MySQL Help

    Posted

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

    Union Won't Merge SQL

    in MySQL Help

    Posted

    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

    Union Won't Merge SQL

    in MySQL Help

    Posted

    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.

    Counts are Inaccurate

    in MySQL Help

    Posted

    So you're just trying to caculate each person's score.

     

    But is this query supposed to calculated EVERYONE's score, or just the one person's?

     

    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. 

    Counts are Inaccurate

    in MySQL Help

    Posted

    And what is that code trying to calculate?

     

    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.

    Counts are Inaccurate

    in MySQL Help

    Posted

    I don't understand what you're trying to query.

     

    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.

     

     

     

    Counts are Inaccurate

    in MySQL Help

    Posted

    What are you actually trying to do?  Why are you destroying the names of your columns, and why are you selecting 7 empty columns for no reason?

     

    Just from glancing at these queries, you should run them separately.

     

    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?

    Counts are Inaccurate

    in MySQL Help

    Posted

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

    Counts are Inaccurate

    in MySQL Help

    Posted

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

×
×
  • 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.