Jump to content


Photo

mysql join with a count query on the 2nd table


  • Please log in to reply
33 replies to this topic

#21 fenway

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

Posted 20 July 2006 - 04:04 PM

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

#22 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 10:27 PM

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

#23 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 21 July 2006 - 02:49 AM

i just found an issue. . .

so the query i have is

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


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

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


#24 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 21 July 2006 - 03:02 AM

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

my total articles went from like 60 to 25

#25 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 July 2006 - 04:00 AM

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


You should simply run another query


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.

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;


#26 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 21 July 2006 - 04:15 AM

that does work, when run in phpmyadmin

but when i do it through php i get

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\www\digg\include\body.php on line 28


the php code i have is
$result = mysql_query("SELECT
a.*, COUNT(c.articleid) 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
 ORDER BY voteCount DESC LIMIT ".$start.", ".$limit);

while($row = mysql_fetch_array( $result )) {


then i start echoing my data

EDIT - line 28 is the while statement

#27 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 21 July 2006 - 04:28 AM

sorry it was the order by votecount, changed it and i think it works well now, time to really test


EDIT - sweet, impressive, that works exactly how i am looking for it to work
I thought I had to change your count(c.id) to count(c.article.id). but no. it works perfectly


if i only knew what it was doing, lol

#28 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 July 2006 - 04:42 AM

To make debugging easier use the following format when making the mysql_query call
<?php
$query = 'query here';
$result = mysql_query($query) or die('query: '.$query.'<br />\n'.mysql_error());
?>

If you've changed the ORDER BY numvotes then the "LIMIT $start and $limit" is probably causing a problem. Use mysql_error() in the way shown above and see if you can find the error. If you can't, post the error and the query shown here.

I originally missed the fact that you tried using 2 queries earlier, but had trouble displaying the data

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

If using 2 queries it would probably be easier to create an array that holds the values and then output the result after.
eg:
<?php
$result = 'SELECT a.*, voteCount FROM etc';
$the_array = array();
while($row = mysql_fetch($result))
{
    $the_array[($row['id'])]['voteCount'] = $row['voteCount'];
    $the_array[($row['id'])]['otherinfo'] = $row['otherinfo'];
}

$result = 'SELECT commentCount etc';
while($row = etc)
{
   $the_array[($row['id'])]['commentCount'] = $row['commentCount'];
}

foreach ($the_array as $articleid => $i)
{
   print $article_id.':'.$i['voteCount'].','.$i['otherinfo'];
}
?>

EDIT: I missed your edit while replying. I saw the original post where you said that you changed voteCount to numvotes but it still wasn't working.

#29 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 21 July 2006 - 04:53 AM

thanks for that debugging tip, i will keep that in mind

i guess over time, with having at least more time looking at mysql, it will come together? or is mysql something you have to study real hard?

as like a comparison maybe, is mysql harder to pick up than php?

#30 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 July 2006 - 07:16 AM

thanks for that debugging tip, i will keep that in mind

i guess over time, with having at least more time looking at mysql, it will come together? or is mysql something you have to study real hard?

as like a comparison maybe, is mysql harder to pick up than php?


I can't really say which is harder to pick up, but I'd recommend reading a book that deals with MYSQL exclusively rather than a PHP book that has a MYSQL chapter. Do your own experiments. Make test tables and perform test JOINS, GROUP BYs etc.

#31 Seth^^

Seth^^
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 10 August 2006 - 12:39 PM

Hello shoz,

I have the same problem that Deville had.
This is my query
	SELECT		l.id, l.parent, l.traceback, l.name, l.pic, l.description, l.d_lastupdated, 
				COUNT(c.id) AS child, c.parent,
				COUNT(f.id) AS files, f.parent
	
	FROM downloads AS l
	LEFT JOIN downloads AS c ON c.parent = l.id
	LEFT JOIN files AS f ON f.parent = l.id
	WHERE l.parent = '{$_GET['d']}' 
	
	GROUP BY l.id
	ORDER BY l.name ASC

Can you please show me how the sub-query is made ? Thanks in advance

#32 Seth^^

Seth^^
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 10 August 2006 - 12:54 PM

never mind, i managed to do it on my own... hehe
SELECT
l.*, COUNT(f.id) AS numfiles, c.numchildren
FROM downloads AS l
LEFT JOIN
files AS f
ON l.id=f.parent
INNER JOIN
    (
    SELECT
    l2.id AS parent, COUNT(c2.id) AS numchildren
    FROM downloads AS l2
    LEFT JOIN
    downloads AS c2
    ON l2.id=c2.parent
    GROUP BY l2.id
    ) AS c
ON
c.parent=l.id

WHERE l.parent = '{$_GET['d']}' 
	
GROUP BY l.id
ORDER BY l.name ASC


#33 sosdios

sosdios
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 07 June 2012 - 06:32 PM

@Shoz, There are a bunch of MYSQL front-ends that can make it easier to use and can ensure proper database structuring and management. http://www.Eliacom.com has one that has a built in query browser where you can do some testing and also be able to see what effects it has pretty easily. There's also http://www.vfront.org/ which is a bit more like phpmyadmin, but is a little clunky for my taste.

Hope that helps!

#34 Maq

Maq
  • Administrators
  • Advanced Member
  • 9,410 posts
  • LocationPennsylvania, USA

Posted 07 June 2012 - 07:03 PM

Please don't revive dead threads from 6 years ago, thanks.
tjmothy
ini_set ("display_errors", "1");
error_reporting(E_ALL);




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users