Geek Answers Handbook

Table binding in mysql

I need to know how to link two tables in php / mysql and then rank the results?
Here is my situation.

I have a story table:

storyid writerid title story submitdate

and vote table

 voteid userid storyid vote

I save the vote as 1 and vote as -1
I am looking for a way to join these two tables, and then rank / sort stories by the number of votes they received.
I am open to any ideas on how to do this or with another possible database schema.

+4
source share
1 answer

I prefer the names of my tables to be single. This is not a "Stories" table; This is a History table with several rows.

Voting can only be assigned to one story, so this is a one-to-many relationship between them. I would put the foreign key in the votes table and give him an indication of the history with which it is associated. Change your schema if you agree: remove voteid from the story table and make storyid in vote foreign key in the story table.

But with that said, perhaps you can try this query:

 select stories.storyid, sum(vote=-1) as down, sum(vote=1) as up from stories inner join votes on (stories.storyid = votes.storyid) group by stories.storyid

Fixed for each ypercube comment below.

+4
source

More articles:


All Articles