Page 1 of 1
I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 8:35 pm
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?
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 9:14 pm
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
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 9:47 pm
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.
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 9:51 pm
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.
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 10:18 pm
by scarface222
Alright thanks man, as usual
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 10:20 pm
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
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 10:30 pm
by John Cartwright
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 10:43 pm
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
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 10:49 pm
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?
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 10:58 pm
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.
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 11:01 pm
by scarface222
so then basically a function will not reduce server load...OK great to know, was not sure about that.
Re: I am struggling with query, can someone quickly look it over
Posted: Mon Mar 01, 2010 11:06 pm
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
Re: I am struggling with query, can someone quickly look it over
Posted: Tue Mar 02, 2010 9:54 am
by pickle
Moved to Databases