CODEWITHSUNDEEP
phpmysql
Rithu
Rithu

Reputation: 1289

Mysql orderBy count of field name from another table

This is the page URL http://domain.com/testphp?id=1&mview=4

The choices are displayed from the table named je_add_choice and it contains the field names

  1. choice_id
  2. poll_id
  3. choice_creator_id
  4. choice_name
  5. choice_image
  6. description
  7. Choicecreationtime

It displays the choices with their votes for the particular poll, what i want to do is, I have to display the choices orderby no of votes. But the problem is, The vote count for the choices are from another table named je_user_vote and it contains the fields

  1. user_id
  2. Poll_id
  3. choice_id
  4. vote_id (primary key)
  5. datetime_voted
  6. usertype

In the poll page i used the query 

select * from je_addchoice where poll_id='$poll_id'

In this query the $pollid variable is get from the url $_GET['id']

I want to rewrite the query as 

select * from je_addchoice where poll_id='$poll_id' // append the code order by count(fieldname) from je_user_vote table.

I already used 

SELECT *, (
            SELECT count(*) 
            FROM je_uservote T2 
            WHERE T2.pollid=T1.pollID 
            AND T2.choiceid=T1.choiceID) AS votes
FROM je_addchoice T1
ORDER BY votes

But it shows the blank page. Anybody can help me for solve this problem. Thanks in advance for reading this and help me to solve

Upvotes: 2

Views: 107

Answers (3)

Rithu
Rithu

Reputation: 1289

SELECT COUNT(t2.choice_id) AS total_votes, t1.* 
    FROM je_addchoice t1
    LEFT JOIN je_user_vote t2
        ON t1.choice_id=t2.choice_id
        AND t1.poll_id=t2.Poll_id
    WHERE t1.poll_id = " . $poll_id . "    
    GROUP BY t1.choice_id
    ORDER BY 1 DESC

This is the answer for my question. I just take coding from subred's answer and modify the names as required in the page and then i got results. Now is working fine

http://www.domain.com/projects/je/pollpage.php?id=2&mview=4

This is the URL in which the choice are now displayed in order of high count votes to low count votes

Upvotes: 1

Rithu Psks
Rithu Psks

Reputation: 92

SELECT COUNT(t2.choice_id) AS total_votes, t1.* 
    FROM je_addchoice t1
    LEFT JOIN je_user_vote t2
        ON t1.choice_id=t2.choice_id
        AND t1.poll_id=t2.Poll_id
    WHERE t1.poll_id = " . variable_name . "    
    GROUP BY t1.choice_id
    ORDER BY 1 DESC

Hope this helps.

Upvotes: 1

SubRed
SubRed

Reputation: 3187

All you need is left join and aggregate function to calculate the vote. Here query you can try (untested):

"SELECT COUNT(*) AS total_votes, t1.* 
FROM je_add_choice t1
LEFT JOIN je_user_vote t2
    ON t1.choice_id=t2.choice_id
    AND t1.poll_id=t2.Poll_id
WHERE t1.poll_id = " . $poll_id . "
GROUP BY t1.choice_id
ORDER BY 1 ASC"

Upvotes: 2

Related Questions