A strange little databasing question

madcatter

First off, thanks for everyone who helped me out on the last issue. Here's my latest brain-melting problem: I'm working on a short-story forum for people to come and submit their stories for other people to read.

I want to write a page of PHP code 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

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--

barand

The first thing I'd do is create a separate table for authors

author_id,
author_name,
author_email

and store the id in your stories table. That way you only hold these once which

makes it easy to change data like email
avoids mis-spelling of names which would prevent searches by name
[/list=a]

In your href links, you need to name the variables that you are passing, and use the id of the story or the author eg

<a href="http://www.aimfreaks.net/writersright/story.php?storyid=<?= $uid ?>><?= $title ?></a>

Apart from that, you seem to be on the right track.

hth

fandelem

Barand has the right idea.

You should have:

a stories table with a one-way link to the
authors table.

this will allow you to have many stories for one author. that way you can simply do a "select * from stories where author_id = X" and get all stories by a certain author.

this also helps you solve the problem of linking to an author page. when they are reading the story, the link to the 'authors.php' will simply have an author_id in it and 'authors.php' will do a lookup on this in your authors table.

cheers,

madcatter

Thank you both for your input. My next question is, how should I set up my table? Should it look like:

STORIES
Fields: storyid (unique), title, date, story, authorid

AUTHORS
Fields: authorid, authorname, authoremail

? If I set up stories.php to connect to the database, and then have a function that does

SELECT * FROM stories WHERE storyid=$storyid AND title=$title

will it automatically grab $storyid and $storytitle from the address
(i.e. ../stories.php?storyid=28&title=whatever )? Would it automatically search the AUTHORS table for the appropriate authorid? How would I write the function to output the info into the correct manner? Would I have to do something like

Echo $title
Echo " by "
Echo "<a href="../authors.php?authorname=<? $authorname ?>>$authorname</a>
Echo " written on "
Echo $date
Echo "<p>
Echo $story

? What would I do to output that information? Would accessing stories.php on its own bring up all the stories by all the authors by all the dates?

I'm just kinda confused here....

MadCatter101

PS: thanks for all the input so far, I think I'm on the right track...

barand

For your first query you need

Select s.storyid,s.title, s.datesubmitted, s.authorid, a.authorname
from story s inner join author a on s.authorid = a.authorid.

(NOTE i've used 'datesubmitted' instead of 'date' - it's a good idea not to use field names that could be misinterpretted by SQL. (eg do not user 'order' or 'group' etc as these are reserved words in the SQL language)

When a user clicks your author link

<a href="story.php?authorid=<?=$authorid?>"><?=$authorname?></a>

your next query is

select title, datesubmitted from story where authorid=$authorid

to get a list of titles by the selected author