[thelist] mysql select query

Steve Cook steve.cook at evitbe.com
Thu Apr 4 08:50:01 CST 2002


You say that the story sections are included in the same table? Are they
stored as text or as numbers? If they are stored as text then
congratulations, you've just made life difficult for yourself ;-)

The best way to store this would be to have a table with the following:

table name: stories

storyId
storyText
storyHeader
...etc
storyLive
storyArchived

and then a second table with something like

table name: categories

categoryId
categoryName

and then a third table with

table name: relCatsStories

storyId
categoryId

The third table is a link / relation table. It contains the number os a
story and the category it is a part of. In this way you can create stories
which pertains to severaö categories by having several rows.

To get the information back, you would do the following:

SELECT * FROM stories LEFT JOIN relCatsStories ON stories.storyId =
relCatsStories.storyId JOIN categories ON relCatsStories.categoryId =
categories.categoryId;

If you want all the storeis from a specific category then you use the
following:

SELECT * FROM stories LEFT JOIN relCatsStories ON stories.storyId =
relCatsStories.storyId JOIN categories ON relCatsStories.categoryId =
categories.categoryId WHERE categories.categoryId = 3 AND storyLive = 1
ORDER BY
RAND() LIMIT 3;

(Of course, there are ways to do it using a varchar column for the category,
but in my opinion it is best to try and build this sort of flexibility into
your design from the beginning - it is much harder to go back and rebuild
this in the future. You may of course decide it is overkill and run with one
of the other solutions presented ;-).

.steve

----------------------------------
   WapWarp - http://wapwarp.com
 Wap-Dev - http://www.wap-dev.net
 Cookstour - http://cookstour.org
----------------------------------
> select stuff from stories where published is not null and, if
> its a news
> story or a feature story, make sure live is not null, order
> by random limit 3
>
> it's the "if it's a news story..." bit that i'm stumped on.
>
> any help *really* appreciated.
>
> tia



More information about the thelist mailing list