How To Upload Images to a
Microsoft SQL Server with PHP

How To Upload Images to an SQL Server using PHP and the MSSQL extension

Recently, I was looking for a solution that would enable me to upload an image to a Microsoft SQL server, using the PHP scripting language. Most of the information that I’ve found on the web did not offer a complete solution or explanation as to why the image I had uploaded could not be displayed on a web page.

I finally came across a comment that was posted on the PHP website by Cristiano that gave me the results I was looking for.

In this article I will try to pool all the information that I got from the web to make things a little easier for other developers to easily upload images to an SQL Sever.

Creating a Table with an image column
To store an image or a binary file inside a table, you will have to create a column and assign it a data type of image, varbinary or varbinary(max) as in the case of SQL Server 2005. These column types are required for binary data storage.

Here’s an example of a script that will create a database called ImageDB and a table called MyPhotos.


CREATE DATABASE ImageDB
GO

USE ImageDB
GO

CREATE TABLE MyPhotos (
	name varchar(50),
	filetype varchar(3),
	photodata varbinary(max)
)
GO

The next step is to set the mssql.textsize and mssql.textlimit parameters inside our php.ini (or php-isapi.ini) file to 2147483647. By default SQL Server will only return the first 4096 bytes of an image or binrary column. Setting these parameters to the maximum value will instruct the server to return up to 2Gb of binary or image data. Note: If you're using IIS you may have to restart the IIS service in order for the new php settings to take effect.

The PHP Code
In order to store the image inside the SQL server we must first convert the image into an hexadecimal string. The following snippet of code will convert the uploaded image: 


// get uploaded file content and convert it to hexadecimal
$tmpFileName = $_FILES['image']['tmp_name'];
$dataString = file_get_contents($tmpFileName);
$arrData = unpack("H*hex", $dataString);
$data = "0x".$arrData['hex'];

I've put together an example showing how to upload and download an image file to and from the SQL server. You can download the example files by clicking on the link below.

Download the PHP Image Upload Example Files. – Make sure you edit the files and change the database connection settings to connection to your SQL Server.

Credits
Many Thanks to Cristiano for putting his comments on the PHP website. Visit http://php.net/manual/en/function.unpack.php#54910

Write a comment

  • Required fields are marked with *.

If you have trouble reading the code, click on the code itself to generate a new random code.
Security Code:
 
sakura
Posts: 4
Comment
Hey my bad =)
Reply #4 on : Mon November 09, 2009, 03:46:03
Sorry i didn't read it well..hehe it can handle up to 2GB. thanks a lot.
You're a genius! =)
sakura
Posts: 4
Comment
size limit question
Reply #3 on : Mon November 09, 2009, 03:42:40
Hi,
what is the maximum file/image size that this uploader can handle? By the way, thanks for sharing this. It helped me a lot.
Anonymous
Posts: 4
Comment
Thanks
Reply #2 on : Sat February 07, 2009, 07:04:27
Found this web page after a couple of days of getting no where and it was just what I needed. I have used this to upload a load of pdf files into a SQL SERVER 2000 database and view them. Many Thanks.
budi
Posts: 4
Comment
great
Reply #1 on : Mon December 29, 2008, 04:46:33
this is great! thanks bro, you saved my day!