mysql join with a count query on the 2nd table

[Dville]

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
[code=php:0]$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 )) {

[/code]

then i start echoing my data

EDIT - line 28 is the while statement

[Dville]

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

[shoz]

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

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
[quote=Dville]
$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 )) {
[/quote]
If using 2 queries it would probably be easier to create an array that holds the values and then output the result after.
eg:
[code]
<?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'];
}
?>
[/code]

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.

[Dville]

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?

[shoz]

[quote author=Dville link=topic=100996.msg400998#msg400998 date=1153457621]
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?
[/quote]

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.

[Seth^^]

Hello shoz,

I have the same problem that Deville had.
This is my query
[code]
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
[/code]

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

[Seth^^]

never mind, i managed to do it on my own... hehe
[code]
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
[/code]

[sosdios]

@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!

[Maq]

Please don't revive dead threads from 6 years ago, thanks.