Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : Microsoft SQL Server Functions : mssql_fetch_field

mssql_fetch_field

Get field information (PHP 4, PHP 5, PECL odbtp:1.1.1-1.1.4)
object mssql_fetch_field ( resource result [, int field_offset] )

Examples ( Source code ) » mssql_fetch_field

<?php
// Connect MSSQL
mssql_connect('MANGO\SQLEXPRESS''sa''phpfi');
mssql_select_db('php');

// Send a select query to MSSQL
$query mssql_query('SELECT * FROM [php].[dbo].[persons]');

// Construct table
echo '<h3>Table structure for \'persons\'</h3>';
echo 
'<table border="1">';

// Table header
echo '<thead>';
echo 
'<tr>';
echo 
'<td>Field name</td>';
echo 
'<td>Data type</td>';
echo 
'<td>Max length</td>';
echo 
'</tr>';
echo 
'</thead>';

// Dump all fields
echo '<tbody>';

for(
$i 0$i mssql_num_fields($query); ++$i)
{
    
// Fetch the field information
    
$field mssql_fetch_field($query$i);

    
// Print the row
    
echo '<tr>';
    echo 
'<td>' $field->name '</td>';
    echo 
'<td>' strtoupper($field->type) . '</td>';
    echo 
'<td>' $field->max_length '</td>';
    echo 
'</tr>';
}

echo 
'</tbody>';
echo 
'</table>';

// Free the query result
mssql_free_result($query);
?>

Code Examples / Notes » mssql_fetch_field

pong

When you mssql_fetch_field(int result), you need to do loop to get the name of each field.  Something like:
while($fld = mssql_fetch_field($rs)){
echo $fld->name . "
";
}
I am wondering why we cannot refer it by a field number.


kubalaa

Using this function with MSSQL 7, $returned->column_source is the column name, not the table name as it should be.

skipsey

It seems fairly hard to get a list of the tables from your database using MSSQL but this seems to do the trick. This is set to get only the User Tables and ignores the sytem tables.
function GetField($res,$field,$number) {
return stripSlashes(mssql_result($res,$number,"$field"));
}
mssql_connect("server","","") or die ("help me!");
mssql_select_db("") or die ("Noooo!");
$result = mssql_query ("sp_tables");
$fields = mssql_num_fields ($result);
$rows   = mssql_num_rows ($result);
for ($f=0; $f<$rows; $f++) {

$CHKTYPE=GetField($result,"TABLE_TYPE",$f);

if($CHKTYPE=='TABLE'){
//$name = mssql_fetch_field($result, 2);
$field=GetField($result,"TABLE_NAME",$f)."
";

echo $field;
}
}


reynard hilman

If you want to describe table structure (like mysql 'desc table' command), sending this query might help:
<?
$sql = "SELECT c.name, c.isnullable, c.length, c.colstat, t.name type
 FROM syscolumns c, systypes t, sysobjects o
 WHERE o.name = '$table' AND o.id = c.id AND c.xtype = t.xtype";
?>
I suspect the colstat field in syscolumns table indicates primary key when its value is 1


schattenfeld

If you want to describe table like DESCRIBE in MySQL you can use this:
$sql = <<<SQL
SELECT column_name,data_type,column_default,is_nullable
FROM
 information_schema.tables AS t
 JOIN
 information_schema.columns AS c ON
   t.table_catalog=c.table_catalog AND
   t.table_schema=c.table_schema AND
   t.table_name=c.table_name
WHERE
 t.table_name='TABLE-NAME'
SQL;


huszti_dot_roland_at_freemail_dot_com

For really detailed table information, use syscolumns, like this:
SELECT c.name, c.prec, c.scale, t.name type
 FROM syscolumns c, systypes t, sysobjects o
 WHERE o.name = 'yourtablename' AND o.id = c.id AND c.xtype = t.xtype
For other properties see the MS SQL online help. Search for 'syscolumns'.
Or an another solution:
sp_columns @table_name = 'yourtablename', @column_name = 'thecolumnname'
//no "select ..." !!!!
This gives info about only the specified column.


php_rindern_de

commenting Reynard Hilman:
for me it looks like the colstat field value of 1 in syscolumns table indicates an Identity Column.


bmaddy_at_class_dot_umn_dot_edu

Be aware that this function will only return the first 30 characters of the name of the column.  If the actual column name is longer, it will be truncated.  This is at least true with the following setup:
PHP 4.3.1
MSSQL 8.00.760
Have a good day everyone!
Brian


alonf

As kubalaa at bigfoot dot com note mssql_fetch_field->column_source return field name instead table name also with MSSQL2000 connection. Be adwised!!!

mdean

A quicker query to retrieve table names from the database:
select name from sysobjects where type='u'


Change Language


Follow Navioo On Twitter
mssql_bind
mssql_close
mssql_connect
mssql_data_seek
mssql_execute
mssql_fetch_array
mssql_fetch_assoc
mssql_fetch_batch
mssql_fetch_field
mssql_fetch_object
mssql_fetch_row
mssql_field_length
mssql_field_name
mssql_field_seek
mssql_field_type
mssql_free_result
mssql_free_statement
mssql_get_last_message
mssql_guid_string
mssql_init
mssql_min_error_severity
mssql_min_message_severity
mssql_next_result
mssql_num_fields
mssql_num_rows
mssql_pconnect
mssql_query
mssql_result
mssql_rows_affected
mssql_select_db
eXTReMe Tracker