mysql query and how to join

[Aero77]

I have two tables, where I need data from both, but I also need to count or sum the votes in one of the tables and make a variable like totalVotes. 

 

This is the current sql query I got which works, but it don't count

SELECT * FROM wp_links INNER JOIN wp_toplist ON wp_links.link_id=wp_toplist.link_id

Hope someone can help. Thanks for reading

Edited February 28, 2015 by Aero77

[Barand]

Use SQL function SUM().

Can't tell you much more from what you've given us.

[Aero77]

nevermind I figured it out.

SELECT *, count(wp_toplist.link_id) AS totalVotes  FROM wp_toplist INNER JOIN wp_links ON wp_toplist.link_id=wp_links.link_id GROUP BY wp_toplist.link_id ORDER BY totalVotes DESC

is that okay you think or is there a better way ?

[Barand]

The query syntax is correct but I don't believe that is what you want.

 

COUNT(link_id) with GROUP BY link_id will give every count total = 1. But as I said, there is no way of my knowing exactly what you are trying to achieve.

[Aero77]

Im making a wordpress toplist plugin. Each vote is a new entry in the database table called wp_toplist where I store the IP address for 12 hours or so. So to find out how many votes each site has on the list I count the records and group them by link id

 

From what I can see it seems to be working cause they sites are listed one by one with their total number of votes, unless I'm missing something

Edited February 28, 2015 by Aero77

[Barand]

In that case you need

... GROUP BY site_id

[Aero77]

I tried that now, but it returns the same results 

[Barand]

What are you table structures. SELECT * obscures everything as well as being inefficient

[Aero77]

id	        mediumint(9)
link_id	        text	 
visitor_ip	varchar(15)
click_at	date
site_votes	text
on_page	        varchar(50)

link_id	        bigint(20)	No 	 	 
link_url	varchar(255)	No 	 	 
link_name	varchar(255)	No 	 	 
link_image	varchar(255)	No 	 	 
link_target	varchar(25)	No 	 	 
link_description	varchar(255)	No 	 	 
link_visible	varchar(20)	No 	Y 	 
link_owner	bigint(20)	No 	1 	 
link_rating	int(11)	No 	0 	 
link_updated	datetime	No 	0000-00-00 00:00:00 	 
link_rel	varchar(255)	No 	 	 
link_notes	mediumtext	No 	 	 
link_rss	varchar(255)

First one is wp_toplist and 2nd is wp_links

Edited February 28, 2015 by Aero77

[Barand]

Which table is which in your query?

Which field identifies a site? Should you be grouping by url, perhaps?

If you are joining on link_id, why is it text type in one table and int in the other?

[Aero77]

one table is default from wordpress, the other one I made myself. Didnt pay much attention to what kind of tables there were as long as I got my data in it. First one is the wp_toplist and the second is wp_links (from wordpress)