Order by votes - PHP

Question

I have a voting script which pulls out the number of votes per user.

Everything is working, except I need to now display the number of votes per user in order of number of votes. Please see my database structure:

Entries:

UserID, FirstName, LastName, EmailAddress, TelephoneNumber, Image, Status

Voting:

item, vote, nvotes

The item field contains vt_img and then the UserID, so for example: vt_img4 and both vote & nvotes display the number of votes.

Any ideas how I can relate those together and display the users in order of the most voted at the top?

Thanks

Both Matteo Tassinari and fluffeh have good answers, I'd agree with fluffeh, you shoul re-work your db structure however if thats not possible then Matteo solution should work. — Dpolehonski, Sep 14 '12 at 08:23
I built my own solutions based on OP's actual database structure, but I really think he might want to restructure his database. Someone downvoted my answer but also left no explanation of the reason... — Matteo Tassinari, Sep 14 '12 at 08:25

score 2 · Answer 1 · edited Mar 20 '17 at 10:29

2

You really need to change the structure of the voting table so that you can do a normal join. I would strongly suggest adding either a pure userID column, or at the very least not making it a concat of two other columns. Based on an ID you could then easily do something like this:

select
    a.userID,
    a.firstName,
    b.votes
from
    entries a
        join voting b
            on a.userID=b.userID
order by
    b.votes desc

The other option is to consider (if it is a one to one relationship) simply merging the data into one table which would make it even easier again.

At the moment, this really is an XY problem, you are looking for a way to join two tables that aren't meant to be joined. While there are (horrible, ghastly, terrible) ways of doing it, I think the best solution is to do a little extra work and alter your database (we can certainly help with that so you don't lose any data) and then you will be able to both do what you want right now (easily) and all those other things you will want to do in the future (that you don't know about right now) will be oh so much easier.

Edit: It seems like this is a great opportunity to use a Trigger to insert the new row for you. A MySQL trigger is an action that the database will make when a certain predefined action takes place. In this case, you want to insert a new row into a table when you insert a row into your main table. The beauty is that you can use a reference to the data in the original table to do it:

CREATE TRIGGER Entries_Trigger AFTER insert ON Entries
    FOR EACH ROW BEGIN
        insert into Voting values(new.UserID,0,0);
    END;

This will work in the following manner - When a row is inserted into your Entries table, the database will insert the row (creating the auto_increment ID and the like) then instantly call this trigger, which will then use that newly created UserID to insert into the second table (along with some zeroes for votes and nvotes).

edited Mar 20 '17 at 10:29

Community

1
1

answered Sep 14 '12 at 08:19

Fluffeh

33,228
16
67
80

Thanks for getting back to me, I have added that query with no luck! I have added a EntryID within the votes table (as INT 11) and I have manually put in the ID's associated for the current ones! But this isnt showing, any ideas? Also, I have forms which are entering the data, which I assume I will need to amend, correct? – craigclicky Sep 14 '12 at 08:45
Nevermind, I had a slight change within the query which was different. This is now working. I will now post my insert code, to see if that needs to change to update the voting table also. – craigclicky Sep 14 '12 at 08:48
@craigclicky Before I finish looking through it, go away and change your database password. NOW. You just posted your usename/password publically along with the IP where your database is. – Fluffeh Sep 14 '12 at 08:55
@craigclicky Okay, had a look at the code, if you modified only the votes table to contain a userID column, you won't need to change anything there - it doesn't interact with the Votes table at all. You probably will need to have a look at the code that inserts records into the votes table/updates them. – Fluffeh Sep 14 '12 at 08:58
Hi Fluffeh, Thanks for the heads up on the passwords, I forgot that was include.. oops! Those have now been changed. – craigclicky Sep 14 '12 at 09:06
The way everything works, so that when you upload an entry it uses this query: http://pastebin.com/daP6KGBC Then once approved they aren't added to the website using the following query: http://pastebin.com/LMNqLHaM I need it during that process to add the EntryID and the item name vt_img{ID GOES HERE} and set votes to zero! Any ideas? – craigclicky Sep 14 '12 at 09:17
@craigclicky See the Edit section of my answer (the bottom half of it where I said EDIT:) which addresses the second half of the answer :) – Fluffeh Sep 14 '12 at 13:19
Thanks, is this a seperate query? or just one? – craigclicky Sep 15 '12 at 09:05
@craigclicky A trigger is something you create once, then it sits there working away until you drop it :) – Fluffeh Sep 15 '12 at 10:16

score 0 · Answer 2 · answered Sep 14 '12 at 08:18

Your database is badly designed. It should be:

Voting:

item, user_id, vote, nvotes

Placing the item id and the user id into the same column as a concatenated string with a delimiter is just asking for trouble. This isn't scalable at all. Look up the basics on Normalization.

score 0 · Answer 3 · answered Sep 14 '12 at 08:18

You could try this:

SELECT *
FROM Entries e
JOIN Voting v ON (CONCAT('vt_img', e.UserID) = v.item)
ORDER BY nvotes DESC

but please notice that this query might be quite slow due to the fact that the join field for Entries table is built at query time.

You should consider changing your database structure so that Voting contains a UserID field in order to do a direct join.

score 0 · Answer 4 · answered Sep 14 '12 at 08:28

I'm figuring the Entries table is where votes are cast (you're database schema doesn't make much sense to me, seems like you could work it a little better). If the votes are actually on the Votes table and that's connected to a user, then you should have UserID field in that table too. Either way the example will help.

Lets say you add UserID to the Votes table and this is where a user's votes are stored than this would be your query

SELECT Users.id, Votes.*,
SUM(Votes.nvotes) AS user_votes
FROM Users, Votes
WHERE Users.id = Votes.UserID
GROUP BY Votes.UserID
ORDER BY user_votes

score -1 · Answer 5 · answered Sep 14 '12 at 08:17

-1

USE ORDER BY in your query --

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

answered Sep 14 '12 at 08:17

swapnesh

26,318
22
94
126

This doesn't help much when the data isn't matching in the tables - which is the crux of the matter here. – Fluffeh Sep 14 '12 at 08:20

Order by votes - PHP

5 Answers5