What is the best way to link an image with a record in mysql

[AdRock]

I have a news database where the user can add new item of news to the database and would like to be able to add an image that is linked to the news item.

I would like to know the best way I can have an image displayed that is relevant for the news item.

Would the best way to be to create a table to store images or have the images in a folder and use php echo to call the image (this would obviously have to have another field in the news table with the image name)?

I am trying to make it as user friendly as possible as the main user is not very computer literate.

[code]I use this code to display each record
echo "<b>".$code['title']."</b><br>Added on: ".$code['date']."<br>".nl2br($code['content'])."<img src=\"images/jack1.jpg\"><br><br>\n";[/code]

As you can see I need to change the image depending on which news record is displayed

[AndyB]

Usually, I give the images names that include the database record id and avoid having a separate record for the image name, i.e. img name = 20.jpg is the image that belongs to news item 20.  If I have associated thumbnails, I use a name like 20_tn.jpg; if I have multiple images for the same item I name them like 20_1.jpg, 20_2.jpg etc

[AdRock]

i did think of that ages ago but how would the user know what id he was on

[AndyB]

[quote author=AdRock link=topic=102434.msg406445#msg406445 date=1154352647]
i did think of that ages ago but how would the user know what id he was on
[/quote]

User?  If it's a visitor then when news_id=20 is displayed image 20 gets displayed.  If it's the person adding info to the database, the id for the NEXT news story would be used as the id for the image to be uploaded.  You do have an auto-increment id in your database table, don't you?

[AdRock]

it's my dad who the site is for so he's going to add news in a form and i've inluded in the form an upload script so he can upload images to the images folder.

The news table has an id field whjich is set to auto increment.
What I don't understand is how to give the image name the same name as the next news id like you desrcibed.

I did try adding this to my code but it's not displaying a picture.
[code]echo "<b>".$code['title']."</b><br>Added on: ".$code['date']."<br>".nl2br($code['content'])."<img src=\"images/".$code['image'].".jpg\"><br><br>\n";[/code]
I don't know if you can do this.  I have created another field called image and i insert the name of the image without the extension, so i call the picture name, and in theory add the extension afterwards

[AdRock]

Does anyone know why this does not work?
I have checked to make sure that the image exists and that it is in the images folder and it's correct

[code]echo "<b>".$code['title']."</b><br><i>Added on: ".$code['date']."</i><br><br>".nl2br($code['content'])."<img src=\"images/".$code['image'].".jpg\"><br><br>\n";[/code]

I can't think of any other way to display a relevant image to the record  ???

[HeyRay2]

You can use the auto-incremented ID of your last submitted news item for your image filename by doing something like the following:

[code=php:0]
<?php

// The news insert query
$sql = "INSERT INTO news SET title = '".$code['title']."'" //your insert statement, etc...

// Run the query
mysql_query($sql) or die("Error adding news: ".mysql_error());

// Image saving path
$image_path = "path/to/images";

// Get the ID Of the last inserted news item
$last_news_id = mysql_insert_id();

// Get the ID of the last inserted news item for the upload file name
$image_filename = $last_news_id.".jpg";

// Put the image and path together
$image_fullpath = $image_path . $image_filename;

// Save the image
move_uploaded_file($_FILES['image']['tmp_name'], $image_fullpath);

// Update the DB entry
$sql2 = "INSERT INTO news SET image = '$image_filename' WHERE news_id = $last_news_id";
mysql_query($sql2) or die("Error updating image name: ".mysql_error());

?>
[/code]

Also, before we will be able to assist you more with why your line of code isn't working correctly, we need to see the code that you are using to display / add / update your news entries.

;)

[simcoweb]

Hi:

I have a similar need... adding an image reference to a MySQL database and then summoning the image reference into a web page separately. My setup involves a simple portfolio with an image of the website as part of the display. The way mine works is so similar to the 'news' insertion issue that I thought i'd join this thread instead of starting a new one.

My portfolio entries could work the same way where they have an 'id' number assigned. Then, the image could be assigned to that id if i'm understanding correctly. That would match the two up in any case.

My first question would be pertaining to your code about the INSERT statement. I'm a noob so bear with me :)  I'm assuming that the INSERT statement for the image has to be a separate INSERT statement from the data... ? In other words, from the form entry input i'm inserting the results into a database. One of the fields is the 'image' field. When i'm writing the INSERT code I have this:

[code]mysql_query("INSERT INTO portfolio
(site_name, url, description, details, thumbnail) VALUES('$name', '$url', '$descr', '$details', '$image') ") or die(mysql_error());[/code]

The variables are as so:

[code]$name = $_POST['site_name'];
$url = $_POST['url'];
$descr = $_POST['description'];
$details = $_POST['details'];
$image = $_POST['thumbnail'];
$uploadDir = "images/";[/code]

So, basically I complete the form and upon submit it should open the DB and enter the data. However, the 'image' part is confusing to me since I know that the image itself doesn't reside in the DB but the reference to it does. So, I need to assign the reference...?

[HeyRay2]

Uploaded files are contained in the $_FILES superglobal array. If your upload form has this field:

[code]<input type="file" name="thumbnail">[/code]

Then access to the elements of that file upload would be contained in:

[code=php:0]$_FILES['thumbnail'][/code]

You are correct that you are going to "INSERT" the content of the form into a database. Each element in your form that you want it's value preserves will need a field in the database.

You are also correct that the first "INSERT" statement will not include the image reference, because in this case we are relying on the ID number of the database entry for the file name, so we have to wait until that is assigned, then we can "UPDATE" (which I made a mistake on in my code, I apologize) the last entry with this file name, like so:

[code=php:0]
// Update the DB entry
$sql2 = "UPDATE news SET image = '$image_filename' WHERE news_id = $last_news_id";
mysql_query($sql2) or die("Error updating image name: ".mysql_error());
[/code]

This handles the database reference to the file. However you still need to save the file in a place that your webserver has access to, and can be read from when you print out your database entries, which is where the following line comes in:

[code=php:0]
// Save the image
move_uploaded_file($_FILES['thumbnail']['tmp_name'], $image_fullpath);
[/code]

This take the uploaded file from it's temporary location and saves it on your webserver in a folder you specify. You can then reference that file by pointing to that path and filename when you print our your entries.