Store pdf in mysql

Question

How to save a PDF document in a field in mysql?

I currently have a list of clients, and each client has a certificate with their account information that they can provide to other companies to prove that they are our customers. Currently, their certificate is exported in pdf format and sent by e-mail to someone here at work (the client also receives a physical copy), and this person’s mailbox is filled with these emails. I would rather just have it in the client record - allow access to it through the client file in our internal CRM.

I considered placing PDF files in a folder and saving their location as varchar in the client record, but if the PDF files are moved / deleted / etc. then we are driving.

I understand that blob or mediumblob is the type of field that I would use to store it, but I don’t know a bit about that. I'm not sure how to store something like this in a field (what type of C # to transfer it to), and then how to get it and open it using a PDF reader.

Answer 1

Put it in the database, but the blob data type probably won't cut it. The average value is usually sufficient.

Mysql Data Types

BLOB, TEXT L + 2 bytes, where L < 216 MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 224 LONGBLOB, LONGTEXT L + 4 bytes, where L < 232 

I have used this several times with very good results. Be sure to save the file size as it makes it easier to extract it. Not sure if this applies to C # like PHP.

If you use prepared statements with parameters, the data will be automatically shielded by AFAIK.

Also, I see no real reason why the database itself will be slow when this data type is stored in it. The main bottleneck will be data transfer. Also, mysql sometimes limits the maximum length of requests and responses in particular.

As soon as you run it, it is pretty neat, especially when working with a large number of small files. For a small number of large files, this approach does not make sense; it is better to use some backup system to process moved / deleted files.

Answer 2

http://www.phpriot.com/articles/images-in-mysql - a good tutorial with some background information and implementation of image storage in MySQL

Answer 3

Honestly, I think that going with links instead of actually inserting the file into the database is the best way. This will make the database very slow and there will be more problems than its value.

I would upload the files to the specified folder, for example, “certificates”, and make the certificate names sent with the client number, so they are easy to find and edit, delete, etc. I have seen people store images in databases, but even this is not recommended.

If you need a method, be sure to check out this article:

http://www.wellho.net/mouth/1001_-pdf-files-upload-via-PHP-store-in-MySQL-retrieve.html

It explains how to store and extract .pdf files in the mySQL database.

Good luck

Answer 4

I considered placing PDF files in a folder and saving their location as a varchar in a client record, but if the PDF files are moved / deleted / etc. then we are driving.

This is the approach I would take. Then, using some logic, perhaps some kind of BPEL material - detects that any of the files moves / deletes and launches a trigger in your database to correctly update the location / delete the location.