On Thu, May 1, 2008 at 10:49 PM, James Keeline <[EMAIL PROTECTED]> wrote: > --- Phill Sparks <[EMAIL PROTECTED]> wrote: > >> An example of the full query is... >> >> SELECT SQL_CALC_FOUND_ROWS uri, COUNT(tag) AS related_tag_count >> FROM ( >> SELECT subject AS uri, object AS tag >> FROM milk_properties prop >> INNER JOIN milk_posts model >> ON model.uri = prop.subject >> AND model.status = "published" >> AND model.parent = 'milk:blog' >> WHERE >> predicate="http://cuttlefish.com/schemas/cms/1.0/concepts/document/tags" >> AND prop.subject <> 'posts/2008/04/13/how_to_customise_your_ssh_config' >> AND prop.object IN ('SSH','config','unix','howto') >> ) AS tags >> GROUP BY uri >> HAVING COUNT(tag) > 0 >> ORDER BY COUNT(tag) DESC, uri DESC >> > The SQL_CALC_FOUND_ROWS was new to me so I looked it up and found this page > > http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows > > which suggests that it needs to be used in conjunction with a found_rows() > function. > > I wonder if all of this is necessary or if it is being over engineered. Are > there particular advantages you are trying to leverage with this syntax? > > Getting back to your initial issue, I don't know when some of these features > were added and that could be confusing the older version of MySQL you are > using. The PHP doc pages give better information on when a feature is added. > > James >
Woo! I've fixed it! It would seem that it didn't like me using COUNT() in the ORDER BY statement. I changed this to use the alias 'related_tag_count' and it's working fine! :-) MySQL 4 seems to have no problems with the SQL_CALC_FOUND_ROWS being in there. It's there so that I can find out the total number of results available, useful for pagination and the such. Thanks for your help! Phill
