July 23, 201214 yr I am trying to get information from my database that is held in two tables; information about websites is held in one table while comments about that website is held in a second. For the purpose of this example let's say there are two comments for a particular website... Using an INNER JOIN command in my SQL query I am querying the website table and the comments table. Testing this with mysql_num_rows shows that it returns two results, as it should, then using a WHILE loop and mysql_fetch_assoc I can print out the two sets of information, each including information about the website and the attached comment, exactly as I want. The problem occurs when I add a maths equation into the SQL query, it correctly performs the equation for the first comment but doesn't even perform a second query as it should, I tested this using mysql_num_rows to make sure it wasnt the while loop that was failing, it only returned one result this time not two. Here is the SQL query... $sql = "SELECT Website.SiteID, Comments.SiteID, (COUNT(Comments.CommentID))*(UNIX_TIMESTAMP(Website.DateAdded)) FROM Website INNER JOIN Comments ON Comments.SiteID = Website.SiteID"; On a side note, I want to divide rather than multiply, I'm using that for my testing at the moment as dividing doesn't return the correct answer, it only goes to 3 decimal places, no more.
July 23, 201214 yr Author I'm creating a popularity algorithm and I'm not sure that I need to use it anymore, but it would appear that it was the count function that was causing the problem.
July 24, 201214 yr If you are trying to measure your site popularity based on the number of visits over a period of time, try something like this: SELECT Website.SiteID, (COUNT( Comments.CommentID )) / ( DATEDIFF( CURDATE( ) , Website.DateAdded ) ) AS PopularityIndex FROM Website INNER JOIN Comments ON Comments.SiteID = Website.SiteID GROUP BY Website.SiteID ORDER BY PopularityIndex Desc
Create an account or sign in to comment