[thelist] mysql/php - creating an index

[Liam Delahunty]

Dunstan Orchard wrote:

>I'd like to be able to show a list of everyone <cited>'d in my blog and
links
>to the posts in which their name appeared.

...

>This table would be emptied and rebuilt each time I added/edited/deleted a
>post.

I think you'll be much better off with two tables. One for the people cited
and another one for the articles. That way you can also maintain a list of
who has been cited in which documents.

So you'd have something like:
cite_tbl
id int not null
citing char(255)

cite_doc_tbl
id int not null
citee_doc int not null // if page is in db else char(255) if it's a filename
cite_id int not null

then when you add to your blog you'd just need to:
1. search the text for a cite.
2. if ones found then check to see if the person is in cite_tbl
 if so return the id,
 if not add them
3. add the current document and cite_id to cite_doc_tbl

Then Bob's your aunties live-in lover..

Kind regards, Liam Delahunty, Mega Products Limited
http://www.megaproducts.co.uk/ web design, programming, e-commerce
http://www.onlinesales.co.uk/ Open Source PHP/MySQL E-commerce App.
http://www.britstream.com/ Hosting/ Domain Names From UKP 7.50 p.a.