mysql_field_len()

[jowatkins]

Hi,

I have the following bit of code that I want to use to dynamically check the legth of an input against the allowed legth for that field in the database.

function check_field_length($table)
{
	$field_result = mysql_query ("SELECT * FROM $table");

foreach ($_POST as $key => $value)
{
	$max = mysql_field_len($field_result, $key);
	$length = strlen($key);
	//echo "$key, $value, $max, $length";
	if ($length > $max) 
	{
		echo "<p><font color=\"#ff0000\">PROBLEM</font> $key must be no greater than $max characters.";
		echo "<br/>Please go back and correct.</p>";
		//exit;
	}
}
}

The problem seems to lie with the mysql_field_len function which keep returning the length of the index field. Can anyone see what the problem might be?

Cheers, Jo

[barand]

$max = mysql_field_len($field_result, $key);
$length = strlen($key);

Not knowing what your post variables contain, I can only guess but it looks as though

$max = mysql_field_len($field_result, $key);
$length = strlen($val);

may be what you need

If the post,s come from input fields on a html form, you could set the length constraints there

eg <input type="text" name="surname" maxlength="15">

[jowatkins]

Hi Thanks, but it doesn't work. I think it might be something to do with the query. If I change it to:

$field_result = mysql_query ("SHOW columns FROM $table");

All field lengths are returned as 64??

[barand]

So are you saying that if you run

$res = mysql_query ("show columns from $table");
while ($row = mysql_fetch_row($res)) {
	echo "$row[0] $row[1]<br>";
}

then $row[1] on every line says something like varchar(64) ?

If that is the case you need to look at your table definition (unless, of course, they should all be 64 chars in size).

[jowatkins]

for this table in particular, the field types go something like INT(4), VARCHAR(20), VARCHAR(20), VARCHAR(15) etc. there is no 64??

[barand]

After some experimenting eith mysql_field_len I've come to the following conclusions

1. it wants the field as a numeric offset

2. Using a fieldname always returns the length of field 0

3. It is only going to be useful for string fields, it won't check a max integer size
   [/list=1]

   Given these constraints, the code should work.

[jowatkins]

Thank you, Thank you, Thank you, you've stopped me from going mad.

This function might be of use to someone else, so here's the final version. It'll go through each form posted and dynamically check that no inputs are greater than the field length allowed in the db.

function check_field_length($table)
{
	$field_result = mysql_query ("SELECT * FROM $table");
	$fields = mysql_list_fields("PDS", $table);
	//count how many fields in form posted and delete 2 for buttons
	$numposts = count($_POST);
	$actualposts = $numposts-2;
	$i=1;
	foreach ($_POST as $key => $value)
  {
		if ($i <= $actualposts) 
		{
			$type = mysql_field_type($fields, $i);
			if ($type == "string") 
			{
			  $max = mysql_field_len($fields, $i);
      	$length = strlen($value);
      	//echo "$key, max = $max, length=$length<br/>";
      	if ($length > $max) 
      	{
          echo "<p><font color=\"#ff0000\">PROBLEM</font> $key must be no greater than $max characters.";
          echo "<br/>Please go back and correct.</p>";
          exit;
      	}
			}			
		}
		$i++;
  }
}