This should be a simple one...

[Anon]

I'm trying to create a really simple system for editing content on my site by pulling the content from my MYSQL database.

Each record in my "content" table has five fields:
1) story_id (primary key, auto_increment. Will keep track of the stories over the life of the site)
2) print_orderID (the order in which the story appears on the page)
3) story_title
4) story_body
5) edition_id (this distinguishes whether the story goes in the current issue and/or the order that it is placed in the archives)

So with each new edition I publish, I will create a new edition_id. This means that the largest number would be the latest edition.

I'm very new to PHP, and I don't know how to tell PHP to select only the records that have the largest number in the edition_id field and print it to the new edition page.
(I also have an edition table that joins with the content table on the edition_id. This edition table holds the edition_id and date.

This is what I have so far...

<?

$dbh=mysql_connect("localhost","username","password");

mysql_select_db("DatabaseName",$dbh);

$query="

select distinct

content.story_id as story_id,

content.print_orderID as order_id

content.story_title as title,

content.story_body as body,

content.edition_id as edition,

edition.date as date

from

content,

edition

where (

content.edition_id=edition.edition_id

and edition.edition_id = The latest edition--or all of the records in the edition_id field that have the largest number (How do I do this ???)

Then I want to be able to layout the stories in different table cells for positioning.

So I need to be able to say...

First table cell:
Print story_title and story_body where order_id=1

Second table cell:
Print story_title and story_body where order_id=2

and so on and so on.

I have no idea how to do this.
I'm sure that my archaic system is probably not the most efficient way to do this either, but I need help !!

Thanks.

[Anon]

Andrew, this isn't a PHP issue, it's an SQL issue. You'll get better advice by asking in the database forum.

I'm not an SQL wizard, but it appears that you need to store the current issue ID somewhere -- and it could be in a table, but not necessarily -- and select for that when you query CONTENT, and order by PRINT_ORDERID.

To put the results on your page, write a function to do your preferred formatting, and pass it the results of repeated calls to mysql_fetch_object() (see docs for same).

One omission that I noticed is that your CONTENT table does not have any provision for story status flags. Adding a status field would let you support workflow -- make status=0 mean "publish this" and other values mean "ready for editing," "edited but not released," et cetera.