/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Need Help Construcing a SQL Statement for PHP/MySql

Copy linkTweet thisAlerts:
Sep 17.2008

Hey, I wasn’t sure if this is a possibility. I’d like to INNER JOIN 2 tables so I created the following SQL Statement

[CODE]SELECT blogs.id, blogs.createddate, blogs.title, blogs.userid, COUNT(0) AS Expr1
FROM blogs INNER JOIN blogs_comments ON blogs_comments.blogid = blogs.id AND blogs.userid = CurrentUser
GROUP BY blogs.id, blogs.createddate, blogs.title, blogs.userid[/CODE]

Okay, now obviously it’ll only show blogs that have comments. I want to be able to show blogs that have 0 comments as well (that’s why I tried Count(0), I was hoping just maybe. Is there a way to do that in the SQL Statement? I know I could just make the first recordset just pull everything from the blogs table and filter by id and then use the following code in a repeat loop:

[code=php]<?php
$blogid = $row_myblogs[‘ID’];
$blogcount = mysql_num_rows(mysql_query(“SELECT * FROM blog_comments WHERE blogid = ‘%$blogid%'”));
?>[/code]

(I know the above script doesn’t have the right variables, I used it from an old site I built).

Basically I was wondering if I’m going about this with the second example a longer way, when I can just do it all in the SQL Statement, or am I just shooting for the stars?

Suggestions?

to post a comment
PHP

5 Comments(s) ↴

Copy linkTweet thisAlerts:
@Phill_PaffordSep 17.2008 — Okay well your COUNT(0) is selecting a count of 1, your telling MySQL to count the number zero. which it does and then retruns the count which is one zero.

maybe try this
[CODE]
SELECT blogs.id, blogs.createddate, blogs.title, blogs.userid
FROM blogs INNER JOIN blogs_comments ON blogs_comments.blogid = blogs.id AND blogs.userid = CurrentUser
GROUP BY blogs.id, blogs.createddate, blogs.title, blogs.userid, blogs.comments HAVING COUNT(*) < 1
[/CODE]

I don't know the field name so I guessed, you can replace the comment field "blogs.comments" with the field your doing the COUNT() on.

Hope this helps
Copy linkTweet thisAlerts:
@cinematic_jesiauthorSep 17.2008 — This doesn't work. (Unless I'm doing something wrong). There's no such thing as blogs.comments. In the original SQL Statement, I made a new variable called Expr1.. which counts records in the inner join of the two tables.. Basically how many people commented on a particular blog..
Copy linkTweet thisAlerts:
@Phill_PaffordSep 17.2008 — [CODE]
SELECT blogs.id, blogs.createddate, blogs.title, blogs.userid, COUNT(*) AS Expr1
FROM blogs INNER JOIN blogs_comments ON blogs_comments.blogid = blogs.id AND blogs.userid = CurrentUser
GROUP BY blogs.id HAVING Expr1 < 1, blogs.createddate, blogs.title, blogs.userid
[/CODE]

I think this will work, need to add the HAVING Expr1 < 1 clause for the comment count in the GROUP BY
Copy linkTweet thisAlerts:
@cinematic_jesiauthorSep 17.2008 — Its saying there is a syntax issue.

But when I remove [B]HAVING Expr1 < 1[/B] it works fine..... this is a toughy! ?

EDIT: Works fine as in no syntax issues...
Copy linkTweet thisAlerts:
@Phill_PaffordSep 17.2008 — [CODE]
SELECT blogs.id, blogs.createddate, blogs.title, blogs.userid, COUNT(*) AS Expr1
FROM blogs INNER JOIN blogs_comments ON blogs_comments.blogid = blogs.id AND blogs.userid = CurrentUser
GROUP BY blogs.id, blogs.createddate, blogs.title, blogs.userid HAVING Expr1 < 1
[/CODE]
×

Success!

Help @cinematic_jesi spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.5,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...