Categories, Post but not publish, post images

cmccomas

Hey,

#1) I have create a database (news) to add/edit/update news and post it on my site. What I was wondering about doing was splitting it up into two different categories (or more) of news. For example's sake, we'll use football and basketball. I have the following fields for adding news (headline, author, byline [250 words or less introduction], and article). I was wanting my main page to post the most recent of either football or basketball, and then I can seperate the links below as one table for football and the other for basketball. Also, I was wanting a basketball index page and a football index page with only the latest from each category.

#2) I'm working on authentication right now, so I can assign username/passwords to my admin section for writers. I was curious about coding to allow certain people to type up a news story, but it must be approved by me before actually posted on the site.

#3) I have a roster table in my database for players on a teams roster, including name, number, height, weight and I want to include the players headshot. I was curious about the best way to do this, as far as uploading, saving (save as a blob?), etc.

#4) What is the command to add a field to a table?

Thanks for any help you can offer!

Pig

#1 just create a extra column for the category. If you are going to be doing more than 2 types, I would create a table of category names and ids.

Then all you have to do is filter by type in your query.

#2 Just give them a form that is authenticated (lots of articles on this, so look around), but have the article flagged for review. The easiest way to do this is to create a column in the articles table name approved. when it is inserted, make the value=2, and then you can query to find all the stories with that value. Once you have reviewed them, change it to 1.

#3 create a directory for them, and store the absolute url for hte images in your datbase. DO NOT store the images themselves in your db.

#4 read the manual.

cmccomas

#1 just create a extra column for the category. If you are going to be doing more than 2 types, I would create a table of category names and ids.

So, if I do five. I would need a new table with 2 fields (name and id). Then what? Five more tables for each category?

Then, when trying to draw the stories to the main page, where I want the most recent what do I need to change my query too?

Right now, I'm using:

$query =  "SELECT headline, byline, author, id FROM story ORDER BY timestamp DESC LIMIT 0, 1";

#2 Just give them a form that is authenticated (lots of articles on this, so look around), but have the article flagged for review. The easiest way to do this is to create a column in the articles table name approved. when it is inserted, make the value=2, and then you can query to find all the stories with that value. Once you have reviewed them, change it to 1.

Never thought of that. How would this workin the query, so that it only returns items marked '1' (or '2')?

#3 create a directory for them, and store the absolute url for hte images in your datbase. DO NOT store the images themselves in your db.

So, http://www.mysite/pictures/pic1.gif? What would the code be?

<font class="smallText"><?php echo $row->image; ?></font>

#4 read the manual.

I did, couldn't find it.

THANKS!

cmccomas

Found it. Still need help on 1-3.

cmccomas

Can I somehow set the path to the pictures, if I put all my pictures in http://www.mysite.com/pictures/ can I set it to just add pic1.gif in the input box and it understand?

Pig

OK,

#1 the column in your story table is just a column of id's. the category table would have 2 columns, id's and names. each category would be a different line.

To filter, simply add an item in the Where clause: ...WHERE category='2'... If you don't want to filter, then just leave it as is.

#2 to get hte unapproved stories, you would simply run a query with that in the where clause: ... WHERE status='2'...

To make it be unapproved, just make the default for that column be "2", or add that to the insert statement.

#3 put that in your database, then when you need to use the url, you would just do something like:
<img src=\"$row[image_url]\">

Pig

yeah, you can do that too. Just as long as it is all consistent. Then you could just put the rest of the url in the <img> tag.

cmccomas

So, would this be right?

$query =  "SELECT headline, byline, author, id category FROM story WHERE category='2' ORDER BY timestamp DESC LIMIT 0, 1";

Pig

yeah, assuming you wanted just "basketball" or whatever.

cmccomas

so, i add category as a field, 1 for football and 2 for basketball, so if i want basketball stories that is what i use?

thanks man, you made the so simple!