Skip navigation links

MySQL Forums :: General :: How to store pictures in Mysql database?


Advanced Search

EXAMPLE: How to store pictures in Mysql database
Posted by: Michael G. Zinner ()
Date: May 30, 2005 12:05PM

1) Download and install MySQL Query Browser

2) Start it and create a connection to your database. Use the "test" as default schema.

3a) Excute the following SQL statement.

CREATE TABLE `test`.`pic` (
`idpic` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`caption` VARCHAR(45) NOT NULL,
`img` LONGBLOB NOT NULL,
PRIMARY KEY(`idpic`)
)
TYPE = InnoDB;

3b) Instead of creating the table via SQL, you can create a table by pressing the right mouse button on the schemata tree and select "Create New Table"

Type "pic" in the Table Name field. Press enter. Press enter again to create the first column named "idpic" (that will be your primary key). Press enter to accept INTEGER as datatype. Enter "caption" to store a name for you picture. Press enter. Type "v" (which will trigger VARCHAR(45)) and press enter. Type "img" and enter. Type "longb" (which will trigger LONGBLOB) and press enter. Click [Apply Changes]. This will show you the SQL statement that will be executed.

Press [Execute] to create the table.

5) Close the table editor or refresh the schemata tree and doubleclick the new generated table in the schemata tree

6) This will generate the SQL statement

SELECT * FROM pic p

Now click the table again to execute it. You can press Ctrl+Enter instead (of course)

7) Now click on the [Edit] button in the action bar at the bottom. Doubleclick the empty caption field. Enter a name for your picture. Press enter.

8) Click the "Open" overlay icon in the left of the img field in the first row. Or you can press the right mousebutton on the field and select "Load Field Content" from the popup menu.

9) Click [Apply Changes] to actually store the new data in the database.

10) Now click the "Magnifyer" overlay icon and if you have inserted a JPG, PNG or BMP image you will see it in the field viewer.

Tip: To be able to insert images that are bigger than 1MB you have to increase the max_allowed_packed option in the server configuration file. You can change that in the Startup Variables section in the MySQL Administrator on the Networking page.

Michael Zinner, Team Lead, Developer Tools
MySQL AB, www.mysql.com

Are you MySQL certified? www.mysql.com/certification

Options: ReplyQuote


Subject Written By Posted
How to store pictures in Mysql database? Raja Janardan 03/06/2005 11:56PM
Re: How to store pictures in Mysql database? Aye Mya Thida Kyaw 05/26/2005 02:05AM
Re: How to store pictures in Mysql database? gaoagong 05/30/2005 01:39AM
EXAMPLE: How to store pictures in Mysql database Michael G. Zinner 05/30/2005 12:05PM
Re: EXAMPLE: How to store pictures in Mysql database Kenneth Boehme 06/09/2005 02:09AM
Re: EXAMPLE: How to store pictures in Mysql database James Zapico 06/20/2005 06:03PM
Re: EXAMPLE: How to store pictures in Mysql database James Zapico 06/20/2005 06:24PM
Re: EXAMPLE: How to store pictures in Mysql database matthukew young 07/18/2005 02:50PM
Re: EXAMPLE: How to store pictures in Mysql database James Zapico 12/07/2005 09:18PM
Re: EXAMPLE: How to store pictures in Mysql database matt vruggink 12/08/2005 10:07PM
Re: EXAMPLE: How to store pictures in Mysql database bagya raj 04/06/2009 03:16AM
Re: Darren Roberts 07/31/2008 03:29PM
Re: How to store pictures in Mysql database? lina gialama 07/29/2005 03:35AM
Re: How to store pictures in Mysql database? Andrew Gilfrin 08/01/2005 07:32AM
Re: How to store pictures in Mysql database? Badreddine Louachama 07/19/2007 06:43AM
Re: How to store pictures in Mysql database? bagya raj 04/06/2009 01:59AM
Re: How to store pictures in Mysql database? bagya raj 04/06/2009 02:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.