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:
 
Alpa
Posts: 9
Comment
Upload PDF to MSSQL with PHP - Solved
Reply #9 on : Thu September 02, 2010, 11:50:19
Hi,
I solved the issue for uploading PDF. So the trick is writing this hex data in query. Please dont use quotes ('') in your insert/update query and this will work like a charm.
$sql = "insert into docs(pdfname, pdfdoc) values('$pdfname', $hexdatastring)
Alpa
Posts: 9
Comment
Can be used for PDF?
Reply #8 on : Wed September 01, 2010, 16:50:36
Hi there,

I want to achieve the same thing but not for images, its for PDF. Found this article after spending a whole lot of my time and its the best explanation so far for PHP to SQL Server 'Image' datatype. Have tried your code for PDF but its not working. Can you suggest me a way?
Your any help would be greatly appreciated. Thank you !
xwisdom
Posts: 9
Comment
Re: How To Upload Images to a Microsoft SQL Server with PHP
Reply #7 on : Thu August 19, 2010, 12:14:08
Whoops!

That should be header('Content-Type: image/jpeg');
xwisdom
Posts: 9
Comment
Re: How To Upload Images to a Microsoft SQL Server with PHP
Reply #6 on : Thu August 19, 2010, 12:13:11
Hi Prabeen,

You can do this by setting the header to image/jpeg and then echo the image content to the browser:

header('image/jpeg');
echo $imageContent;
Prabeen Giri
Posts: 9
Comment
Re: How To Upload Images to a Microsoft SQL Server with PHP
Reply #5 on : Mon June 28, 2010, 09:11:29
Can u please tell me how to render the above the image in browser from the data u instered on the database using php ? Please help
sakura
Posts: 9
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: 9
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: 9
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: 9
Comment
great
Reply #1 on : Mon December 29, 2008, 04:46:33
this is great! thanks bro, you saved my day!