Jump to content

mysql join with a count query on the 2nd table


Dville

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

  • 3 weeks later...
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
Link to comment
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]
Link to comment
Share on other sites

  • 5 years later...

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.