Aero77 Posted February 28, 2015 Share Posted February 28, 2015 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 Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/ Share on other sites More sharing options...
Barand Posted February 28, 2015 Share Posted February 28, 2015 Use SQL function SUM(). Can't tell you much more from what you've given us. Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507065 Share on other sites More sharing options...
Aero77 Posted February 28, 2015 Author Share Posted February 28, 2015 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 ? Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507073 Share on other sites More sharing options...
Barand Posted February 28, 2015 Share Posted February 28, 2015 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. Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507075 Share on other sites More sharing options...
Aero77 Posted February 28, 2015 Author Share Posted February 28, 2015 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 Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507089 Share on other sites More sharing options...
Barand Posted February 28, 2015 Share Posted February 28, 2015 In that case you need ... GROUP BY site_id Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507090 Share on other sites More sharing options...
Aero77 Posted February 28, 2015 Author Share Posted February 28, 2015 I tried that now, but it returns the same results Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507092 Share on other sites More sharing options...
Barand Posted February 28, 2015 Share Posted February 28, 2015 What are you table structures. SELECT * obscures everything as well as being inefficient Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507093 Share on other sites More sharing options...
Aero77 Posted February 28, 2015 Author Share Posted February 28, 2015 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 Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507094 Share on other sites More sharing options...
Barand Posted February 28, 2015 Share Posted February 28, 2015 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? Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507096 Share on other sites More sharing options...
Aero77 Posted February 28, 2015 Author Share Posted February 28, 2015 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) Link to comment https://forums.phpfreaks.com/topic/294965-mysql-query-and-how-to-join/#findComment-1507097 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.