A problem: Large project, no clue where to start....

[MadCatter101]

I have a huge project in front of me and no clue as to how to begin it. I have a database set up properly (MySQL; PHPAdmin is a GODSEND...), and a page that submits data to the database.

I want to write a page of PHP that:

1) Connects to my database "aimfreak_writersright"
2) Brings up the items in table "stories"
3) Sorts these items by date, in descending order
4) DOESNT DISPLAY EVERY FIELD IN THE TABLE. The fields in that table so far are:
uid (a unique ID, int field, 8 chars long, autoincrease)
authorname (author's name, text field)
authoremail (authors email addy, text field)
title (title of work, text field)
date (text field, 10 characters long, formatted yyyy.mm.dd, i already have a page to submit items into the database)
story (longblob field that contains the story itself).

I want to have this page bring up the title, author, and date of the entry. But that's not all I need!

Where it spits back "Title", I want it to have a link to a separate PHP page (story.php) that displays the title, author, date, and contents of the story. Where it spits back "authorname", I want that also to be a link to a separate page (authors.php) that brings up the titles this author has written (with links to the separate stories), and their email address.

My problem?

I have no clue where to start. I have the code to connect to the database. What would I do to generate dynamic links like that? What code do I need to have for the other 2 pages, story.php and author.php? How do I get the data off the database? Right now I'm using the code

$select = "SELECT * FROM stories ORDER BY date DESC";

should that work? I've already made the code to connect to the database & table, that's not a problem. What's confusing is how to get the data spitted back by the database to generate links to the proper sites... would I have to do something like

<a href=&quot;

http://www.aimfreaks.net/writersright/story.php?title=<?=

$title ?>><?= title ?></a>

to bring up the story by title, from the data pulled off the database? This project just seems large and daunting and confusing; any help that I could get would really be appreciated. Thanks a lot in advance!! --MadCatter--

 

[sleipnir214]

I would use more than one table.

My first table would be &quot;authors&quot;. Each record in the table will have an authorID, author name, author email, and whatever other biographical information you want to include. Only author biographical information here.

My second table would be a &quot;story&quot; table, which includes in each record a storyID, authorID, title, date, and story text. A record's authorID will match the UID column of the record in the &quot;authors&quot; table which holds that story's author's biographical information.

Having the author's record in a separate, related table allows you to have a single writer to be the author of more than one story in the database without duplicating author records, because two or more stories can have the same authorID value. This also gives you the ability to make global changes to author information -- you only have to change the email of a single author record to update the author biographical information for any number of stories.

Besides your existing main script, create two more. The &quot;authors&quot; script will retrieve author information via a provided author UID. The &quot;stories&quot; script will retrive the story text via a provided story UID.


Your main script should then pull out all data from all three tables:

SELECT
*
FROM
stories,
authors
WHERE
stories.authorID = authors.storyID

Your main script will then create a set of lines, constructing two links in each line, one to the author, one to the story:

<A href=stories.php?story_id=[retrieved storyID]>[retrieved title of the story]</a>, by <A href=author.php?author_id=[retrieved authorID]>[retrieved name of the author]</a>

Each &quot;A&quot;-link, in the href attribute part beginning with &quot;?&quot;, will hand to the appropriate script the needed UID just as if you had a GET-method form doing it.

The &quot;stories&quot; script will look in $_GET['story_id'] to find the value which matches the UID column of the record in the &quot;stories&quot; table which contains the story text. It will then retrieve and display that text. It should also provide a static link back to the main page.

The &quot;authors&quot; script will look in $_GET['author_id'] to find the value which matches the the UID column of the record in the &quot;authors&quot; table which contains the correct biographical information. It will then retrieve and display the author's biographical information. It should also provide a static link back to the main page. ______________________________________________________________________
TANSTAAFL!