Jump to content

Archived

This topic is now archived and is closed to further replies.

Dville

mysql join with a count query on the 2nd table

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.