Bitcoin Forum
December 09, 2016, 01:47:19 PM *
News: Latest stable version of Bitcoin Core: 0.13.1  [Torrent].
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: MySQL Help Requested...  (Read 963 times)
SgtSpike
Legendary
*
Offline Offline

Activity: 1344



View Profile
May 12, 2011, 08:14:50 PM
 #1

I can't really figure out the best way to do this...

On my website, bitcoinfeedback, I have a few MySQL tables.  One of them is feedback, and one of them is users.

I'd like to easily be able to pull the percentage of positive vs negative vs neutral feedback a user has while generating a list of users, all with one query.

The only way I can think of to do this is to have three more fields in the users table that just keep an updated count of feedback any time someone leaves feedback on a person's profile.  It would run a query to count the three different types of feedback on a given users account, then update those three fields with the count numbers.  When pulling the users into a list, I could easily make whatever calculations I wanted to do with those three count numbers.

Is there a better way to do this?  Currently, feedback "type" is stored within a single varchar field within each feedback.  So the varchar field will either say "positive", "negative", or "neutral".  I can change that to numbers (1, 0, and -1), and say, only do a sum of it divided by the count, but I'd lose visibility of how many of each the person had.

Thoughts?  Other ways of accomplishing the same thing?
1481291239
Hero Member
*
Offline Offline

Posts: 1481291239

View Profile Personal Message (Offline)

Ignore
1481291239
Reply with quote  #2

1481291239
Report to moderator
1481291239
Hero Member
*
Offline Offline

Posts: 1481291239

View Profile Personal Message (Offline)

Ignore
1481291239
Reply with quote  #2

1481291239
Report to moderator
1481291239
Hero Member
*
Offline Offline

Posts: 1481291239

View Profile Personal Message (Offline)

Ignore
1481291239
Reply with quote  #2

1481291239
Report to moderator
"Apparently, so I am told, there exist "people" who prefer to wipe sitting down. From the front. Initial research indicates it could be up to half the population." -- benjamindees
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
theymos
Administrator
Legendary
*
Offline Offline

Activity: 2506


View Profile
May 13, 2011, 05:27:45 AM
 #2

With PostgreSQL you could do this:
Code:
SELECT users.name, count(CASE WHEN (feedback.type='positive') THEN 1 ELSE NULL END) AS positive, count(CASE WHEN (feedback.type='negative') THEN 1 ELSE NULL END) AS negative FROM users JOIN feedback ON (feedback.name=users.name) GROUP BY users.name;
I don't know if that would work on MySQL.

1NXYoJ5xU91Jp83XfVMHwwTUyZFK64BoAD
SgtSpike
Legendary
*
Offline Offline

Activity: 1344



View Profile
May 13, 2011, 05:30:47 AM
 #3

Thanks for the suggestion, I'll give it a shot!
trentzb
Sr. Member
****
Offline Offline

Activity: 406


View Profile
May 13, 2011, 05:39:24 AM
 #4

Post schema and sample data.
davout
Legendary
*
Offline Offline

Activity: 1358


1davout


View Profile WWW
May 13, 2011, 05:49:51 AM
 #5

Subqueries baby

Code:
SELECT
  users.name,
  (SELECT COUNT(*) FROM feedback f1 WHERE [it's positive] AND users.name = f1.username) AS positive,
  (SELECT COUNT(*) FROM feedback f2 WHERE [it's negative] AND users.name = f2.username) AS negative
FROM
  users

SgtSpike
Legendary
*
Offline Offline

Activity: 1344



View Profile
May 13, 2011, 05:57:28 AM
 #6

davout, yeah I ended up going the subquery route on suggestion from a friend.

I thought the CASE method might be more efficient (which it might), but I couldn't get it to work right.  It kept clumping up the data into a single person's username.  I'll revisit the issue later on if speed of the query becomes a problem.
Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!