Page 1 of 1

get column type from query result

Posted: Tue Jul 04, 2006 5:24 am
by mcccy005
I want to get the data type from each mysql column in a result from an sql query in PHP.

Code: Select all

$result=$db_connection->query($sql_query);
$temp_column;
$temp_result_field=array( );
$temp_result_set=array( );

for($i=0; $i<$result->num_rows; $i++)
{
     $row=$result->fetch_row( );
     for($j=0; $j<sizeof($row); $j++)
     {
          $temp_result_field[$j]=new result_cell(stripslashes($row[$j]), <<GET DATA TYPE FROM $row[$j] HERE>>);
     }
     $result_set[$i] = $temp_result_field;
}
I've included the entire piece of code so you understand exactly what I'm talking about. What I'm trying to avoid is double-querying the database; once with the query; and then again using the DESCRIBE function.

Would the PHP function gettype( ) by any chance work with the data I get from the database???

Thanks

Posted: Tue Jul 04, 2006 5:37 am
by Jenk
gettype() wouldn't work, because all values returned will be of string type.

Have a look at mysql_field_type()

Posted: Tue Jul 04, 2006 12:22 pm
by bdlang
Jenk wrote:gettype() wouldn't work, because all values returned will be of string type.

Have a look at mysql_field_type()
Hmm. While I agree that mysql_field_type() will return the type of MySQL defined field, it will not necessarily return the PHP data type, nor will it return the actual column makeup (VARCHAR, etc). If you want to go the route of returning either, you may want to create a wrapper function or class that maps each type.

Posted: Tue Jul 04, 2006 11:19 pm
by mcccy005
It's all good - mysql_field_type( ) ought to do the trick.
I just need to know if data types are 'SMALLINT', 'INT', LARGEINT etc etc etc so I can classify them in PHP as 'numerical'; and the same again re: text; date/times and 'bool'.

Thanks