Determining Database Field Lengths

Here's the scenario: You've set up your database and now you're building your web page to display the information. You have a text field that stores a city name. In the database you've specified the maximum length to 50 characters. In HTML, you add on your maxlength attribute and specify it as 50. An easier way would be to have it automatically "know" what the length was.

In ASP, you can do this using the DefinedSize property of the field. For example, <input type="text" name="myfield" value="<%=rs("fieldname")%>" maxlength="<%=rs("fieldname").DefinedSize%>" >

You can do the same thing in PHP but it's a little more complicated. Here's an example: $length = mysql_field_len($rs, 0); The problem here is that you can only pull out one field length at a time and only using an index. The index being the order that the field names are specified in the SELECT. Therefore, it might be easier to loop through and copy all the field names and their lengths into an associative array.

Published April 16, 2005 · Updated September 17, 2005
Categorized as Other
Short URL: https://snook.ca/s/354

Conversation

5 Comments · RSS feed
Scott said on April 17, 2005

That's an interesting technique. I like to make my database fields a little larger than the maxlength, in case any quotes or other characters need to be escaped for one reason or another. You could always pull the field size out, subtract some number (say 10 characters) from it, and print that as your field size.

Jonathan Snook said on April 17, 2005

I was surprised that escaping would have any effect on field length so I did a quick test and confirmed that it doesn't. At least it doesn't in MySQL 4.1.9 or MS SQL Server 2000. I'd suspect all database servers to be similar.

Therefore '123456789''' would be 10 characters long and not 11.

Julian said on April 18, 2005

You'll have to check if the text field is not too long after submitting the form. Everyone can easily remove maxlenghts.

Jonathan said on April 18, 2005

In no way should this post be construed as a way of avoiding proper form validation. But as in the maxlength example, you could use the DefinedSize property or the mysql_field_len function to assist in form validation.

Ben Kennedy said on April 18, 2005

I suppose you could also issue a 'DESCRIBE' query and then parse the results in the Field and Type columns. Although that's kind of hackish.

Sorry, comments are closed for this post. If you have any further questions or comments, feel free to send them to me directly.

Want to learn about scaling CSS for large projects?

I'm available for full and half-day workshops on scalable CSS architecture. I can provide on-site training for your team. Interested?
Get in touch.