I am struggling with query, can someone quickly look it over

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
scarface222
Forum Contributor
Posts: 354
Joined: Thu Mar 26, 2009 8:16 pm

I am struggling with query, can someone quickly look it over

Post by scarface222 »

Here is my query

SELECT *, COUNT(*) AS num FROM topic LEFT JOIN counter ON counter.topic_id=topic.topic_id WHERE topic.creator = '$user' ORDER BY counter.views DESC LIMIT $start, $limit

I want to count the results so I can use pagination, the query stops working with the addition of ', COUNT(*) AS num'. I was also able to get the count working when using a count with a subsequent query but the subsequent query is highly redundant in my opinion. Can I even use count with left join?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: I am struggling with query, can someone quickly look it over

Post by mikosiko »

scarface222 wrote:Here is my query

SELECT *, COUNT(*) AS num FROM topic LEFT JOIN counter ON counter.topic_id=topic.topic_id WHERE topic.creator = '$user' ORDER BY counter.views DESC LIMIT $start, $limit
look there ^^^ in red
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Re: I am struggling with query, can someone quickly look it over

Post by Griven »

When you add the COUNT() function to your query, and you do not use a GROUP BY clause on the end, your query will only return one row.

My advise is to drop the COUNT(*) from the query, and count up the items returned with PHP.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: I am struggling with query, can someone quickly look it over

Post by Eran »

My advise is to drop the COUNT(*) from the query, and count up the items returned with PHP.
He can't count the items in PHP since he's using a LIMIT clause.

Use your original approach - a separate count query and a select query with a limit.
scarface222
Forum Contributor
Posts: 354
Joined: Thu Mar 26, 2009 8:16 pm

Re: I am struggling with query, can someone quickly look it over

Post by scarface222 »

Alright thanks man, as usual
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: I am struggling with query, can someone quickly look it over

Post by John Cartwright »

pytrin wrote:
My advise is to drop the COUNT(*) from the query, and count up the items returned with PHP.
He can't count the items in PHP since he's using a LIMIT clause.

Use your original approach - a separate count query and a select query with a limit.
There is a way to do this in a pure SQL.. I just can't remember how...

//waiting for someone else to chime in
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: I am struggling with query, can someone quickly look it over

Post by Eran »

There is a way to do this in a pure SQL.. I just can't remember how...
I think you meant in one query, COUNT(*) is pure SQL just the same. Regarding SQL_CALC_ROWS, I'd advise against it -
viewtopic.php?p=509688#p509688
scarface222
Forum Contributor
Posts: 354
Joined: Thu Mar 26, 2009 8:16 pm

Re: I am struggling with query, can someone quickly look it over

Post by scarface222 »

Ok then I have one more question. The reason I wanted to reduce it to one query is so I could more easily create a universal function for the very simlar sorting queries where you just substitute the query in parameters. This does not really work in this case though since things defined in the function will not be defined outside where the pagination script is. My question however is this: I know google and sites like reddit condense their code by deleting all the white space. Should I try to condense php similarly by trying to condense similar code into functions to save space? Is this worth doing to improve performance and reduce bandwidth?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: I am struggling with query, can someone quickly look it over

Post by Eran »

Sites concatenate and compress client-side scripts (Javascript, stylesheets) to reduce bandwidth and the number of requests. This has nothing to do with PHP or any other server-side scripts.
scarface222
Forum Contributor
Posts: 354
Joined: Thu Mar 26, 2009 8:16 pm

Re: I am struggling with query, can someone quickly look it over

Post by scarface222 »

so then basically a function will not reduce server load...OK great to know, was not sure about that.
scarface222
Forum Contributor
Posts: 354
Joined: Thu Mar 26, 2009 8:16 pm

Re: I am struggling with query, can someone quickly look it over

Post by scarface222 »

you guys probably already know all this, but I found it pretty interesting. check out this article on optimization

http://developer.yahoo.com/performance/rules.html
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: I am struggling with query, can someone quickly look it over

Post by pickle »

Moved to Databases
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply