Dynamically Selecting Query

[bladx]

hey everyone,

i've been reading up on the select documentation http://dev.mysql.com/doc/mysql/en/SELECT.html lately, but I'm still trying to figure out how to do this:

I have two tables. One is called "story", and the other is "categories". Basically, a story has a category ID number. In the table categories, there are a small list of categories that a story fits into (e.g. news, help, random, etc.) basically topics.

I want to be able to select from the story table to get the first latest story, selecting it from the news category. I know how to do that, if I use a hardcoded number when i know the ID for the category named "news". But, what I want to do, is in case that category "news" ever gets deleted, and someone makes a new category named news later, I need the select to be based on the label "news" and not on the ID of the category. Here's a look at what the table looks like:

"categories" table

+-----+------------------+
| cid | c_name |
+-----+------------------+
| 1 | news |
| 2 | FAQ |
| 6 | test |
| 7 | another test |
| 8 | yet another test |
| 9 | hello |
| 10 | test |
| 11 | yeah |
+-----+------------------+

and here is the "story" table

+------------------+----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------------------+----------------+
| sid | smallint(5) unsigned | | PRI | NULL | auto_increment |
| catid | smallint(5) | | | 0 | |
| subcat | smallint(5) | | | 0 | |
| child | smallint(5) | YES | | NULL | |
| title | varchar(255) | | | | |
| author | varchar(255) | | | | |
| date | datetime | YES | | 0000-00-00 00:00:00 | |
| bodytext | text | | | | |
| layout | varchar(255) | YES | | NULL | |
| image | varchar(255) | YES | | NULL | |
| shortdescription | varchar(255) | YES | | NULL | |
| relevance | smallint(2) | | | 0 | |
+------------------+----------------------+------+-----+---------------------+----------------+

here is what i currently tried... but it doesn't select the right category "news":

select story.title, story.image, story.bodytext, story.shortdescription, categories.c_name from story, categories where categories.c_name='news' order by date DESC limit 0,1

[mrhappiness]

SELECT
  s.title, s.image, s.bodytext,
  s.shortdescription, 
  c.c_name 
FROM story s
  INNER JOIN categories C ON c.cid = s.catid
WHERE c.c_name='news' 
ORDER BY s.date DESC 
LIMIT 1

give this a try

[bladx]

Thanks for the fast reply, I just found out how to do it on my own, but I want to try out your code too right now, thanks,

here's what i used:

select * from story, categories where story.catid=categories.cid and c_name="news" order by date DESC limit 0,1;

[mrhappiness]

the results should be the very same

i prefer my version (of course i do so 😉) but the 1 or 2 microseconds you will save doing it with JOIN and not with WHERE don't matter i believe

[mourisj]

Items in your categories table should not be deleted! Otherwise, items in story will refer to a category which no longer exists!
On the otheer hand, if a new category is created as described in your example, this will not help your records in story to recover a category!

In worst case, your useres will never find back these stories!

[bladx]

mrhappiness, i like the use of join though, i just am new to that, so i will use your code, thanks 😃

mourisj: good point but, if you make a deleted categories old categories just default to another category, it works fine.