[thelist] mysql select query

[Means, Eric D]

> um, let's say that i've just got one table (which i have) and
> that there
> are a lot of different story categories (which are defined by
> name rather
> than number) and that there are only a couple of categories
> from which i
> want to exclude the archived stories... which is right?

Technically they're all equivalent.

(1) uses php-style variables; you'd pass in the text for the two categories
you want to return only live stories as $SectionID and $SectionID2.  It
otherwise is basically identical to (2) (but note that (2) should probably
say "and ((not section = 'news' and not section = 'feature') or..." instead
of the simplified form.
(3) uses "not in" instead of "not x and not y"; it's a little more succint,
but IN may not be supported on mySQL, now that I think about it.  It's also
a little easier to add future categories to (just add a comma followed by
the category name, instead of "and not section = 'whatever'").

They should all work more or less equally well.

<references>
1)
SELECT * FROM $db.$table WHERE ((SectionID = $SectionID AND Live = 1) OR
(Section ID = $SectionID2 AND Live = 1)) AND Published = 1 ORDER BY
RAND() LIMIT 3

2)
where published is not null and ((not news and not feature) or live is not
null)

3)
select * from stories
where published is not null
and (storytype not in ('news', 'feature') or live is not null)
order by random limit 3
</references>