Jump to content

Counts are Inaccurate


unemployment
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

So that JOINs are faster.  If the columns being used in a join are indexed, the rows are matched up faster because the database doesn't have to build a mini-index manually to support the join (in a manner of speaking)
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

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