Closed Thread Icon

Topic awaiting preservation: mysql_fetch_fields as field_name => max_length array(); (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=31073" title="Pages that link to Topic awaiting preservation: mysql_fetch_fields as field_name =&amp;gt; max_length array(); (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: mysql_fetch_fields as field_name =&gt; max_length array(); <span class="small">(Page 1 of 1)</span>\
 
Hustluz
Bipolar (III) Inmate

From:
Insane since: Jun 2003

 posted posted 06-23-2009 15:00

What i am trying to do is send query to the database that brings back the field names and their max lengths:

these are my database fields:

code:
CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `sUsername` varchar(35) NOT NULL default '',
  `sPassword` varchar(40) NOT NULL default '',
  `sFName` varchar(35) NOT NULL default '',
  `sMName` varchar(35) default NULL,
  `sLName` varchar(35) NOT NULL default '',
  `sAddr1` varchar(40) NOT NULL default '',
  `sAddr2` varchar(35) default NULL,
  `sCity` varchar(35) NOT NULL default '',
  `sState` varchar(35) NOT NULL default '',
  `sPCode` varchar(35) NOT NULL default '',
  `cCountryCode` char(2) NOT NULL default '',
  `sPhone` varchar(30) NOT NULL default '',
  `sEmail` varchar(80) NOT NULL default '',
  `sQuestion` varchar(255) default NULL,
  `sAnswer` varchar(255) NOT NULL default '',
  `dtInserted` timestamp NOT NULL,
  `dtUpdated` datetime default NULL,
  `iAccessLevel` tinyint(4) NOT NULL default '1',
  `is_banned` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;




This is the code that is working pretty good but what confuses me is the max length it returns is not the actual max length created in the database

code:
# select all fields
$field = mysql_query("SELECT * FROM table");

# setting up array to catch values
$db_fields = array(); 

#getting the number of fields
$num_fields = mysql_num_fields($field); 

# setting the array values to table fields
$i = 0;
while ($i < $num_fields) {
	$meta = mysql_fetch_field($field, $i);
	$db_fields[$meta->name] = $meta->max_length;
	$i++;		
}

print_r($db_fields);



the code prints this result

code:
Array ( 
     [id] => 2 [sUsername] => 7 [sPassword] => 40 [sFName] => 6 [sMName] => 8 
     [sLName] => 7 [sAddr1] => 14 [sAddr2] => 6 [sCity] => 10 [sState] => 2 
     [sPCode] => 6 [cCountryCode] => 2 [sPhone] => 12 [sEmail] => 22 [sQuestion] => 19 
     [sAnswer] => 9 [dtInserted] => 19 [dtUpdated] => 19 [iAccessLevel] => 1 
     [is_banned] => 1 
)



Question:
is there a way to manipulate my code to have it bring back the max length settings i set up in my users table? Please help thank you in advance.

(Edited by Hustluz on 06-23-2009 15:34)
Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

 posted posted 06-23-2009 16:01

have a look at the mysql DESCRIBE TABLE statement.
Hustluz
Bipolar (III) Inmate

From:
Insane since: Jun 2003

 posted posted 06-23-2009 16:46
quote:

Tyberius Prime said:

have a look at the mysql DESCRIBE TABLE statement.




thank you very much for your input i had at look at it and im not sure im not nearly as advanced with this as you are because i couldnt figure out a solution to my problem with the information i read.

if it is possible would you be able to offer a solution as to how i can tweak my code or maybe an alternative in order to get my desired results?

Again what i need is the ability to go to my database and get all the field names put them into an array with the field name being the key and the max field length to be the value so that i can check to make sure all my form elements do not exceed their max values without having to manually type all the fields into an array.

sorry for being such a amateur or newbee as some may call it, but your help would be greatly appreciated. thank you.
Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

 posted posted 06-24-2009 10:18

code without checking... I'm busy, but DESCRIBE TABLE get's you all the necessary information queried, then all you need to do is parse that

code:
$query = "DESCRIBE TABLE myTable";
$hRes = mysql_query($query);
$arrFields = array();
while ( $arrRow = mysql_fetch_array($query))
{
  if (strpos($arrRow['Type'],'(') !== false)
 {
  $iMaxSize = substr($arrRow['Type'],strpos($arrRow['Type'],'(') + 1, -1); //don't know if php finally does -1 as a length... cut of last character anyhow. Don't forgot to cast to int..., I'm not doing that right now.
 }
  else
    $iMaxSize = false;
  $arrFields[$arrRow['Field']] = $iMaxSize;
}



So long,

->Tyberius Prime
Hustluz
Bipolar (III) Inmate

From:
Insane since: Jun 2003

 posted posted 06-25-2009 00:21

Thank you I'll use that as my starting point and try to make it work for me hopefully I get it right. Thanks again.
Hustluz
Bipolar (III) Inmate

From:
Insane since: Jun 2003

 posted posted 06-27-2009 23:42

Hey thanks again for the starting point you gave me. I am happy to say i came up with a solution. If anyone is wondering what i came up with here it is:

once again my table structure is:

code:
DROP TABLE IF EXISTS `table`;
CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `sUsername` varchar(35) NOT NULL default '',
  `sPassword` varchar(40) NOT NULL default '',
  `sFName` varchar(35) NOT NULL default '',
  `sMName` varchar(35) default NULL,
  `sLName` varchar(35) NOT NULL default '',
  `sAddr1` varchar(40) NOT NULL default '',
  `sAddr2` varchar(35) default NULL,
  `sCity` varchar(35) NOT NULL default '',
  `sState` varchar(35) NOT NULL default '',
  `sPCode` varchar(35) NOT NULL default '',
  `cCountryCode` char(2) NOT NULL default '',
  `sPhone` varchar(30) NOT NULL default '',
  `sEmail` varchar(80) NOT NULL default '',
  `sQuestion` varchar(255) default NULL,
  `sAnswer` varchar(255) NOT NULL default '',
  `dtInserted` timestamp NOT NULL,
  `dtUpdated` datetime default NULL,
  `iAccessLevel` tinyint(4) NOT NULL default '1',
  `is_banned` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;



This is the code I came up with...

code:
$sql = "DESCRIBE table";
$fields = mysql_query($sql);

$arrFields = array();
while ( $arrRow = mysql_fetch_object($fields) ) {
	# fields with lengths will have the '('
	# get the position of the number after the '('
	$pos = strpos($arrRow->Type,'(') + 1;
	# get the position of the number before the ')'
	$pos2 = strpos($arrRow->Type,')');
	#get the number if digits for the length
	$length = $pos2 - $pos;
	# set maxlength to the number inside the '()'
	$iMaxsize = substr($arrRow->Type, $pos, $length);
	
	# if the field has a mex length then place the field $key and $value into the array;
	$arrFields[$arrRow->Field] = $iMaxsize;
	
	# if there is no '(' then the field does not have a max length
	if (strpos($arrRow->Type,'(') == '') {
		# unset the array value if it doesnt have a max length
		unset($arrFields[$arrRow->Field]);
	}
																																						 
}

print_r($arrFields);



which results in this:

Array (
[id] => 11 [sUsername] => 35 [sPassword] => 40 [sFName] => 35 [sMName] => 35
[sLName] => 35 [sAddr1] => 40 [sAddr2] => 35 [sCity] => 35 [sState] => 35
[sPCode] => 35 [cCountryCode] => 2 [sPhone] => 30 [sEmail] => 80 [sQuestion] => 255
[sAnswer] => 255 [iAccessLevel] => 4 [is_banned] => 4
)

(Edited by Hustluz on 06-28-2009 01:40)

« Backwards — Onwards »

Show Forum Drop Down Menu