[thelist] Need help with difficult MySQL query

Simon Willison simon at incutio.com
Fri Jul 12 18:48:02 CDT 2002


I'm currently re-writing my blog to use MySQL for storage (at present it
uses serialized PHP objects in text files, which is very versatile but is
unlikely to scale particularly well). I've run in to an interesting SQL
problem which has got my stumped. Blog entries are categorised, but an
entry can appear in more than one category.

Here are my (relevant) tables:

entries - the blog entries themselves
=======
id (index, the ID of the entry)
entryText
dateAdded

categories - used to categorise blog entries
==========
shortName - shortened name used in URLs, e.g "webservices", also acts as
table index
longName - full name of category used for display, e.g "Web Services"

ent_cat_lookup - the lookup table for the many-to-many relationship between
entries and categories
==============
categoryShortName - the shortname of the category
entryId - the ID of the entry

Here's my problem. In order to display the most recent blog entries I need
a query which grabs the last 20 entries from the database complete with
their category information (I need both the shortName and the longName in
order to properly display links to the category pages). I only want to
return each entry once, but some entries will need to return information
for more than one category. The following query returns multiple rows for
any entries in more than one category, which is far from ideal:

select entries.*, categories.* from entries, categories, ent_cat_lookup
where entries.id = ent_cat_lookup.entryId
and categories.shortName = ent_cat_lookup.categoryShortName
order by entries.dateAdded desc

I have also tried grouping by entries.id :

select entries.*, categories.* from entries, categories, ent_cat_lookup
where entries.id = ent_cat_lookup.entryId
and categories.shortName = ent_cat_lookup.categoryShortName
group by entries.id
order by entries.dateAdded desc

This only returns one row per entry, but in doing so only returns
information on one category pre entry which is no good as some entries have
more than one category.

Is what I'm trying to do even possible?

Thanks,

Simon Willison
http://www.bath.ac.uk/~cs1spw/blog/

<tip type="PHP Debugging">
print_r() is your friend! If you need to debug a form that has been
submitted (to work out what variables are available, particularly useful if
you are dealing with checkboxes) use print_r($_REQUEST); to quickly see
exactly what information has been submitted. For an interesting look at how
a script works try adding print_r($GLOBALS) to the bottom of the file to
see a dump of the whole of PHP's global namespace. Both of these lines can
benefit from echo '<pre>'; in front of them to ensure that the output array
is readable, although you can just view-source on the resulting HTML for
the same effect.
</tip>




More information about the thelist mailing list