mysql - 从 MySQL 列获取字段长度

Question

我找到了这个SQL statement StackOverFlow 上其他地方我修改了供我使用。

SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'tblContent' AND COLUMN_NAME = 'ContentTitle' OR COLUMN_NAME = 'ContentImageCaption'

这有效并显示两个字段的最大字符长度。

我想将列名称的长度放入网络表单中。例如:

<input name="ContentTitle" type="text" value="<%=(rsContent.Fields.Item("ContentTitle").Value)%>" maxlength="40">
<p>Maximum Characters Allowed: 40</p>

使用 CHARACTER_MAXIMUM_LENGTH 中的长度填写 maxlength 标记以及下面语句中允许的总字符数。

我希望这是有道理的。

Answer 1

我想您可以将列名称和字段长度读取到数组中，然后根据该数组生成表单字段...

dim SQL, rs
dim Flds, f
SQL = "SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH " _
    & "FROM information_schema.columns " _
    & "WHERE table_schema = DATABASE() AND table_name = 'tblContent' " _
    & "AND COLUMN_NAME = 'ContentTitle' OR COLUMN_NAME = 'ContentImageCaption'"
Set rs = Server.Createobject("ADODB.Recordset")
rs.Open SQL, "my_connection_string",1,2
If Not rs.EOF Then Flds = rs.GetRows
rs.Close
Set rs = Nothing
'... open rsContent, write out beginning of form, etc. ...
For f = 0 to UBound(Flds,2)
   Response.Write "<p>" & Flds(0,f) & ": <input type='text' name='" & Flds(0,f) & "'"
   Response.Write " maxlength='" & Flds(1,f) & "'"
   Response.Write " value='" & rsContent("ContentTitle") & "'>"
   Response.Write "<span class='instructions'>Maximum Characters Allowed: "
   Response.Write Flds(1,f) & "</span></p>"
Next
'... close rsContent, write out rest of form (e.g. a submit button), etc. ...

但是，实际上表架构不太可能包含表单所需的全部信息。 (例如，人类可读的字段标签就很好。)因此，问问自己:表模式真正改变的频率是多少？手动将信息写入表单不是更好吗？

如果您想对多个表使用相同的表单，那么某种自动生成方法可能会有意义。不过，我仍然会手动设置“表单定义”数组。

dim F(2,3) ' (i,0) = field name, (i,1) = max length, (i,2) = size, (i,3) = label
F(0,0) = 2 ' = number of fields
F(1,0) = "ContentTitle" : F(1,1) = 40 : F(1,2) = 25 : F(1,3) = "Title" 
F(2,0) = "ImageCaption" : F(2,1) = 30 : F(2,2) = 20 : F(2,3) = "Caption"