PHP can work with a large number of database systems, most common of which is MySQL. However, SQL Server is a very poweful alternative to MySQL, and well worth considering for larger projects.
However, settings for the mssql extension in PHP can easily be overlooked, resulting in strange behaviour. One of the most unusual problems I encountered on a recent project was that every time text was pulled from a column of type "text", it was truncated at around 4000 characters.
If you are experiencing a similar problem, there are a few ways to work around it.
You need to increase the maximum size of a text column to be returned from SQL Server by PHP. You can do this with a simple SQL query:
SET TEXTSIZE 2147483647
Which you can run with the following PHP (best run just after you make a connection).
mssql_query("SET TEXTSIZE 2147483647");
A better way to work around the issue is to change the "textlimit" and "textsize" settings within php.ini, like so:
mssql.textlimit = 2147483647
mssql.textsize = 2147483647
In the above examples, I have used "2147483647" (number of bytes) for the value. You don't need to set it this high necessarily, however there is no real harm in doing so.
And why does this not happen with ASP? When you use Microsoft's SQL Server ODBC driver and OLE DB Provider, textsize is automatically set to 2147483647 when a connection is made.
13 Comments
after that also i was not increasing the limit . then what can i do to increase the limit from 4 kb
#1, manicka prakash, India, 28 September 2005. Reply to this.
manicka if u dont have a text field instead u have a varchar the limit that u still have is 255, attemp to do a cast.
myvarchar varchar(4000)
$sql="select cast (myvarcharfield as text) from mytable";
mssql_queyr($sql);
#2, JORGE OROZCO, Mexico, 19 July 2006. Reply to this.
You must edit C:\WINDOWS\php.ini and uncomment the following two lines:
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 2147483647
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 2147483647
and change them to:
; Valid range 0 - 2147483647. Default = 4096.
mssql.textlimit = 2147483647
; Valid range 0 - 2147483647. Default = 4096.
mssql.textsize = 2147483647
#3, JJ, United States, 5 September 2006. Reply to this.
I found this didn't make a difference with my ms sql database. Neither did changing to datatypes to text as this gave issues with other pages that were trying to update the same tables.
I managed to eliminate the 255 limit by setting up an odbc connection to the database.
First you have to set up an odbc connection to the database on the server that the db exists on, I did this using IIS. In this example the odbc when set up was given the name: odbcname.
Once you have created this on the server you need the following code(tweak to suit) in the php page/s. (All other code, for example layout and display, obviously remain the same.)
#connect to db
$conn = odbc_connect('odbcname','username','password');
if(!$conn) {exit("Err:Conn"); }
#Query
$sql = "select * from table";
#execute
$result = odbc_exec($conn, $sql);
if( !$result )
{
exit ("Could not execute Query");
}
#data retrieve
$rowodbc = odbc_fetch_array($result);
#create variables from data
$variable =. $rowodbc["column_name"];
#close connection
odbc_close( $conn );
P.S I only had to change the connection type from mssql to odbc on pages that were trying to retrieve more than 255 characters, both types will work along side each other.
Hope this helps
#4, The R, United Kingdom, 8 January 2007. Reply to this.
had an issue where mssql.text* settings were ignored by php. explicitly setting the values with ini_set() worked fine.
#5, Eli Burmin, United States, 30 January 2008. Reply to this.
What about truncating the displayed value but keeping the full value intact in the DB?
Thanks & I love JD too!
#6, Zaps, United States, 4 February 2008. Reply to this.
The problem I'm experiencing is that the odbc_fetch_array (using sql server) is truncating the COLUMN name to 31 characters. Has anyone ever experienced this?
#7, Anonymous, United States, 15 April 2008. Reply to this.
I´m getting another problem... the VarChar fields with more than 255 chars cannot be used... so... this very usefull feature are not avaliable...
#8, Ruben Zevallos Jr., Brazil, 30 April 2008. Reply to this.
Yes, I'm experiencing it now...can't figure out why this is happening either.
========================
The problem I'm experiencing is that the odbc_fetch_array (using sql server) is truncating the COLUMN name to 31 characters. Has anyone ever experienced this?
#9, Dave, United States, 18 June 2008. Reply to this.
Thank you dude! This helped me a lot. ;)
#10, Keli, United States, 2 July 2008. Reply to this.
Good Stuff, this was very useful.
#11, KDS, Unknown, 20 April 2009. Reply to this.
I have that problem too - still trying to solve. Only solution so far is to assign aliases in query for the longer fileds and after fetching reassign original column names to the fetched values.
#12, dev(ill), Poland, 13 June 2011. Reply to this.
http://php.net/manual/en/ref.mssql.php
comment from ahribernik would be worked
#13, komkid, 26 August 2011. Reply to this.