[thelist] MySQL problemo

James Newbery jamesnewbery at yahoo.co.uk
Tue Aug 20 06:50:00 CDT 2002

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

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?



<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.



Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts

More information about the thelist mailing list