[thelist] MySQL problemo

[James Newbery]

I have a MySQL db with three tables:

- pages (a list of urls and page title)
- keyphrases (a list of keyphrases with definitions)
- pages_phrases (a list of which pages have which
phrases (many-to-many))

Each has auto-increment ids as the primary key.

I am trying to write a query that lists all the
keyphrases beginning with a specified letter (like an
A to Z index), but only if that keyphrase is
associated with at least one page (i.e. there is at
least one entry with the keyphrase id number in
'pages_phrases').

In PHP I currently have:

$query = "SELECT * FROM keyphrases AS kp,
pages_phrases AS pph" .
"WHERE kp_phrase LIKE '" . strtolower($letter) . "%'
OR kp_phrase LIKE '" . strtoupper($letter) . "%' " .
"AND kp.kp_id = pph.kp_id " .
"GROUP BY kp.kp_id ORDER BY kp_phrase";

This correctly lists the keyphrases for the letter in
alphabetical order, but still maintains those that do
not have any associated page relationships.

Is the GROUP BY tack the correct one, or is there some
other way that I should be doing this? Will I have to
perform further queries?

TIA,

Jim

<tip type="Interaction Design" author="Jim Newbery">

Stuck on how to approach the interface design for a
new site? Need some fresh ideas for information
presentation and navigation?

Take a look at some UI patterns. These are tried and
(usually) tested UI methods for particular UI needs.

http://time-tripper.com/uipatterns/
http://www.welie.com/patterns/

</tip>

__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com