Need Help with Table Structure

[SalientAnimal]

Hi Guys/Gals,

 

I have a database that curerntly has 4 tables in it that are all linked to each other with a refernce number. I would like to create another table, one that will store all my attachments. I have never done nor create a table that stores any kind of attachments and need some advice in how to go about with creating the table:

What fields do have to have in the table?

What is the correct structure for each field in the table?

I need to have the reference number also saved in this table as it is my unique identifier.

How do I then save the file into the database using the attachment function from a PHP form?

 

Thanks.

 

[nibbrwebdevelopment]

you might want to employ a developer to do this for you. it is a little much to explain.

[SalientAnimal]

I know how to do the coding and the structuring of the tables.

 

I just need to know:

What must the table look like?

Are there any other requirements that I need to make to the webserver/database to handle attachments, if so, what?

 

[Jessica]

The table needs to contain all the data that you think is relevant.

 

Files should not be stored in databases, but in the filesystem. You would store the location of the file in your table.

[SalientAnimal]

The table needs to contain all the data that you think is relevant.

 

Files should not be stored in databases, but in the filesystem. You would store the location of the file in your table.

 

Hi Jesirose,

 

Can you maybe give me some guidance of how to do all this? Keep in mind I would need to add an "Attach Document" field to my form.

 

Thanks

[smoseley]

Depends if you want to store your attachments in the filesystem:

CREATE TABLE attachments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    reference_number INT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) TYPE=InnoDB;
/* This may have to be a foreign key reference */
ALTER TABLE attachments ADD INDEX (reference_number);

 

Or in your database:

CREATE TABLE attachments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    reference_number INT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_data BLOB NOT NULL,
    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) TYPE=InnoDB;
/* This may have to be a foreign key reference */
ALTER TABLE attachments ADD INDEX (reference_number);

[smoseley]

Files should not be stored in databases, but in the filesystem. You would store the location of the file in your table.

Not true.  Files can be stored in a DB just fine, and there are legitimate reasons for doing so.

[Jessica]

Just because you CAN do something doesn't make it a good idea.

[smoseley]

Just because you CAN do something doesn't make it a good idea.

And per the second half of my response, there are many cases in which it is a good idea.