mysql join with a count query on the 2nd table

[Dville]

So I had a thread going in the php/mysql - php help forums. . .but it kinda went dead. And I believe that is due to my issue being more mysql than php.

Here is my table structure for the articles
[code=php:0]// Create a MySQL table in the selected database
mysql_query("CREATE TABLE articles(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
title VARCHAR(255), link VARCHAR(255), category VARCHAR(30), hits INT, date VARCHAR(50), body VARCHAR(400))");[/code]


and here is the structure of the votes table
[code=php:0]// Create a MySQL table in the selected database
mysql_query("CREATE TABLE votes(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
username VARCHAR(50), articleid INT(10))");[/code]


so the query I use is
[code=php:0]$result = mysql_query("SELECT * FROM articles ORDER BY id DESC LIMIT ".$start.", ".$limit);
while($row = mysql_fetch_array( $result )) {[/code]


and this is how i output the data
[code=php:0]while($row = mysql_fetch_array( $result )) {

  $url = $row['link'];
  $id = $row['id'];
  $title = $row['title'];
  $link = $row['link'];
  $category = $row['category'];
  $body = $row['body'];
  $poster = $row['poster'];



echo "<table class='sample' valign='top' border='1' cellpadding='5'> <tr> <th width='49' rowspan='3'>";
echo $row['hits'];
echo "<br>";
echo "<u><font size='2'><a href='/digg/hit.php?appid=$id'>Vote!</a></font></u>";
echo "</th>";

echo "<td width='650'>";
echo "<b>";
?>
<a href="<?php echo $url; ?>"><?php echo $row['title'];?></a> </b><b>
<?php
echo "</b>";
echo "<br><font size='2'>";
echo "Posted on - ";
echo $row['date'];
echo " by - ";
echo "<u><font size='2'><a href='/digg/member_articles.php?appid=$poster'>";
echo $row['poster'];
echo "</a></font></td> </tr> <tr> <td width='650'>";
echo $row['body'];
echo "</td> </tr> <tr> <td width='650'>";
echo "<p style='font-size:78%'>Category - <b>[";?>

<a href="/digg/cat/<?php echo $row['category']; ?>/new.php">

<?php
echo $row['category'];
echo "</a> ] | ";
?>
<a href="<?php echo "/digg/comment.php?appid=$id"; ?> ">Discuss this article</a> |
<a href="mailto:changeme@email.com?subject=Check out this link!&body=<?php echo $row['link'] ?>">Email this link</a> |
<a href="<?php echo "/digg/report.php?appid=$id"; ?>">Report this article</a> |

<?php
echo "</p></b></font></table><br>";

}[/code]




You can see how this looks by going to http://digg.sytes.net/digg

How it's setup now, I just have an incrementing # to tally the votes. I have been told I need to use a seperate table for votes, and I can do where queries to check if that member has voted on a particular article or not. And then I can go through and do a COUNT with a where articleid=$id

Now at first I thought I needed to do another query, but after reading I see there is a join command. This command confuses me very much, especially with wanting to do a 'count' on that 2nd table. I need to be able to use the $id variable, since that's what I use to pick out which article id is being voted on, commented on, being reported, etc.

If anyone could help with this issue that's been driving me crazy for about a week, I would greatly appreciate it. And thanks in advanced.

[fenway]

You simply want to count the number of votes for each article?  And you want to combine this with the existing query?

[Dville]

yes, my votes table has two fields, one for username, and one for articleid
so each query will need to count how many times the variable $id shows up in the field.

I assume I need this combined, due to the fact that $id changes with each article.

[fenway]

Well, you should be able to do something like the following:

[code]SELECT articleid, COUNT(*) AS cnt FROM votes GROUP BY articleid[/code]

And you can easily JOIN in the articles table for the remaining details, if need be.

[Dville]

so i understand what's going on, what does

AS cnt

do?
also, my tables are called 'articles' 'votes' 'users' and 'comments'
what would the syntax look like if, like im wanting to do, when both the votes table AND the articles. the select query you have above doesn't include this

i am new to php, and newer to mysql. tried going to the library and getting 'sql for mere mortals' but it's not there. so any help and explaination of code is greatly appreciated


EDIT - and wouldn't I want to 'select *'?

[fenway]

* would work too... AS is just an alias, in case you're using this count field in PHP, so it becomes easy to reference.

[Dville]

how to i join the articles table into this current query?

and how do I output the count would it be echo COUNT($id);?

[fenway]

Try the following:

[code]SELECT a.*, COUNT(v.id) AS voteCount FROM articles AS a LEFT JOIN votes AS v ON ( v.articleid = a.id ) GROUP BY a.id[/code]

[Dville]

after doing some checking it seems this query works how i like(i just found out i can test queries in phpmyadmin, which will show what the table will look like, so i know how i can access it

[code]select * from articles left join votes on articles.id = votes.id[/code]

EDIT - oh man, after running that in phpmyadmin, i believe that's exactly what im looking for. i will go insert this into my php code and see if it's working correctly

[fenway]

Well, first, you don't want * from a join, you'll get column name collisions; second, there are no counts in that query, which seemed like the entire point of this post.

[Dville]

my column names don't clash(thank god)
when I said i just tried the code, i meant i just tried your code and it worked

thanks a ton fenway. if i could give you a karma point i would
the code now works, and you can check it out at http://digg.sytes.net/digg if you'd like to see the final result login with test/test to check out the restricted voting(test on index.php only, im adding this to the other pages now)

[fenway]

Oh... I thought it was the code that you posted... glad you got it working.  Still, don't rely on not clashing.

[Dville]

so my query is
[code]SELECT a.*, COUNT(v.id) AS voteCount
FROM articles AS a
LEFT JOIN votes AS v ON ( v.articleid = a.id )
GROUP BY a.id[/code]

Just as fenway said. Works perfectly. . .but now I need to add another table, and do a count on (i belive) one column, the articleid column(comments.articleid to be exact). This count will be echo'd from I would guess a commentCount(like the voteCount i have now) that for each article, shows how many comments are made to it.

A - since I have votes.articleid being counted in the query above. will it 'clash' on this new join, cause when I do the query above, i dont have an articleid column. If so, I can change this column name easy.

B - How can I do a 2nd left join, and a 2nd count. . .to this already complicated(to me) query shown above?

Here is the structure to all 3 databases in question.

[code=php:0]// Create a MySQL table in the selected database
mysql_query("CREATE TABLE articles(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
title VARCHAR(255), link VARCHAR(255), category VARCHAR(30), date VARCHAR(50), body VARCHAR(255))");[/code]


[code=php:0]// Create a MySQL table in the selected database
mysql_query("CREATE TABLE comments(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
username VARCHAR(50), comments VARCHAR(255), date VARCHAR(50), articleid VARCHAR(50))");[/code]


[code=php:0]// Create a MySQL table in the selected database
mysql_query("CREATE TABLE votes(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
username VARCHAR(50), articleid INT(10))");[/code]

[fenway]

You can't do that easily, nor does it make any sense.  You should simply run another query, and combine them later... you can't "join" and add more stuff to the "same" record.

[Dville]

what am i adding to the same record? and what record am i adding it to?
lol

i simply want to add another table via left join, that's not possible?
that and do a 2nd count to a new column/record - commentsCount

and if i can't do this, by running another query do you mean a subquery? or a seperate query all together, but if i do that, how can i run both in one while statement

this is how i setup my query

[code=php:0]
$result = mysql_query("SELECT a.*, COUNT(v.id) AS voteCount
FROM articles AS a
LEFT JOIN votes AS v ON ( v.articleid = a.id )
GROUP BY a.id
ORDER BY id DESC
LIMIT ".$start.", ".$limit);

while($row = mysql_fetch_array( $result )) {
[/code]

[Dville]

I see this query

[code]SELECT a.*, COUNT(c.articleid) AS commentsCount
FROM articles AS a
LEFT JOIN comments AS c ON ( c.articleid = a.id )
GROUP BY c.articleid
ORDER BY id DESC[/code]

will combine my votes and comments tables.

This is how it looks

[code=php:0]$result = mysql_query("SELECT a.*, COUNT(v.id) AS voteCount FROM articles AS a LEFT JOIN votes AS v ON ( v.articleid = a.id ) GROUP BY a.id ORDER BY id DESC LIMIT ".$start.", ".$limit);


$result2 = mysql_query("SELECT a.*, COUNT(c.articleid) AS commentsCount
FROM articles AS a
LEFT JOIN comments AS c ON ( c.articleid = a.id )
GROUP BY c.articleid
ORDER BY id DESC");
while($row2 = mysql_fetch_array( $result2 )) {


// Begins listing all items stored into the database
while($row = mysql_fetch_array( $result )) {
[/code]

But when I echo $row2['commentsCount'] It will only echo '0' which is how many comments are on articles.id 's first row. So instead of showing how many commentCounts are shown for each article, it only shows commentCounts on the first article in the table.

If I move the $result2 = mysql_query under the $result = mysql_query's while loop
like so

[code=php:0]
while($row = mysql_fetch_array( $result )) {

$result2 = mysql_query("SELECT a.*, COUNT(c.articleid) AS commentsCount
FROM articles AS a
LEFT JOIN comments AS c ON ( c.articleid = a.id )
GROUP BY a.id");
while($row2 = mysql_fetch_array( $result2 )) {
[/code]



It spits out many many articles, and even echo's duplicate entries.

So yea, I am lost. I shocked myself that I figured out how to count the comments. I just dunno how to add that to my first query.

[Dville]

or maybe I could do. . .

[code=php:0]($row = mysql_fetch_array( $result, $result2 )[/code]


that way there will only be one while loop?

[Dville]

holy shit. . .

[code=php:0]$result = mysql_query("SELECT a.*, COUNT(v.id) AS voteCount, COUNT(c.articleid) AS commentsCount
FROM articles AS a
LEFT JOIN votes AS v ON ( v.articleid = a.id )
LEFT JOIN comments AS c ON ( c.articleid = a.id )
GROUP BY a.id
ORDER BY id DESC LIMIT ".$start.", ".$limit);[/code]


i think i got it!!!!!!

[fenway]

That's right, you can join in another table in this scenario, because the numbers you get back will be meaningless.  You'll have to issue a second query, either separately, or issue the two count queries as subqueries.

(EDIT -- see below)

[Dville]

what i just posted above, works. . .100% to what i need

that one query returns all rows from the articles table, and adds two more, voteCount and commentsCount, which will show
how many votes each article has, and how many comments each article has

about an hour ago i had a 'victory cigarette', lol, i can't believe i figured it out

thank you so much fenway, you really worked with me here. 7 days i've been stuck on adding these two things to my script

when at first i didnt even know there was a join command in mysql, i thought i had to do a $result2 type thing as i posted earlier(except with no join. . .using the result2 to do what the join does, basicly). lol

i hate to ask of something else. but so i don't come out of this experience with just 'using your code' and then 'hacking into it' to do what i figured out.

could you disect this first query code you showed me, and tell me what each step is, and what it does. so i can learn something out of this, and be able to do it on my own next time. . .and from scratch(not just copying your code each time i need a join query)

you can check http://digg.sytes.net/digg/changelog.txt and I have added a 'special thanks' section. Without you guys, I would have thrown php out the window by now. It's really hard for me to learn from manuals. Even some tutorials don't work, unless it's gonna do exactly what I need it to do. Syntax issues have been a big deal, that would have driven me crazy without you guys.

[fenway]

Glad you got it working... my brain wasn't working last night, obviously a second left join where the ON clause is simply linked back to "first" table would be sufficient, as you found out.  I'm not sure what you want me to dissect, but I'd be more than happy to.

[Dville]

just something like

// what each command does, the odd way of doing a.*, and how the count(v.id) works exactly
$result = mysql_query("SELECT a.*, COUNT(v.id) AS voteCount
// what the AS a does exactly
FROM articles AS a
// what the on (v.articleid = a.id) does exactly
LEFT JOIN votes AS v ON ( v.articleid = a.id )
// why do we need them grouped? and by grouping them by a.id how does this give me what i want
GROUP BY a.id

[Dville]

i just found an issue. . .

so the query i have is

[code]
SELECT a.*, COUNT(v.id) AS voteCount, COUNT(c.articleid) AS commentsCount
FROM articles AS a
LEFT JOIN votes AS v ON ( v.articleid = a.id )
LEFT JOIN comments AS c ON ( c.articleid = a.id )
GROUP BY a.id
[/code]


I 'voted' on one article just now, and instead of it refreshing and going from '0' to '1' it goes straight to 3. I voted again, and it went up to 6.

and just did some more test votes. out of about 10 votes, 9 did correct, and changed it from 0 votes to 1. the last one went straight to 4. voted again, and it went up to 8. . .on just two votes

when since the article id only shows up twice, it should only show a total of 2 votes


wow, i can't believe it, i think i figured it out

[code]SELECT a.*, COUNT(v.articleid) AS voteCount, COUNT(c.articleid) AS commentsCount
FROM articles AS a
LEFT JOIN votes AS v ON ( v.id = a.id )
LEFT JOIN comments AS c ON ( c.id = a.id )
GROUP BY v.articleid[/code]

[Dville]

well. . .now i see it isnt pulling all the rows . . .
:(

my total articles went from like 60 to 25

[shoz]

[quote=Dville]
[code]
SELECT a.*, COUNT(v.id) AS voteCount, COUNT(c.articleid) AS commentsCount
FROM articles AS a
LEFT JOIN votes AS v ON ( v.articleid = a.id )
LEFT JOIN comments AS c ON ( c.articleid = a.id )
GROUP BY a.id
[/code]
[/quote]

[quote=fenway]
You should simply run another query
[/quote]

I'd also recommend that you run another query. Do one join and count the comments and another join and count the votes.

Based on how the tables are related (I'm assuming how the tables are related based on the query you posted), the query you posted will most likely give an inaccurate count.

Note that if you're using MYSQL 4.1 or higher, you can also use a subquery to get the result you want.  I'd probably still use 2 queries and possibly if I had speed problems, experiment with the subquery to see if it was faster.

[code]
SELECT
a.*, COUNT(c.id) AS numcomments, v.numvotes
FROM articles AS a
LEFT JOIN
comments AS c
ON a.id=c.articleid
INNER JOIN
    (
    SELECT
    a2.id AS articleid, COUNT(v2.id) AS numvotes
    FROM articles AS a2
    LEFT JOIN
    votes AS v2
    ON a2.id=v2.articleid
    GROUP BY a2.id
    ) AS v
ON
v.articleid=a.id
GROUP BY a.id;[/code]