Jump to content


Photo

mysql join with a count query on the 2nd table


  • Please log in to reply
33 replies to this topic

#1 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 18 July 2006 - 04:30 PM

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
// 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))");


and here is the structure of the votes table
// 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))");


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


and this is how i output the data
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>";

}




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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 July 2006 - 06:20 PM

You simply want to count the number of votes for each article?  And you want to combine this with the existing query?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 18 July 2006 - 06:25 PM

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.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 July 2006 - 08:54 PM

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

SELECT articleid, COUNT(*) AS cnt FROM votes GROUP BY articleid

And you can easily JOIN in the articles table for the remaining details, if need be.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 18 July 2006 - 10:08 PM

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 *'?

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 July 2006 - 11:19 PM

* would work too... AS is just an alias, in case you're using this count field in PHP, so it becomes easy to reference.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 18 July 2006 - 11:35 PM

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

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

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 12:02 AM

Try the following:

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 19 July 2006 - 12:11 AM

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

select * from articles left join votes on articles.id = votes.id

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

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 01:08 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 19 July 2006 - 01:37 AM

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)

#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 01:41 AM

Oh... I thought it was the code that you posted... glad you got it working.  Still, don't rely on not clashing.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#13 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 12:01 AM

so my query is
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

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.

// 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))");


// 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))");


// 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))");


#14 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 July 2006 - 12:35 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#15 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 12:51 AM

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

$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 )) {


#16 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 01:52 AM

I see this 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

will combine my votes and comments tables.

This is how it looks

$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 )) {

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

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 )) {



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.

#17 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 02:20 AM

or maybe I could do. . .

($row = mysql_fetch_array( $result, $result2 )


that way there will only be one while loop?

#18 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 03:19 AM

holy shit. . .

$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);


i think i got it!!!!!!

#19 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 July 2006 - 03:33 AM

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)
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#20 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 03:59 AM

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.ne...g/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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users