determining field type

[jeff5656]

With this code I can determine the fieldname and the value.  They are $col_key and $col_value respectively:

$sql = "SELECT * FROM $tbl ";
$result = mysql_query($sql)
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
if ($row == 1) {
	foreach ($line as $col_key => $col_value) {

 

But how do I determine the field TYPE (i.e. varchar, text, date etc)?

[wigpip]

you could try mysql_field_type()

[scootstah]

You can use this query:

SHOW FIELDS FROM table

[jeff5656]

Ok when I put field type in to this code:

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
if ($row == 1) {

	foreach ($line as $col_key => $col_value) {
		echo "<b>".$col_key. ":</b>   ";
		echo "field is ".mysql_field_type ($result, $col_key);
		?><input type="<?php if ($col_key=='password'){echo 'password';} else {echo 'text';}?>"  name="<?php echo $col_key;?>" value="<?php echo $col_value;?>" /><br/><?php 

	}

}

 

all I get is "field type is int"  for every field, even ones that are NOT int.

[jeff5656]

You can use this query:

SHOW FIELDS FROM table

No i want the field type for each field as I loop through them (I'm making a generic form so I want the input types to reflect the field type - so a text box will be used if the field type is text etc)

[scootstah]

You can use this query:

SHOW FIELDS FROM table

No i want the field type for each field as I loop through them (I'm making a generic form so I want the input types to reflect the field type - so a text box will be used if the field type is text etc)

 

That query is the only way you're going to find out its type. You'll have to run that query ahead of time, make an associative array of the field names and their types, and then use that later when you're looping through results.

[jeff5656]

 

That query is the only way you're going to find out its type. You'll have to run that query ahead of time, make an associative array of the field names and their types, and then use that later when you're looping through results.

ugh. So theres no way to identify a field type inside the loop, field by field??

[wigpip]

yes with mysql_field_type() try it using the fresh $result loop not within the foreach()

[scootstah]

 

That query is the only way you're going to find out its type. You'll have to run that query ahead of time, make an associative array of the field names and their types, and then use that later when you're looping through results.

ugh. So theres no way to identify a field type inside the loop, field by field??

 

No, but you could do something like this.

$result = mysql_query("SELECT * FROM $tbl");

$fields = array();
while($row = mysql_fetch_assoc($result)) {
$fields[$row['Field']] = $row['Type'];
}

$sql = "SELECT * FROM $tbl ";
$result = mysql_query($sql)
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
if ($row == 1) {	
	foreach ($line as $col_key => $col_value) {
		echo "<b>".$col_key. ":</b>   ";
		echo "field is ".$fields[$col_key];
		?><input type="<?php if ($col_key=='password'){echo 'password';} else {echo 'text';}?>"  name="<?php echo $col_key;?>" value="<?php echo $col_value;?>" /><br/><?php 

	}		
}
}

 

yes with mysql_field_type() try it using the fresh $result loop not within the foreach()

 

mysql_field_type does not return the actual field type, just a generalization. For example, tinyint, smallint, mediumint, int, and bigint are all classified as "int".

[scootstah]

Oops, just noticed a typo in my code. At the top,

$result = mysql_query("SELECT * FROM $tbl");

should be

$result = mysql_query("SHOW FIELDS FROM $tbl");