how to store an image and a text in a database in mysql

I want to to store recipes 开发者_运维问答in database, the format of the recipes should be a small photo on the top and a text below. Could anyone explain me how the structure should be?

Thanks in advance

I would avoid storing the actual bytes of the image in the database, that's generally a bad idea (See Storing Images in DB - Yea or Nay?). Instead, I would create a simple TEXT field that stores perhaps a Markdown version of your recipe page. This page could include an image tag pointing to your image or just the recipe instructions.

When the image is uploaded create a unique id for it and store it in an images directory.

Store the unique image id in a table with a TEXT field: -

id | image_id | recipe

the id field should be set to auto_increment. You'll probably want a title field.

A populated db would look like: -

id | image_id           | recipe_title  | recipe_body
--------------------------------------------------------------------------------
1  | 218328132192.jpg   | 'Boiled egg'  | 'Put egg in pan and boil for 10 mins'

The image_id can be generated using a hash function or even auto_increment again.

You can store the image in the DB but it is not generally trivial to retrieve and present the image from the DB to the user (It doesn't perform that great either). I would suggest storing the image on some sort of NAS, and storing the location to that image in the database along with the other data for the recipe.

You could use a BLOB for the image data and a VARCHAR for the text. Perhaps a VARCHAR name field or INT id field for the identifier/key if you feel so inclined.

You could easily go for a 3-field table

| id | image | text |
---------------------
|    |       |      |

Id of type INT, AUTO-INCREMENT, PK, image could be BLOB and text VARCHAR (255) or TEXT if longer. I'm used to save image as a path instead of blob (a VARCHAR field, i.e.), and then print out only this path to the folder where I store the relative image. It takes less resources and less work ;)

Generally the best idea (more managable in the sense that the image and text are separated) in this situation is to store the text in one field in the table, and the image PATH in another as Damien Pirsy suggests. So I am just adding my voice to that. It gives better control not only over the data, but how the data is presented. And in the most database-efficient manner.

Storing the image bytes itself in the database is not very efficient, depending on how big the image is and how many you are retrieving at one time. You have to consider that the contents of such fields is variable and dependant on user actions, hence not terribly safe from a performance point of view. E.G. What if a user inserts a 1MB image?