link uploaded files with news post

[Static42]

I have made a news script where you post news and it gets displayed on the website. Inside that script is an option to attach and upload files. When I click Submit, the attached files get uploaded to the server. However, I would like those attached files to be linked with its associated news post.

For example, on the website, the news post would be displayed, and then right underneath it would be the attached files.

http://img.photobucket.com/albums/v91/SableyeRULES/uploaded.png

Something like the image above. I just don't know how to do that. I'm hoping someone could explain the steps I would have to take to achieve this.

[mitya]

1) What are you uploading your news to - a DB or a text file?

2) Where are you saving any uploaded attachments - in a DB (as blobs) or as files on your server?

[Static42]

1) What are you uploading your news to - a DB or a text file?

The news is getting saved inside a database.

2) Where are you saving any uploaded attachments - in a DB (as blobs) or as files on your server?

The files are uploaded into a folder on the server.

[mitya]

Simple, then. Add a column to the table in which your news is stored, called "attachments". In here store the saved locations of all attachments related to this story. So a typical entry in your news table might read...

story_id = 1
headline = some headline here
story = the full story here
when = 2008-07-12 12:00:00
attachments someFile.doc,someOtherFile.doc

You said a story could have multiple attachments, so I've comma-separated the names above. Then, when you come to output your story, you simply read this column.

$thisStoryQuery = mysql_query("select headline, when, attachments from news where news_id=1");

//what attachment(s) did the query find?
$thisStoryAttachments = explode(",", mysql_result($thisStoryQuery, 0, 'attachments');

//now output links to the file(s) found
for ($h=0; $h<count($thisStoryAttachments); $h++) {
echo "<a href=newsAttachmentsFolder/".$thisStoryAttachments[$h].">".$thisStoryAttachments."</a>";
}

[Static42]

That makes sense. I do have one question though. How would I comma-separate the attachment file names when they get added to the database? What would the column type be (varchar, text)?

[mitya]

Column type: I'd use 'tinytext' unless you're planning stories having loads and loads of attachments, in which case use something that can hold more bytes such as 'text'.

How to comma separate: This assumes you've received and saved the form attachment(s) and now you have the name(s) of the attachment(s) in an array, thus...

$uploadedAttachments = array("someDoc.doc", "anotherDoc.doc", "yetAnotherDoc.pdf");

Now, the easiest way to turn this array into a com-sep string is to use implode(), using a comma as the glue.

//prepare the attachments part of the mysql query, turning the array into a com-sep var
$attachmentNames = implode(",", $uploadedAttachments);

//prepare rest of query
$query = "insert into news (null, \"$_POST['headline']\", \"$_POST['story']\", \"2008-07-12 12:00:00\", \"$attachmentNames\")";

//run query
mysql_query($query);

p.s. note there was a mistake in my post before this one; the penultimate line should have been:

echo "<a href=newsAttachmentsFolder/".$thisStoryAttachments[$h].">".$thisStoryAttachments[$h]."</a>";

[Static42]

Thank you. That works perfectly. Had to made a few modifications so that it would work on my script, but that went smoothly. :)

One final question. Take a look at my script below. It displays the news on the website.


$news_query = mysql_query("SELECT * FROM news ORDER BY id DESC");
while ($row = mysql_fetch_array($news_query))
{
$news[] = $row;
$smarty->assign('news', $news);
$query = mysql_query("SELECT * FROM news");
$newsAttachments = explode(',', mysql_result($query, 0, 'attachments'));
$smarty->assign('newsAttachments', $newsAttachments);
}


Yeah I use Smarty Template Engine for my website...

Anyways, that script above basically selects all my news entries and displays them on the web page. I'm not sure if I have that set up right. My news gets displayed, but my attachments do not. And I know that my HTML/Smarty code works because the attachments displayed when I did: $query = mysql_query("SELECT * FROM news WHERE id = '76'");
But of course that's not what I want. I hope this makes sense.

[mitya]

For some reason you're trying to select the attachments from the database twice - once in line 1, again on line 6. Line 1 is the only query you need since it fetches everything (*), including the attachments column.


$news_query = mysql_query("SELECT * FROM news ORDER BY id DESC");
while ($row = mysql_fetch_array($news_query)) {
$newsAttachments = explode(',', mysql_result($news_query, 0, 'attachments')
$news[] = $row;
$smarty->assign('news', $news);
$smarty->assign('newsAttachments', $newsAttachments);
}

I have fixed this issue but can't be sure of the $smarty-> lines since I don't use that extension. If you can't get it to work I'll write you a demo which uses natural PHP, no extension.

[Static42]

If you can't get it to work I'll write you a demo which uses natural PHP, no extension.

Could you please do that? Thanks. :)

[mitya]

The following assumes:

1) the structure of your news table is 5 columns: id, headline, story, when, attachments - modify as required.

2) That you don't show the full stories on this page, rather you click the headline to be taken to a page to read the full story. If I'm wrong, and the full story is displayed on this page, just use "*" in the query, as you did previously, and remove the link I put on the headline.

//fetch data
$stories = mysql_query("select id, headline, substring(story, 1, 200) as story, attachments, when from news order by id desc");

//start output, with a loop
for ($g=0; $g<mysql_num_rows($stories); $g++) {
echo "<p><a href=read_story.php?id=".mysql_result($stories, $g, 'id')."><b>".mysql_result($stories, $g, 'headline')."</b></a></p>";
echo "<p>".mysql_result($stories, $g, 'story')."...</p>";

//work out how many attachments this story has and output links to these in a loop
$attachments = explode(",", mysql_result($stories, $g, 'attachments');
echo "<p>This story's attachment(s):";
for ($h; $h<count($attachments); $h++) {
echo "<br>- <a href=newsAttachments/$attachments[$h]>$attachments[$h]</a>";
}
}