[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