Images - PROBLEMS

[Mr Chris]

Hi Guys,

This is driving me potty and would really appreciate some help and you guys are always very helpful so would like to ask for some advise please!

I’m building a Content Management system and I’m looking to assign pictures to stories.

Now all images are already uploaded via a separate form in a table called cms_pictures table and are re-sized on the fly.

Now in my two tables:

cms_stories
story_id int(4) No auto_increment
headline varchar(128) latin1_swedish_ci No
body_text text latin1_swedish_ci No

cms_pictures
pic_id int(3) No auto_increment
date_uploaded varchar(200) latin1_swedish_ci No
main_dir varchar(200) latin1_swedish_ci No
main_name varchar(200) latin1_swedish_ci No
thumb_dir varchar(200) latin1_swedish_ci No
thumb_name varchar(200) latin1_swedish_ci No

I need to link these two tables together to assign pictures to stories.

But I am completely stuck!

If you would be kind enough to take a look at:

http://www.slougheaz.org/greenock/news_test/test.php

There is a form for adding data to the cms_stories table with a Javascript prompt at the bottom which searches for images in the cms_pictures table.

What this does is launch a new window which searches for images in the cms_pictures table. So if you search for the word caption it returns results.

Now This is the bit i'm stuck on - what I want to be able to do is click on the image. Then in the background of the page the pic_id of the image from the cms_pictures table is assigned to the cms_stories table so it has a reference to it.

Then I can simpy hit submit and the data for form andthe cms_stories data is saved together with a refence to the image assigned to that story.

Could anyone please advise me:

A) How I can progress further with this from where I am stuck
B) If there is a simpler way anyone can think of assigning pictures to stories and have I been thinking too hard about this.

Many Thanks

Chris

[designationlocutus]

Hmm I think that you have a normalisation decision to make. You can either create a foreign key in either table to make reference to it, or a seperate table to link them.

Ask yourself the questions,

"For each story, can I have a 0 pictures, exactly one picture, or more than one?"

"For each picture, can I have 0 stories, exactly one story, or more than one?"

Once you have the answer to both of those, then you will be able to link them. 

[Mr Chris]

Hi,

Defintely 1 image per story.  I've already normalized my database by making the pictures a seperate entity from the story (ie all picture details are not being held in the cms_story table, but the cms_pictures table) thus re-usability is enabled with pictures.

But I don't see how I can link in the two tables together as you say?

Many Thanks

Chris

[designationlocutus]

If its one image per story, then the relationship would be one-to-one (this means that you must have exactly one picture to every story, no exceptions).

Conventions say that if it is a one-to-one then ideally it should be an attribute of your story table. However, from an OO point of view the picture is a separate object so it should have its own table.

In order to link them you need to add a foreign key to the story table that references an image entry in the image table.

When the SQL is created to access the data, the foreign key is used to link the tables and retrieve your combined data.

[Mr Chris]

Hi,

Thanks I understand where you are coming from with this with your object-oriented approach, but this system will be used by a number of users without any of them being very 'techie', so i'd ideally like to keep the system as simple as possible for those users.

[quote]In order to link them you need to add a foreign key to the story table that references an image entry in the image table.[/quote]

Also with the approach you mention how would that also cope with duplicate names if each is given a reference?

Any futher comments on my problem would be most helpful

Many Thanks

Chris

[designationlocutus]

[quote author=Mr Chris link=topic=100055.msg394454#msg394454 date=1152542991]
Also with the approach you mention how would that also cope with duplicate names if each is given a reference?
[/quote]

It wouldn't make any difference unless your code depends on the name at any point. For example of you listed all 10 picture names then you would need a way for the user to identify them individually.

You could have one million pictures in your images table called image.jpg. If the primary key is different, then each of those entries is unique by it's key. When that key is referenced by a foreign key, it relates to that one picture.

[Mr Chris]

First of all thanks for your help  ;D

[quote]In order to link them you need to add a foreign key to the story table that references an image entry in the image table.[/quote]

I still don't quite get what you mean here.

What I thought you were getting at was for example having a field in my pictures table where I could apply a reference for each image. 

So for example If uploaded a picture in my upload_pictures.php page to my cms_pictures table to a field called 'reference' and called it [b]joebloggs[/b]

So in my add_stories.php page i'd then have a field also called reference where I could add the word [b]joebloggs[/b] again - save that data

Then on my live site echo out the images where cms_stories.reference is equal to cms_pictures.reference?

But how would that be unique as suely I could add another joebloggs? or am I understanding it wrong?

Many Thanks

Chris

[designationlocutus]

No it's in the context of database tables, for example:

[code]
image
-----
imageid
imagetitle
imagepath

story
-----
storyid
storytitle
imageid
[/code]

story.imageid is your reference to the image in your image table. You link them via SQL for example:

[code]
SELECT * FROM story, image WHERE story.imageid = image.imageid
[/code]

That will return the image and story data where both the the reference from the story table and the primary in the image table are the same. It links two tables while keeping them seperate.

[Mr Chris]

Thanks - probably being really thick here, but:

If I upload an image on my [b]upload_image.php[/b] page and it is given an image_id of 12 in the images table

How does my system then know and keep and hold the image_id is 12, so when I go to the other page to Add a story [b]add.php[/b] (a different page) the image_id is reference as 12 in the story table?

[Mr Chris]

Hi designationlocutus,

Just been thinking though what would you do if you wanted to change that image associated with the story?  Surely the only way would be to delete the whole story - enter it again and re-upload a new image?

Thanks

Chris

[designationlocutus]

When you add the story, there will have to be a selection box that selects your image when you upload it.

But since you said that there will be one image to one story then the most obvious solution would be to upload your pic on the same page with your story. Once the form is processed  the story is added with the correct image id.

INSERT INTO IMAGE
get the last image id
INSERT INTO STORY with the image id reference.

Changing the image would be on the edit page of the above. Re-uploading an image would:

- Delete the image on the server and from the database (DELETE FROM)
- Replace it with the one you uploaded (UPDATE)