ArdaTahsinAyar ArdaTahsinAyar - 24 days ago 8
SQL Question

Get One Normal Table and Counts From Another Table In One SQL Query (JOIN)

I need a SQL query which gives content of one table and count of second table. I tried, could not.

I have a "comments" table. And in the another table named "likings", there are numbers of likes of comments.

In my algorithm, in the "likings" table, there are a column named "

likeType
", if it is "
1
" that means a like, otherwise (if it is "
0
") means a dislike. I need to bring comment from "
comments
" table, counts of
likeType=1
and
likeType=0
from "
likings
" table in one query.

Here is my best attempt for that, which didn't work:




PHP side:



$getFirst8Comments = "SELECT
episodecomments.cmtID,
episodecomments.cmtConEpisode,
episodecomments.cmtOwner,
episodecomments.cmtDate,
episodecomments.cmtContent,
episodecomments.cmtSpoiler,
SUM(IF(episodecommentsliking.liType='1', 1, 0)) AS likes,
SUM(IF(episodecommentsliking.liType='0', 1, 0)) AS dislikes
FROM episodecomments
LEFT JOIN episodecommentsliking
ON episodecomments.cmtID = episodecommentsliking.liCmtID
GROUP BY
episodecomments.cmtID,
episodecomments.cmtConEpisode,
episodecomments.cmtOwner,
episodecomments.cmtDate,
episodecomments.cmtContent,
episodecomments.cmtSpoiler
WHERE episodecomments.cmtConEpisode='$epID'
ORDER BY episodecomments.cmtID
DESC LIMIT 8";

while ($getF8C = mysqli_fetch_array($getFirst8Comments))
{
echo "Something coming through-<br>";
}




Error pops out:
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, string given in J:\file.php on line 3484 (error in variable $getFirst8Comments)


Thanks in advance.

Answer

MySQL doesn't support COUNT in that fashion. You'll also need to inform it what to GROUP BY for those aggregate (sum) functions.

Try using SUM instead: (COUNT would work if you're counting NULL vs non-NULL values however - see the MySQL documentation)

  SELECT
    comments.cmtID,
    comments.cmtConEpisode,
    comments.cmtOwner,
    comments.cmtDate,
    comments.cmtContent,
    comments.cmtSpoiler,
    SUM(IF(commentsliking.liType='1', 1, 0)) AS likes,
    SUM(IF(commentsliking.liType='0', 1, 0)) AS dislikes
  FROM comments
  INNER JOIN commentsliking
    ON comments.cmtID = commentsliking.liCmtID
  GROUP BY
    comments.cmtID,
    comments.cmtConEpisode,
    comments.cmtOwner,
    comments.cmtDate,
    comments.cmtContent,
    comments.cmtSpoiler
  ORDER BY comments.cmtID
  DESC LIMIT 8

Also - use "LEFT JOIN" if there's the possibility that a comment record will exist with no like data.


I've further updated my answer to address the error you've since reported being thrown by the mysqli_* commands.

Check the PHP documentation for full details on how to correctly use the mysqli_fetch_array() function - especially with regards to it requiring a mysqli_result type as an argument, as opposed to a string [of SQL])

Comments