I've been away . . . . the overall assessment, you're making life very hard for yourself if you rely on sarching within that block of text for tags. A few highlights,
Notice how new information is added on, but the years are not sequential.
Right, this is where the power of mysql comes in and why I demonstrated an AD/BC column. as Readie says, this allows you to order the results however you want with a few simple changes in your order by clause. This basic data structure would allow you to do that, working example below.
Wouldn't it just be easier to include the year into the line of text and not worry about it?
By putting it into the text, you're locking yourself into the very problem you describe, and the order of the records becomes relevant, whereas with separate columns, you can get a variety of result orderings by a simple rearrancement of the select statement. What you don't appear to be seeing is your requirements for this data may change as it grows, and as much as you can normalize in the underlying structure will make for more painless transitions as it does.
Intrigued by the overall question, here's a little test I put together using my example, you should be able to recreate it and run the below tests via command line or phpMyAdmin. Note I threw them out of order in the insert to verify the correctness when selecting.
create table articles (id int(11) primary key auto_increment, article_id int(11), year int(4), anno_domino char (2) not null default 'AD', description varchar(255));
insert into articles (article_id,year,anno_domino,description) values ('1234','143','BC','ruler XYZ invaded Rome');
insert into articles (article_id,year,anno_domino,description) values ('1235','294','AD','the whatchamacallit was invented by Harold IX');
insert into articles (article_id,year,anno_domino,description) values ('1236','312','AD','The Ewoks Sacked Rome');
insert into articles (article_id,year,anno_domino,description) values ('1237','64','BC','rocknbil pillages brewery');
create table tags (id int(11) primary key auto_increment, tag_id int(11), article_id int(11), tag_content varchar(255));
insert into tags (tag_id,article_id,tag_content) values ('987','1234','Ruler XYZ');
insert into tags (tag_id,article_id,tag_content) values ('988','1234','Rome');
insert into tags (tag_id,article_id,tag_content) values ('989','1235','Europe');
insert into tags (tag_id,article_id,tag_content) values ('24','1237','art of beer');
insert into tags (tag_id,article_id,tag_content) values ('993','1236','Ewoks');
insert into tags (tag_id,article_id,tag_content) values ('994','1236','Rome');
insert into tags (tag_id,article_id,tag_content) values ('995','1236','Europe');
insert into tags (tag_id,article_id,tag_content) values ('13','1237','conquering hangovers');
insert into tags (tag_id,article_id,tag_content) values ('990','1235','Inventions');
insert into tags (tag_id,article_id,tag_content) values ('991','1235','Harold IX');
insert into tags (tag_id,article_id,tag_content) values ('992','1235','Europe');
Now let's try a select that gets just the main table data ordered by BC/AD
$query = "(select article_id,year,anno_domino,description from articles where anno_domino='BC' order by year desc) union all (select article_id,year,anno_domino,description from articles where anno_domino = 'AD' order by year asc)";
Gives you:
1234 | 143 | BC | ruler XYZ invaded Rome
1237 | 64 | BC | rocknbil pillages brewery
1235 | 294 | AD | the whatchamacallit was invented by Harold IX
1236 | 312 | AD | The Ewoks Sacked Rome
As you see, ordered with BC first, years descending, AD next, years ascending, in perfect chronological order, in spite of how the're entered or the auto increment ID.
Now let's step it up, and get the tags. for a select statement, it would involve multiple lines for each of the main table records which would involve programming anyway to avoid multiple lines, so what I'd probably do here is nest it inside a for loop.
(or, if there's a mySQL statement to concatenate the joined table on one line it eludes me ATM, but concat might do it.)
$output = null;
$result = mysql_query($query);
while ($row=mysql_fetch_array($result)) {
list($article_id,$year,$anno,$descr) = $row;
$tags = null;
$query = "select tag_content from tags where article_id=$article_id";
$res = mysql_query($query);
while ($row=mysql_fetch_array($res)) {
$tags .= '<' . $row['tag_content'] . '> ';
}
$output .= <p>$tags $year $anno $descr</p>";
}
//
echo $output;
The last bit is untested, but it'll work, and give you the same output as in your first post, with the exception they are wrapped in paragraph tags.
The overall assessment, thinking outside the box will pay off in the long run . . .
