I am struggling with query, can someone quickly look it over
Moderator: General Moderators
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
I am struggling with query, can someone quickly look it over
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?
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?
Re: I am struggling with query, can someone quickly look it over
look there ^^^ in redscarface222 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
Re: I am struggling with query, can someone quickly look it over
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.
My advise is to drop the COUNT(*) from the query, and count up the items returned with PHP.
Re: I am struggling with query, can someone quickly look it over
He can't count the items in PHP since he's using a LIMIT clause.My advise is to drop the COUNT(*) from the query, and count up the items returned with PHP.
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
Alright thanks man, as usual
- 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
There is a way to do this in a pure SQL.. I just can't remember how...pytrin wrote:He can't count the items in PHP since he's using a LIMIT clause.My advise is to drop the COUNT(*) from the query, and count up the items returned with PHP.
Use your original approach - a separate count query and a select query with a limit.
//waiting for someone else to chime in
- 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
I think you meant in one query, COUNT(*) is pure SQL just the same. Regarding SQL_CALC_ROWS, I'd advise against it -There is a way to do this in a pure SQL.. I just can't remember how...
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
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?
Re: I am struggling with query, can someone quickly look it over
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
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
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
http://developer.yahoo.com/performance/rules.html
Re: I am struggling with query, can someone quickly look it over
Moved to Databases
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.