[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1102
  • Last Modified:

mysql_field_len() with utf-8 = incorrect field length

Hello,

Can anyone tell me a way to retrieve the correct field length from a field?  What is happening now is when the field is utf-8, mysql_field_len() returns the field length times three.  So say a field has a 20 character limit, it will return 60.  Right now I'm dividing the output by three but I'm guessing the script would not function properly if the table had a different collation.

Any help or alternatives would be appreciated.
0
digital0iced0
Asked:
digital0iced0
  • 4
  • 2
1 Solution
 
cr4ck3rj4ckCommented:
Hey there,

It seems that this is a recorded MySQL problem.

Please see: http://bugs.php.net/bug.php?id=33123

You could get the field data in to a PHP variable and use strlen() as an alternative.

Hope that helps,
CJ
0
 
Ray PaseurCommented:
@digital0iced0: Check this:
http://lists.mysql.com/mysql/183792

Best, ~Ray
0
 
digital0iced0Author Commented:
Thanks for the replies.  The problem is that I'm looking for the maximum allowed field length not the specific length of a string.  I need the 40 from varchar(40).  But like cr4ck3rj4ck mentioned its a bug that it returns the total bytes allowed instead of the character limit.  I need to get this information dynamically depending on the table, in other words I don't want to hard code 40 into the script because its supposed to work with multiple tables.  Can you guys think of an alternative way of achieving this?

Thanks,

Alex
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Ray PaseurCommented:
Yes, I'll show you one...
0
 
Ray PaseurCommented:
Try this.  I think you can get the length from the column type. ~Ray
<?php // RAY_varchar_length.php
// ASSUME DATA BASE CONNECTED AND SELECTED
 
 
// FIND ALL THE TABLE NAMES IN AN ARRAY
function get_my_db_tables()
{
	global $db_connection, $my_dbt_prefix;
 
	$sql_t	= "SHOW TABLES";
	if (!$res_t = mysql_query("$sql_t", $db_connection)) { fatal_query_error($sql_t); }
	if (mysql_num_rows($res_t) == 0)
	{
		return FALSE;
	}
	while ($show_tables	= mysql_fetch_array($res_t))
	{
		$my_tables[]	= $show_tables[0];
	}
	return ($my_tables);
}
 
 
// FUNCTION TO GET COLUMN NAMES AND COLUMN TYPE
function get_my_db_table_columns($table_name)
{
	global $db_connection, $my_dbt_prefix;
 
	$sql_c	= "SHOW COLUMNS FROM $table_name";
	if (!$res_c = mysql_query("$sql_c", $db_connection)) { fatal_query_error($sql_c); }
	if (mysql_num_rows($res_c) == 0)
	{
		return FALSE;
	}
	while ($show_columns = mysql_fetch_assoc($res_c))
	{
		$my_columns[$show_columns["Field"]]	= $show_columns["Type"];
	}
	return ($my_columns);
}
 
 
 
// FIND TABLES
$tables = get_my_db_tables();
 
// FIND COLUMN NAMES AND TYPE
foreach ($tables as $my_table)
{
	$cols = get_my_db_table_columns($my_table);
	var_dump($cols);
}

Open in new window

0
 
digital0iced0Author Commented:
Thanks a lot Ray, you really are a genius :)
0
 
Ray PaseurCommented:
Thanks for your points and the kind words, as well as your many thoughtful contributions to EE.  This is a great question as more and more people use UTF-8 and I'm glad its in our archives.  Best always, ~Ray
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now