[SOLVED] MSSQL / PHP limited to 255 characters

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mksolutions
Forum Newbie
Posts: 2
Joined: Fri Nov 28, 2003 8:42 am

[SOLVED] MSSQL / PHP limited to 255 characters

Post by mksolutions »

I am using a PHP query to select data from a database. The entire string is in the database, I can insert the whole thing fine. But I am limited to 255 characters when selecting from the database. I have found other forum threads regarding this, and it was found the be the MSSQL/PHP connecting peice. But, these forum threads are a couple years old. Has there been an update? Is there any possible workaround? Opinions? Suggestions?

Thanks for the help!

Matthew
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

found this from : http://www.phpe.net/manual/function.mssql-query.php

may be just what you are looking for.
If you have difficulty using mssql_query to retrieve *long* text values stored as VARCHAR/CHAR data on your SQL server, try the following.
[This is different than retrieving beyond the 4096 character SQL TEXT data type problem (with resolution) mentioned above.]

Specifically, retrieving text values longer than 256 characters can be a problem.

For example, if you have:

SQL table "WebComments": UserID [int], UserComments [varchar(4000)]
with the data: 100, 'blah blah blah blah...going on for 3000 characters..."

Unless you're lucky, when you execute the following code:
[...]
$SQL="select UserID, UserComments from WebComments where UserID=100";
$Result=mssql_query($SQL, $LinkID);
$Data=mssql_fetch_array($Result);
echo $Data["UserComments"];

only the first 255-ish characters of the UserComments field value will be displayed.

To get around this problem, you can change your database to use SQL TEXT data types instead, but that would be a pain.

To get around this without changing your database, change your initial query to:

$SQL="select UserID, convert(text,UserComments) as UserComments from WebComments where UserID=100";

and keep the rest of the PHP code the same. Now when you execute the code, the entire UserComments field value should be displayed on your screen.

The length of the data displayed should only be limited by your SQL connection's TEXTSIZE value (that defaults to 4096 characters on SQL 7.0) as indicated in the above message regarding surpassing the 4096 character limit for SQL TEXT fields.

On a related note, if you're testing this type of thing using the SQL Query Analyzer, by default, the Query Analyzer will only show you up to 256 characters per field at a time. You may not notice any differences in your testing unless you change the Query Analyzer's display to > 256 characters. Look under Query/Current Connection Options.../Advanced/Maximum characters per column off the Menu Bar to change this value.

edit :

also good to note is :

http://www.phpbuilder.com/lists/php-win ... 2/0116.php
mksolutions
Forum Newbie
Posts: 2
Joined: Fri Nov 28, 2003 8:42 am

Post by mksolutions »

Something funny.....I found both of those articles before...and I beleive I tried them without any luck before. Never the less....adding this:

convert(text,UserComments) as UserComments

worked this time. Thank you, thank you, thank you! This has been bug that has driven me crazy. Thanks for the sanity back! :)

Matthew
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

glad to have helped :)
Post Reply